Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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

`

share|improve this question

1 Answer 1

up vote 0 down vote accepted

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
        ...
share|improve this answer
    
Tarik, I'm confused by your suggestion... my first three parameters are based upon the loop through the datagrid rows... how would i set those "outside the loop"? For example, this .Parameters.AddWithValue("name", strName) is actually the first cell of the current datagrid row, the strName changes during the loop, but the sql statement doesn't insert it into the table. could you possibly modify my code to reflect how you would code this? Thanks! –  JimB Aug 10 '13 at 16:34
    
Create parameters once outside the loop through cmd.parameters.Append cmd.CreateParameter("paramname", adVarChar, adParamInput, 20) then within the loop you set the values of these parameters through .Parameters("...").Value = ... –  Tarik Aug 10 '13 at 17:26
    
Ok, I tried it, and get an error: 'Append' is not a member of 'System.Data.Odbc.OdbcParameterCollection'. I'm using Visual Studio 2005 Pro btw, if that makes a difference. –  JimB Aug 11 '13 at 18:28
    
However, you got me on the right path, thanks! Here's what works: .Parameters.Add("name", Odbc.OdbcType.NVarChar) outside the loop and .Parameters("name").Value = strName inside the loop. thanks for your help! –  JimB Aug 11 '13 at 18:52
    
I did not have a Windows machine to test it. The essential was the idea though. By the way, set the prepared property of the command to true to accelerate your inserts. –  Tarik Aug 11 '13 at 19:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.