CODEWITHSUNDEEP
asp.netdatabasevb.net
sneak14
sneak14

Reputation: 11

Updating database via Gridview manually ASP.NET (VB)

Protected Sub GridView1_RowCommand _
(sender As Object, e As GridViewCommandEventArgs) _
    Handles GridView1.RowCommand
    If e.CommandName.CompareTo("command") = 0 Then

        Dim itemID As Integer = Convert.ToInt32( _
            GridView1.DataKeys(Convert.ToInt32(e.CommandArgument)).Value)

        Dim sqlConn As New SqlConnection("connectionString")

        sqlConn.Open()

        Dim sqlComm As New SqlCommand("UPDATE itemTable SET property = (property + 1) WHERE id = '" + itemID + "', sqlConn")
        sqlComm.ExecuteNonQuery()

        sqlConn.Close()
    End If

End Sub

Basically, it doesn't work and I don't get it. I've been trying to figure this out for a few days now and I can't see what I've done wrong. As you can see I'm trying to manually update a field in my database with the value of (originalvalue)+1 when the user clicks the buttonfield of the corresponding row.

I guess what I'm asking is if anyone can point out any mistakes, please do, or if there's a better way to do this (without having to go through all the DAL/BLL BS) please tell it to me.

Thank you very much!

Upvotes: 0

Views: 2837

Answers (3)

sneak14
sneak14

Reputation: 11

I've solved my own problem! Look at the code:

Sub GridView1_RowCommand(ByVal sender As Object, ByVal e As GridViewCommandEventArgs)

    // If multiple buttons are used in a GridView control, use the
    // CommandName property to determine which button was clicked.
    If e.CommandName = "commandname" Then

        // Convert the row index stored in the CommandArgument
        // property to an Integer.
        Dim index = Convert.ToInt32(e.CommandArgument)

        // Retrieve the row that contains the button clicked 
        // by the user from the Rows collection.
        Dim row = GridView1.Rows(index)

        // Calculate the new value.
        Dim tb = CType(row.FindControl("Label1"), Label)
        Dim newint As Integer = Convert.ToDouble(tb.Text) + 1

        // Get the id of the idea.
        Dim id As Integer = Convert.ToInt32( _
        GridView1.DataKeys(index).Value)

        //Manual update to the database.
        Dim con As New SqlConnection("connectionstring")
        Dim cmd As New SqlCommand("UPDATE ideatable SET field = " & (newint.ToString) & " WHERE id = " & (id.ToString), con)
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()

        //Refresh the page.
        Page.Response.Redirect("default.aspx")

    End If

End Sub

And remember to set OnRowCommand="GridView1_RowCommand" for the gridview in the aspx file code too, I forgot about that earlier! :/

I'm so happy, thanks for all the help offered! Hope this answer helps someone like me who got stuck.

Upvotes: 1

Sanjay
Sanjay

Reputation: 342

Watch the string you are generating and try to execute it manually and then what happens the table gets UPDATE or not.

Because I Think The "ItemId" is not there in your table.

Try to do This in SQL Server "Select * From itemTable Where id = ItemId" Because i think that the itemId is not there in your table.

Upvotes: 0

Sanjay
Sanjay

Reputation: 342

I think there is problem in your sentence :- Try Below one at place of your Statement

Dim sqlComm As New SqlCommand("UPDATE itemTable SET property = (property + 1) WHERE id = " + itemID + "", sqlConn)

Upvotes: 0

Related Questions