locked
SQL Update Statement Rather Than Insert RRS feed

  • Question

  • User-2140330002 posted

    I have an SQL delete/insert statement that works but I think an update statement would be more appropriate. Not sure as I am not very good at sql statements.

    I have five columns in a table (access) that are updated by a gridview checkbox field. The requirement is a minimum of one checkbox checked and a maximum of five checkboxes checked.

    Currently, if less than five or more than five checkboxes are checked, I get the error: 

    Number of query values and destination fields are not the same.

    My question is how do I avoid this error when selecting less than five checkboxes and how do I convert this SQL statement from an insert to an update statement?

    Thanks in advance

    Dim command As String = "insert into [Selections] ([Selection1], [Selection2], [Selection3], [Selection4], [Selection5]) VALUES("
            For Each gvRow As GridViewRow In GridView1.Rows 'itterate thru all rows  
                Dim chkBox As CheckBox = CType(gvRow.FindControl("chkBox"), CheckBox)
                If chkBox.Checked Then
                    command &= "'" & gvRow.Cells(1).Text & "',"
                   
                    Dim ID = CType(gvRow.Cells(1).Text, String)
                End If
            Next
            command &= ")"
            command = command.Replace(",)", ")")


    Sunday, July 18, 2010 8:35 AM

Answers

All replies

  • User-158764254 posted

    i would convert the sql statement to a parameterized statement

    for examples read this: SQL Injection And Parameterized Queries

    once you have a parameterized statement, you need to make sure you supply a parameter for each value you are inserting or updating. 

    you can use a parameter with a default value if your user has made fewer selections.

    I the user made too many selections, you'd either ignore the extras, or (preferrably) stop them with a validation error at the page and make them fix their selections.

    Sunday, July 18, 2010 9:23 AM
  • User-2140330002 posted

    Thanks, Mike. I have read this and am trying to parameterize the statement, but still I am confused.

    I do have my other statements parameterized as in the article, but am not sure what values to assign the parameters. Any specific suggestions here that will make this a bit more clear would be greatly appreciated.

    Thanks

    Sunday, July 18, 2010 9:53 AM
  • User-158764254 posted

    The values could come from your grids cells as they currently are.

    for instances where you have fewer than 5 specificed by the user, you need to decide what you want to use as a default value for those missing selections - perhaps dbNull.Value. 

    Sunday, July 18, 2010 10:07 AM
  • User-2140330002 posted

    Well, I'm not sure what I am doing here. I think I am close, but I am getting a syntax error - missing operator error on @selection1.

    I am posting the code to see if anyone can find the error:

    Protected Sub Button3_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button3.Click
            Dim command2 As String = "DELETE * from [Selections]"
            Dim command As String = "insert into Selections (Selection1, Selection2, Selection3, Selection4, Selection5) VALUES(@Selection1,@Selection2,@Selection3,@Selection4,@Selection5"
            For Each gvRow As GridViewRow In GridView1.Rows 'itterate tru all rows  
                Dim chkBox As CheckBox = CType(gvRow.FindControl("chkBox"), CheckBox)
                If chkBox.Checked Then
                    command &= "'" & gvRow.Cells(1).Text & "',"
                Else
                    command &= "'0'"
                    Dim PlanID = CType(gvRow.Cells(1).Text, String)
                End If
            Next
            command &= ")"
            command = command.Replace(",)", ")") 'replacing the last ,) to only )  
            Response.Write(command2)
            Response.Write(command)
            Dim sqlcon As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[connectionstring;")
            Dim sqlComm2 As New OleDbCommand()
            sqlComm2.Connection = sqlcon
            sqlComm2.CommandText = command2
            Dim sqlComm As New OleDbCommand()
            'With sqlComm.Parameters
            sqlComm.Parameters.AddWithValue("@Selection1", Request.QueryString("gvRow.Cells(1).Text"))
            sqlComm.Parameters.AddWithValue("@Selection2", Request.QueryString("gvRow.Cells(1).Text"))
            sqlComm.Parameters.AddWithValue("@Selection3", Request.QueryString("gvRow.Cells(1).Text"))
            sqlComm.Parameters.AddWithValue("@Selection4", Request.QueryString("gvRow.Cells(1).Text"))
            sqlComm.Parameters.AddWithValue("@Selection5", Request.QueryString("gvRow.Cells(1).Text"))
            'End With
            sqlComm.Connection = sqlcon
            sqlComm.CommandText = command
    
            Using (sqlcon)
                sqlcon.Open()
                sqlComm2.ExecuteNonQuery()
                sqlComm.ExecuteNonQuery()
            End Using
               End Sub


    Sunday, July 18, 2010 10:46 AM
  • User-158764254 posted

    seems like you're using an Access database.

    The syntax is a little different.  give this a read:

    http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, July 18, 2010 11:10 AM
  • User-2140330002 posted

    Thanks. I am using Access, but still no go. Not sure what I am doing wrong here. I have read the article and tried using it, but still am missing smething.

    Sunday, July 18, 2010 5:16 PM