while in grid view, button Select adds data to a table

Question

YMCMb 0 Light Poster

15 Years Ago

Hey everyone,

Im new to ASP.NET i have searched the web and i have come to a few threads which are close to what i am looking for, however they are only helpful to me to a certain extent.

I have a grid in a form which is being populated by a table adapter, which has three Three attributes.

I have included a select button to be enabled,

What i would like to achieve is : When the user select a row, i would like to add the three attributes to a new table and i would like to add a few more details which are not displayed to the table for example Username (windows authentication).

What i have so far is on the code behind is i believe a query that i can use which is :

SqlCommand("INSERT INTO tb2 (s.no, name,class) SELECT s.no, name , class FROM tb1", sqlcon). However this access the table 1 which i dont want, i want to add the selected data to the new table.

I am writing this in VB.

Thank you very much for reading this and any help would be really appreciated.

Thank you ,

:S

.net:vb.net

2 Contributors
14 Replies
222 Views
1 Week Discussion Span
Latest Post 15 Years Ago Latest Post by YMCMb

Dani AI

Generated 7 Months Ago

Brief note: was right that handling the GridView's SelectedIndexChanged is the normal place to capture a row; moving toward a business‑logic/table‑adapter approach is also a good step for long‑term maintainability. The insert failing in this thread is almost always one of a few concrete problems (and not the GridView control itself): malformed SQL/concatenation, wrong cell indexes because the Select button adds a column, trying to insert into an identity or a column with illegal characters, or swallowing the exception in an empty Catch block so you never see the error.

A safer pattern is: use DataKeyNames for the row id, use FindControl or DataKeys instead of hard cell indexes, and use a parameterized INSERT inside a Using block so connections are closed cleanly and types are enforced. Example (VB.NET, illustrative only — adapt control IDs and types to your page):

Protected Sub GVBookings_SelectedIndexChanged(sender As Object, e As EventArgs) Handles GVBookings.SelectedIndexChanged
    Dim id = Convert.ToInt32(GVBookings.DataKeys(GVBookings.SelectedIndex).Value)
    Dim name As String = DirectCast(GVBookings.SelectedRow.FindControl("lblName"), Label).Text
    Dim userName As String = HttpContext.Current.User.Identity.Name

    Dim sql As String = "INSERT INTO dbo.tblMODULES([Module ID],[Name],Cred,Total,Module,Username) " &
                        "VALUES(@Id,@Name,@Cred,@Total,@Module,@User)"

    Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings("StudentsCalculatorConnectionString").ConnectionString),
          cmd As New SqlCommand(sql, cn)
        cmd.Parameters.Add("@Id", SqlDbType.Int).Value = id
        cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 200).Value = name
        cmd.Parameters.Add("@User", SqlDbType.NVarChar, 200).Value = userName

        cn.Open()
        Dim rowsAffected = cmd.ExecuteNonQuery()
        ' check rowsAffected > 0 for success
    End Using
End Sub

Troubleshooting checklist:

Set GridView.DataKeyNames to the primary key and use DataKeys instead of SelectedRow.Cells (Select column shifts indexes).
If you must use text from cells, prefer TemplateField + FindControl to avoid index fragility.
Wrap DB calls in Using and log exceptions in Catch so you can see SQL errors.
Surround column names that have spaces or punctuation (e.g., "Module ID" or "s.no") with square brackets.
If a column is IDENTITY, do not insert it; let the DB generate it.
For Windows username use HttpContext.Current.User.Identity.Name and ensure IIS/web.config are configured for Windows auth.

Final note: moving the insert into a BLL/typed TableAdapter or repository (as did) simplifies validation and testing and avoids repeated inline SQL.

All 14 Replies

Akash Saikia 9 Junior Poster

15 Years Ago

You need to understand the selected index changed event of the grid view

In your grid view's selected index changed event write like this

Dim attrib1, attrib2, attrib3 As String

attrib1 = ""
attrib2 = ""
attrib3 = ""

attrib1 = GridView1.SelectedRow.Cells(0).Text
'0 for getting first attribute value,as its index would be 0 and so on for other attribbutes
attrib2 = GridView1.SelectedRow.Cells(1).Text
attrib3 = GridView1.SelectedRow.Cells(2).Text

Try
'Open your database connection here
SqlCommand("INSERT INTO tb2 (s.no, name,class) values(attrib1,attrib2,attrib3)", sqlcon)

Catch ex As Exception
Finally
'Close your connection here in case any error occurs
End Try

What happens here is that when you select ,the selected index event gets fired and so at that time we can get all the values of the selected row.
Now your selected row will be having 3 columns.
Remember 1st column (Attribute 1) will be having index 0
and so on

:)
Good luck

Akash Saikia 9 Junior Poster

15 Years Ago

Oh yes,i really i have not provided you the full code,i just gave you the other stuffs through which you can get the column values.
So in case you want to fire that query.

Write this...

Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.SelectedIndexChanged
        Dim attrib1, attrib2, attrib3 As String
        attrib1 = ""
        attrib2 = ""
        attrib3 = ""

        Dim connString As String = System.Configuration.ConfigurationManager.ConnectionStrings("Your_connectionString_Name").ConnectionString
        Dim cmd As New SqlCommand()
        Dim sqlCon As New SqlConnection(connString)
        cmd.Connection = sqlCon
        cmd.CommandText = qry

       

        attrib1 = GridView1.SelectedRow.Cells(0).Text
        '0 for getting first attribute value,as its index would be 0 and so on for other attribbutes
        attrib2 = GridView1.SelectedRow.Cells(1).Text
        attrib3 = GridView1.SelectedRow.Cells(2).Text

        Dim qry As String = "INSERT INTO tb2 (s.no, name,class) values(" & attrib1 & ",'" & attrib2 & "','" & ",'" & attrib3 & "')"
        Try
            'Open your database connection here

           
            sqlCon.Open()
            cmd.ExecuteNonQuery()



        Catch ex As Exception
        Finally
            'Close your connection here in case any error occurs
            sqlCon.Close()
        End Try
    End Sub
End Class



I hope this will help you....

Akash Saikia 9 Junior Poster

15 Years Ago

I am not getting your question exactly,but if the question is aboout selecting individual cells,then it is correct. No problem there.

Akash Saikia 9 Junior Poster

15 Years Ago

My pleasure,happy codding :)

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

YMCMb 0 Light Poster · Answer 1 · 2011-03-29T14:29:10+00:00

Hey thanks for the reply really appreciate it sir :), but hopefully just one question i have declared my connection in web.config file and i am calling the relavent xsd file so how do i go around that as at the moment with the coding you have provided it comes with in error : 'SQL Command is a type and cannot be used as a Expression' :( i have imported the

Imports System.Data.SqlClient

?? any suggestions ?

and once again thank you sir for your reply :)

YMCMb 0 Light Poster · Answer 2 · 2011-03-29T14:36:04+00:00

Okay i have corrected it, i forgot to include a new connection to sql

and to do this i have done the following :

Static conn As String = "<connection string>"
        Dim sqlcon As New SqlConnection(conn)
        Dim comm As New SqlCommand

and at the SQL connection i have wrote the following code

comm = new SqlCommand("INSERT INTO tb2 (s.no, name,class) values(attrib1,attrib2,attrib3)", sqlcon)

And this has got rid of the errors.

I will give this a try and let you know how i get along :D

Thanks once again Akash!

YMCMb 0 Light Poster · Answer 3 · 2011-03-29T16:06:06+00:00

Thank you for the full code helped me correct some things that i was doing wrong, but i wanted to ask you, i am picking out individual cells in this, is that correct ? because when i hover over the selects in the grid it says

Select$0

Thanks

Kushal

YMCMb 0 Light Poster · Answer 4 · 2011-03-29T16:54:31+00:00

YMCMb 0 Light Poster

15 Years Ago

Thanks :)

YMCMb 0 Light Poster · Answer 5 · 2011-03-29T19:05:51+00:00

Okay i have a problem its not updating the table and its not returning the number of rows affected either :( any advice :(

Protected Sub Students_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles Students.SelectedIndexChanged
        Dim attrib1, attrib2, attrib3, attrib4, attrib5 As String

            'Open your database connection here

        Dim connString As String = System.Configuration.ConfigurationManager.ConnectionStrings("StudentsCalculatorConnectionString").ConnectionString
        Dim cmd As New SqlCommand()
        Dim sqlCon As New SqlConnection(connString)
        cmd.Connection = sqlCon
        'cmd.CommandText = qry  -> it was showing as a cmd cannot be displayed before beeing declared so i moved the declaration down a few lines!

        attrib1 = GVBookings.SelectedRow.Cells(0).Text
        '0 for getting first attribute value,as its index would be 0 and so on for other attribbutes
        attrib2 = GVBookings.SelectedRow.Cells(1).Text
        attrib3 = GVBookings.SelectedRow.Cells(2).Text
        attrib4 = GVBookings.SelectedRow.Cells(3).Text
        attrib5 = GVBookings.SelectedRow.Cells(4).Text

        'Set the bookings id number to be automated 

        Dim qry As String = "INSERT INTO dbo.tblMODULES([Module ID], name, cred, Total, Module, Username ) values(" & TestLbl.Text & ",'" & attrib2 & "','" & attrib3 & "','" & attrib4 & "','" & attrib5 & "','" & Lbname.Text & "')"
        cmd.CommandText = qry
        Try
            'Open database connection here
            sqlCon.Open()
            cmd.ExecuteNonQuery()

        Catch ex As Exception
        Finally
            'Close connection 
            sqlCon.Close()
        End Try
    End Sub

No error nothing :( doesnt even bring back if any rows were effected :(

Akash Saikia 9 Junior Poster · Answer 6 · 2011-03-29T19:13:11+00:00

did u try to put something inside the catch block, like alert or something to trace for any errors??

YMCMb 0 Light Poster · Answer 7 · 2011-03-29T19:17:40+00:00

Lol that would be a good start let me have a pop at that thanks !

YMCMb 0 Light Poster · Answer 8 · 2011-03-29T20:48:59+00:00

Ok very confused now it says that the

qry cannot be used before being declared and now it has a problem with & signs :(

Akash Saikia 9 Junior Poster · Answer 9 · 2011-03-29T20:58:32+00:00

Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.SelectedIndexChanged
        Dim attrib1, attrib2, attrib3 As String
        attrib1 = ""
        attrib2 = ""
        attrib3 = ""

        Dim connString As String = System.Configuration.ConfigurationManager.ConnectionStrings("Your_connectionString_Name").ConnectionString
        Dim cmd As New SqlCommand()
        Dim sqlCon As New SqlConnection(connString)
        cmd.Connection = sqlCon
        

       

        attrib1 = GridView1.SelectedRow.Cells(0).Text
        '0 for getting first attribute value,as its index would be 0 and so on for other attribbutes
        attrib2 = GridView1.SelectedRow.Cells(1).Text
        attrib3 = GridView1.SelectedRow.Cells(2).Text

        Dim qry As String = "INSERT INTO tb2 (s.no, name,class) values(" + attrib1 + ",'" + attrib2 +  "','" + ",'" + attrib3 & "')"
        cmd.CommandText = qry
        Try
            'Open your database connection here

           
            sqlCon.Open()
            cmd.ExecuteNonQuery()



        Catch ex As Exception
        Finally
            'Close your connection here in case any error occurs
            sqlCon.Close()
        End Try
    End Sub
End Class

I have changed a line from top to bottom and changed & to +

Try this :)

YMCMb 0 Light Poster · Answer 10 · 2011-04-04T16:14:20+00:00

Hey this thread has been solved, two ways of adding one is shown by Akash

The other is to you a business logic layer.

The default.aspx page calls on the myProjectBLL.vb.

The code for the Business logic layer should be something like the following.

<System.ComponentModel.DataObjectMethodAttribute _
        (System.ComponentModel.DataObjectMethodType.Insert, True)> _
    Public Function InsertModules( _
        ByVal StudentName As String, ByVal StudentId As Nullable(Of Double), ByVal TotalMark As Nullable(Of Int32)) _
        As Boolean

        

        Dim Modules As New Students.ModulesDataTable()
        Dim Module As Students.ModulesRow = Bookings.NewModuleRow()

        Module.StudentName = StudentName.ToString
        Module.StudentId = StudentId.Value
        Module.TotalMark = TotalMark.Value

        Modules.AddModuleRow(Module)
        Dim rowsAffected As Integer = AdapterModules.Update(Modules)

        Return rowsAffected = 1



    End Function

And then

in the default.aspx call in the function by the following code!

Dim diditwork As Boolean = bll.InsertModules(StudentName, StudentId, TotalMark)

        If diditwork = True Then
            Dim ModulesMarkAdd = "Module Added"
            ModulesAdded.Text = ModulesMarkAdd
        End If

while in grid view, button Select adds data to a table

Dani AI

Recommended Answers Collapse Answers

All 14 Replies

Recommended Answers