none
Append from DataGridView to table issue

    Question

  • Using the below code I am having the following issue:

    Issue: When running the code I receive the correct number of records, but the data is all showing the first row of the datagrid.

    (is the software title in the first row of the grid is Adobe and there are 33 records of software titles I receive 33 Adobe records, instead of stepping + 1 thru the rows.

    dim con as New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source="C:\users\testaccount\documents\data.accmd"
    Dim sqlstr as New OleDB.OleDBCommand
    con.open()
    sqlstr.connection = con
    for 1 = 0 to datagrid_SoftwareInfo.Rows.Count -1 Step 1
    sqlstr.CommandText = "INSERT INTO syssoftwareInstalled(softwareproduct,softversion,softinstalldate,sysID) Values (varsoftwareproduct,varsoftversion,varsoftinstalldate,varsysID)"
    sqlstr.Parameters.AddwithValue("varsoftwareproduct"), datagrid_softwareinfo.Rows(i).Cells(0).Value)
    sqlstr.Parameters.AddwithValue("varsoftversion"), datagrid_softwareinfo.Rows(i).Cells(1).Value)
    sqlstr.Parameters.AddwithValue("varsoftinstalldate"), datagrid_softwareinfo.Rows(i).Cells(2).Value)
    sqlstr.Parameters.AddwithValue("varsysID"), me.txtbx_SystemID.Text)
    sqlstr.ExecuteNonQuery()
    Next
    con.Close()


    Friday, April 7, 2017 2:02 PM

Answers

  • This example is based upon Reed's suggestion, where the OleDbCommand is recreated for each INSERT:

    for i = 0 to datagrid_SoftwareInfo.Rows.Count - 1
    	Dim sqlstr As New OleDbCommand("INSERT INTO syssoftwareInstalled(softwareproduct,softversion,softinstalldate,sysID) Values (varsoftwareproduct,varsoftversion,varsoftinstalldate,varsysID", con)
    	sqlstr.Parameters.AddwithValue("varsoftwareproduct"), datagrid_softwareinfo.Rows(i).Cells(0).Value)
    	sqlstr.Parameters.AddwithValue("varsoftversion"), datagrid_softwareinfo.Rows(i).Cells(1).Value)
    	sqlstr.Parameters.AddwithValue("varsoftinstalldate"), datagrid_softwareinfo.Rows(i).Cells(2).Value)
    	sqlstr.Parameters.AddwithValue("varsysID"), me.txtbx_SystemID.Text)
    	sqlstr.ExecuteNonQuery()
    Next
    If this doesn't work for you then you will need to step through your code in Debug to see what is actually in your DataGridView.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, April 7, 2017 3:08 PM
  • Hello,

    The following code sample will iterate the rows in your DataGridView.

    First setup parameters and remember them in an array of OleDbParameter which will be used for each iteration as after in each iterating they are cleared so we can then get the new primary key using a secondary query. Perhaps you don't care about the new primary key, even so you should (as I'm doing here) set the parameters up once and in each iteration set values rather than creating parameters for each iteration, use them execute the command, clear and repeat is not efficient at all.

    Note 1 I strongly typed the parameters, if the types are wrong then correct the incorrect types.

    Note 2, the newPrimaryKeys Dictionary is optional, it's simply a container for row index and new primary key value.

    Note 3, the ErrorMessages Dictionary houses exceptions and this also as implemented allows the for next to continue.

    Lots going on here so take your time to study it and remember I could not try it out myself without having your project and database.

    Imports System.Data.OleDb
    
    Public Class Form1
        Private Builder As New OleDbConnectionStringBuilder With
        {
            .Provider = "Microsoft.ACE.OLEDB.12.0",
            .DataSource = "C:\users\testaccount\documents\data.accmd"
        }
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim newPrimaryKeys As New Dictionary(Of Integer, Integer)
            Dim ErrorMessages As New Dictionary(Of Integer, String)
    
            Dim Affected As Integer = 0
            Dim Identfier As Integer = 0
    
            Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                Using cmd As New OleDbCommand With {.Connection = cn}
                    '
                    ' Setup parameters for INSERT
                    '
                    cmd.Parameters.Add(New OleDbParameter With {.ParameterName = "@varsoftwareproduct", .DbType = DbType.String})
                    cmd.Parameters.Add(New OleDbParameter With {.ParameterName = "@varsoftversion", .DbType = DbType.String})
                    cmd.Parameters.Add(New OleDbParameter With {.ParameterName = "@varsoftinstalldate", .DbType = DbType.DateTime})
                    cmd.Parameters.Add(New OleDbParameter With {.ParameterName = "@varsysID", .DbType = DbType.Int32})
    
                    '
                    ' Setup command for INSERT in tangent with parmeters above
                    '
                    Dim InsertCommand As String = "INSERT INTO syssoftwareInstalled(softwareproduct,softversion,softinstalldate,sysID) " &
                        "Values (varsoftwareproduct,varsoftversion,varsoftinstalldate,varsysID"
    
                    Dim NewIdCommand As String = "Select @@Identity"
    
                    Dim ParamsForMainTableInsert(cmd.Parameters.Count - 1) As OleDbParameter
                    cmd.Parameters.CopyTo(ParamsForMainTableInsert, 0)
    
                    Try
                        cn.Open()
                    Catch ex As Exception
                        ' failed to open
                        MessageBox.Show("Failed to open " & Builder.DataSource & Environment.NewLine & "Check your connection string")
                        Exit Sub
                    End Try
    
                    For i = 0 To datagrid_SoftwareInfo.Rows.Count - 1
                        ' check if row is the new row (last row in the DataGridView)
                        If Not datagrid_SoftwareInfo.Rows(i).IsNewRow Then
                            cmd.CommandText = InsertCommand
    
                            cmd.Parameters("@varsoftwareproduct").Value = datagrid_SoftwareInfo.Rows(i).Cells(0).Value
                            cmd.Parameters("@varsoftversion").Value = datagrid_SoftwareInfo.Rows(i).Cells(1).Value
                            cmd.Parameters("@varsoftinstalldate").Value = datagrid_SoftwareInfo.Rows(i).Cells(2).Value
                            cmd.Parameters("@varsysID").Value = Me.txtbx_SystemID.Text
    
                            Try
                                ' execute the insert
                                Affected = cmd.ExecuteNonQuery
                                ' clear parameters for obtaining new primary key
                                cmd.Parameters.Clear()
    
                                If Affected = 1 Then
                                    ' record was inserted, now get the new primary key
                                    cmd.CommandText = NewIdCommand
                                    Identfier = CInt(cmd.ExecuteScalar) ' new identifier for new record
                                    newPrimaryKeys.Add(i, Identfier)
                                End If
                            Catch ex As Exception
                                ErrorMessages.Add(i, ex.Message)
                            End Try
    
                            ' reset for command text insert command
                            cmd.CommandText = InsertCommand
                            ' reset command parameters for insert command
                            cmd.Parameters.AddRange(ParamsForMainTableInsert)
    
                        End If
                    Next
                End Using
            End Using
    
            ' these are the new keys that you can perhaps have a column in the DataGridView
            ' to add them into the appropriate rows
            If newPrimaryKeys.Count > 0 Then
                For Each item As KeyValuePair(Of Integer, Integer) In newPrimaryKeys
                    Console.WriteLine("Row: {0} Id: {1}", item.Key, item.Value)
                Next
            End If
    
    
            If ErrorMessages.Count > 0 Then
                MessageBox.Show("See output window for errors by row index")
                For Each item As KeyValuePair(Of Integer, String) In ErrorMessages
                    Console.WriteLine("Row: {0} Error: {1}", item.Key, item.Value)
                Next
            End If
    
        End Sub
    
    End Class
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, April 7, 2017 4:36 PM
    Moderator

All replies

  • Sorry, the machine my code is on is not accessible via the internet terminals.
    dim con as New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source="C:\users\testaccount\documents\data.accmd")
    Dim sqlstr as New OleDB.OleDBCommand
    con.open()
    sqlstr.connection = con
    
    for 1 = 0 to datagrid_SoftwareInfo.Rows.Count -1 Step 1
    sqlstr.CommandText = "INSERT INTO syssoftwareInstalled(softwareproduct,softversion,softinstalldate,sysID) Values (varsoftwareproduct,varsoftversion,varsoftinstalldate,varsysID)"
    sqlstr.Parameters.AddwithValue("varsoftwareproduct"), datagrid_softwareinfo.Rows(i).Cells(0).Value)
    sqlstr.Parameters.AddwithValue("varsoftversion"), datagrid_softwareinfo.Rows(i).Cells(1).Value)
    sqlstr.Parameters.AddwithValue("varsoftinstalldate"), datagrid_softwareinfo.Rows(i).Cells(2).Value)
    sqlstr.Parameters.AddwithValue("varsysID"), me.txtbx_SystemID.Text)
    sqlstr.ExecuteNonQuery()
    Next
    con.Close()

    Friday, April 7, 2017 2:12 PM
  • I assume that was just a typo in your For statement, because it wouldn't compile.  It is actually "For i = 0 To...", correct?

    I'm not sure how the OleDBCommand is responding to the fact that you keep adding the same parameter names on each iteration of the loop.  I would clear the Parameter collection before adding values or create new OleDBCommand instance on each iteration of the loop.

    Also note that if the DataGridView is displaying the New row line, that will be a blank row in the Rows collection and should be tested for and skipped.  It is generally better to loop through the underlying BindingSource instead of the DataGridView rows when you want to access every item in the currently bound collection.


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Friday, April 7, 2017 2:29 PM
    Moderator
  • Thank you Reed.

    (totally correct on the typo, lol)

    There is no databind on the datagridview, it is pulled from a select statement from a remote machine win32 table and dumped into a datagridview.

    I have tried setting all parameters to = "" but still displays repetitive data.

    its as if the rowcount sees there are 33 records, or 200, and loops thru those, but does not change the parameters value.

    Friday, April 7, 2017 2:37 PM
  • This example is based upon Reed's suggestion, where the OleDbCommand is recreated for each INSERT:

    for i = 0 to datagrid_SoftwareInfo.Rows.Count - 1
    	Dim sqlstr As New OleDbCommand("INSERT INTO syssoftwareInstalled(softwareproduct,softversion,softinstalldate,sysID) Values (varsoftwareproduct,varsoftversion,varsoftinstalldate,varsysID", con)
    	sqlstr.Parameters.AddwithValue("varsoftwareproduct"), datagrid_softwareinfo.Rows(i).Cells(0).Value)
    	sqlstr.Parameters.AddwithValue("varsoftversion"), datagrid_softwareinfo.Rows(i).Cells(1).Value)
    	sqlstr.Parameters.AddwithValue("varsoftinstalldate"), datagrid_softwareinfo.Rows(i).Cells(2).Value)
    	sqlstr.Parameters.AddwithValue("varsysID"), me.txtbx_SystemID.Text)
    	sqlstr.ExecuteNonQuery()
    Next
    If this doesn't work for you then you will need to step through your code in Debug to see what is actually in your DataGridView.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, April 7, 2017 3:08 PM
  • Hello,

    The following code sample will iterate the rows in your DataGridView.

    First setup parameters and remember them in an array of OleDbParameter which will be used for each iteration as after in each iterating they are cleared so we can then get the new primary key using a secondary query. Perhaps you don't care about the new primary key, even so you should (as I'm doing here) set the parameters up once and in each iteration set values rather than creating parameters for each iteration, use them execute the command, clear and repeat is not efficient at all.

    Note 1 I strongly typed the parameters, if the types are wrong then correct the incorrect types.

    Note 2, the newPrimaryKeys Dictionary is optional, it's simply a container for row index and new primary key value.

    Note 3, the ErrorMessages Dictionary houses exceptions and this also as implemented allows the for next to continue.

    Lots going on here so take your time to study it and remember I could not try it out myself without having your project and database.

    Imports System.Data.OleDb
    
    Public Class Form1
        Private Builder As New OleDbConnectionStringBuilder With
        {
            .Provider = "Microsoft.ACE.OLEDB.12.0",
            .DataSource = "C:\users\testaccount\documents\data.accmd"
        }
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim newPrimaryKeys As New Dictionary(Of Integer, Integer)
            Dim ErrorMessages As New Dictionary(Of Integer, String)
    
            Dim Affected As Integer = 0
            Dim Identfier As Integer = 0
    
            Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                Using cmd As New OleDbCommand With {.Connection = cn}
                    '
                    ' Setup parameters for INSERT
                    '
                    cmd.Parameters.Add(New OleDbParameter With {.ParameterName = "@varsoftwareproduct", .DbType = DbType.String})
                    cmd.Parameters.Add(New OleDbParameter With {.ParameterName = "@varsoftversion", .DbType = DbType.String})
                    cmd.Parameters.Add(New OleDbParameter With {.ParameterName = "@varsoftinstalldate", .DbType = DbType.DateTime})
                    cmd.Parameters.Add(New OleDbParameter With {.ParameterName = "@varsysID", .DbType = DbType.Int32})
    
                    '
                    ' Setup command for INSERT in tangent with parmeters above
                    '
                    Dim InsertCommand As String = "INSERT INTO syssoftwareInstalled(softwareproduct,softversion,softinstalldate,sysID) " &
                        "Values (varsoftwareproduct,varsoftversion,varsoftinstalldate,varsysID"
    
                    Dim NewIdCommand As String = "Select @@Identity"
    
                    Dim ParamsForMainTableInsert(cmd.Parameters.Count - 1) As OleDbParameter
                    cmd.Parameters.CopyTo(ParamsForMainTableInsert, 0)
    
                    Try
                        cn.Open()
                    Catch ex As Exception
                        ' failed to open
                        MessageBox.Show("Failed to open " & Builder.DataSource & Environment.NewLine & "Check your connection string")
                        Exit Sub
                    End Try
    
                    For i = 0 To datagrid_SoftwareInfo.Rows.Count - 1
                        ' check if row is the new row (last row in the DataGridView)
                        If Not datagrid_SoftwareInfo.Rows(i).IsNewRow Then
                            cmd.CommandText = InsertCommand
    
                            cmd.Parameters("@varsoftwareproduct").Value = datagrid_SoftwareInfo.Rows(i).Cells(0).Value
                            cmd.Parameters("@varsoftversion").Value = datagrid_SoftwareInfo.Rows(i).Cells(1).Value
                            cmd.Parameters("@varsoftinstalldate").Value = datagrid_SoftwareInfo.Rows(i).Cells(2).Value
                            cmd.Parameters("@varsysID").Value = Me.txtbx_SystemID.Text
    
                            Try
                                ' execute the insert
                                Affected = cmd.ExecuteNonQuery
                                ' clear parameters for obtaining new primary key
                                cmd.Parameters.Clear()
    
                                If Affected = 1 Then
                                    ' record was inserted, now get the new primary key
                                    cmd.CommandText = NewIdCommand
                                    Identfier = CInt(cmd.ExecuteScalar) ' new identifier for new record
                                    newPrimaryKeys.Add(i, Identfier)
                                End If
                            Catch ex As Exception
                                ErrorMessages.Add(i, ex.Message)
                            End Try
    
                            ' reset for command text insert command
                            cmd.CommandText = InsertCommand
                            ' reset command parameters for insert command
                            cmd.Parameters.AddRange(ParamsForMainTableInsert)
    
                        End If
                    Next
                End Using
            End Using
    
            ' these are the new keys that you can perhaps have a column in the DataGridView
            ' to add them into the appropriate rows
            If newPrimaryKeys.Count > 0 Then
                For Each item As KeyValuePair(Of Integer, Integer) In newPrimaryKeys
                    Console.WriteLine("Row: {0} Id: {1}", item.Key, item.Value)
                Next
            End If
    
    
            If ErrorMessages.Count > 0 Then
                MessageBox.Show("See output window for errors by row index")
                For Each item As KeyValuePair(Of Integer, String) In ErrorMessages
                    Console.WriteLine("Row: {0} Error: {1}", item.Key, item.Value)
                Next
            End If
    
        End Sub
    
    End Class
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, April 7, 2017 4:36 PM
    Moderator
  • Hi Ryan,

    Please remember to close your thread by marking helpful post as answer, it is very beneficial to the other communities who face the same issue.

    Thanks for your understanding.

    Best Regards,

    Cherry Bu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 20, 2017 7:01 AM
    Moderator