Insert SQL with variables and variable is not changing in loop (vb.net, postgresql)?

Question

I'm working on an app to insert data from a csv into a postgresql database. I populate a datagrid, loop through the datagrid and insert a record into the table (yes I know the code is pretty verbose, but I want it that way right now for testing purposes). Everything seems to work perfectly; when running the code and debugging, the variables change during the loop, but, on insert into the database, it inserts the data from the first row only with each new insert and not the new variable value.

Thoughts? Suggestions?

Here's the full code:`Me.btnPoupulateData.Enabled = True Dim objConn As New System.Data.Odbc.OdbcConnection Dim objCmd As New System.Data.Odbc.OdbcCommand Dim dtAdapter As New System.Data.Odbc.OdbcDataAdapter Dim ds As New DataSet Dim strConnString As String Dim strSQL As String

    'these are the required fields for the table product_template
    'catc null vals as exceptions
    Dim str_mes_type As String = "fixed"
    Dim i_uom_id As Integer = 1
    Dim i_uom_po_id As Integer = 1
    Dim strtype As String = "product"
    Dim str_procure_method As String = "make_to_stock"
    Dim str_cost_method As String = "standard"
    Dim i_categ_id As Integer = 1
    Dim str_supply_method As String = "buy"
    Dim str_sale_ok As String = True

    Dim str_import_date As String = Me.txtImportID.Text

    Dim dgv As DataGridView = DataGridView1
    Dim iImportCounter As Integer = 0

    System.Windows.Forms.Cursor.Current = Cursors.WaitCursor

    strConnString = "Dsn=PostgreSQL35W;database=OpenERP;server=localhost;port=5432;uid=openpg;pwd=openpgpwd"
    objConn.ConnectionString = strConnString
    objConn.Open()

    strSQL = "INSERT INTO product_template (name,description,standard_price,list_price,mes_type,uom_id,uom_po_id,type,procure_method,cost_method,categ_id,supply_method,sale_ok,import_date) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

    dtAdapter.SelectCommand = objCmd
    objCmd.Connection = objConn

    Try
        For i As Integer = 0 To dgv.RowCount - 1

            'workaround for the problem of not exiting the loop when at end of rows
            If dgv.RowCount = 1 Then
                Exit Try
            End If

            iImportCounter = iImportCounter + 1
            Me.lblRecordsImported.Text = "Records imported: " & iImportCounter


            Dim r As DataGridViewRow = dgv.Rows(i)

            '*************these are the changeable variables******
            With objCmd
                .Parameters.Add("name", Odbc.OdbcType.NVarChar)
                .Parameters.Add("description", Odbc.OdbcType.NVarChar)
                .Parameters.Add("standard_price", Odbc.OdbcType.NVarChar)
                .Parameters.Add("list_price", Odbc.OdbcType.NVarChar)
            End With

            'name goes to default code which is the internal reference number
            Dim strName As String
            strName = dgv.Rows(i).Cells(0).Value
            Dim str_description As String
            str_description = dgv.Rows(i).Cells(1).Value
            Dim i_standard_price As String
            i_standard_price = dgv.Rows(i).Cells(2).Value
            Dim i_list_price As String
            i_list_price = dgv.Rows(i).Cells(3).Value


            With objCmd
                'number of parameters must equal number of ? marks in sql statement
                '14 params now
                '.Parameters.AddWithValue used only for data that's constant
                .Parameters("name").Value = strName
                .Parameters("description").Value = str_description
                .Parameters("standard_price").Value = i_standard_price
                .Parameters("list_price").Value = i_list_price
                .Parameters.AddWithValue("mes_type", str_mes_type)
                .Parameters.AddWithValue("uom_id", i_uom_id)
                .Parameters.AddWithValue("uom_po_id", i_uom_po_id)
                .Parameters.AddWithValue("type", strtype)
                .Parameters.AddWithValue("procure_method", str_procure_method)
                .Parameters.AddWithValue("cost_method", str_cost_method)
                .Parameters.AddWithValue("categ_id", i_categ_id)
                .Parameters.AddWithValue("supply_method", str_supply_method)
                .Parameters.AddWithValue("sale_ok", str_sale_ok)
                '*******created new column in product_template called import_date*******
                'type set to char verying, to be used for later searching
                .Parameters.AddWithValue("import_date", str_import_date)

                .CommandText = strSQL
                .ExecuteNonQuery()

                'delete the gridview row after import
                dgv.Rows.RemoveAt(i)
                Application.DoEvents()

            End With
        Next

    Catch ex As Exception   ' 
        'this is my way to resume next since there are errors on specific data rows that
        'will be ignored
        ImportCSV()

    End Try

    objConn.Close()

    System.Windows.Forms.Cursor.Current = Cursors.Default

`

Tarik Tarik · Accepted Answer · 2013-08-10T15:48:37

You should be adding the parameters once outside the loop then set the parameter values inside the loop using:

With objCmd
    .Parameters.Append objCmd.CreateParameter("name", adVarChar, adParamInput, 20)
    ...
End With


For i As Integer = 0 To dgv.RowCount - 1

    Dim r As DataGridViewRow = dgv.Rows(i)
    Dim strName As String = dgv.Rows(i).Cells(0).Value
    Dim i_standard_price As String = dgv.Rows(i).Cells(1).Value
    Dim i_list_price As String = dgv.Rows(i).Cells(2).Value


    With objCmd
        'number of parameters must equal number of ? marks in sql statement
        .Parameters("name").Value = strName
        ...

Insert SQL with variables and variable is not changing in loop (vb.net, postgresql)?

1 Answers