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

[YMCMb]

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

[Akash Saikia]

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

[YMCMb]

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]

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!

[Akash Saikia]

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....

[YMCMb]

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

[Akash Saikia]

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

[YMCMb]

Thanks :)

[Akash Saikia]

My pleasure,happy codding :)

[YMCMb]

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]

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

[YMCMb]

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

[YMCMb]

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]

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]

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