locked
Insert records into a table using loop in vb 2008

    Question

  •  
    I would like to insert records a user selects from a gridview using check box column
    here is my code which inserts the first record then on the second interation it gives the error

    "The variable name '@Customer' has already been declared. Variable names must be unique within a query batch or stored procedure."


    Public Sub SelectCusListTable()
            Dim cnx As Data.SqlClient.SqlConnection = SqlCnxJPF.SourceDB
            Dim cmdSC = New Data.SqlClient.SqlCommand("insert into selectcus (customer)" & "values (@customer)", (cnx))
            Dim daSC As New SqlClient.SqlDataAdapter()
            Dim dsSC As New DataSet()

          
            Dim c As String



         
            For n As Integer = 0 To DataGridView1.Rows.Count - 1
               
                If CBool(DataGridView1.Rows(n).Cells("selected").Value) = True Then

                    cnx.Open()
                    cmdSC.CommandTimeout = "360"
                     cmdSC.CommandType = Data.CommandType.Text
                    cmdSC.CommandText = ("insert into selectcus (customer)" & "values (@customer)")
                    daSC.SelectCommand = cmdSC
                  
                    c = DataGridView1.Rows(n).Cells(2).Value

                    cmdSC.Parameters.AddWithValue("@Customer", c)
                    cmdSC.ExecuteNonQuery()
                    daSC.SelectCommand = cmdSC
                    cnx.Close()

                End If
                c = ""
            Next

          
    Monday, February 01, 2010 6:37 PM

Answers

  • Clear the parameter collection once you have executed the command would be the quick and dirty way to get past this error.
    You really don't need to reset everything each iteration though.

    jon.stromer.galley
    • Marked as answer by jj3k2009 Monday, February 01, 2010 9:47 PM
    Monday, February 01, 2010 8:41 PM