0

I'm trying to copy the data from DataGridView1 to a table in Access called LogsProductStock and DataGridView1's source is from a query. I also have DataGridView2 to show if the program has copied the data to LogsProductStock.

This is what I have written so far:

        Dim currentdate = Date.Now
        Dim i As Integer

        Try
            If MsgBox("Are you sure you want to sync tables?", vbYesNo + vbQuestion) = vbYes Then
                For i = 0 To DataGridView1.RowCount - 1
                    conn.Open()
                    cmd = New OleDbCommand("Insert into LogsProductStock (Product_ID, Category_ID, ProductQty, ProductBought, StockInDateTime, DateUpdated)
                        Values (@Product_ID, @Category_ID, @ProductQty, @ProductBought, @StockInDateTime, @DateUpdated)", conn)

                    cmd.Parameters.AddWithValue("@Product_ID", DataGridView1.Rows(i).Cells(0).Value.ToString)
                    cmd.Parameters.AddWithValue("@Category_ID", DataGridView1.Rows(i).Cells(1).Value.ToString)
                    cmd.Parameters.AddWithValue("@ProductQty", Integer.Parse(DataGridView1.Rows(i).Cells(2).Value.ToString))
                    cmd.Parameters.AddWithValue("@ProductBought", Integer.Parse(DataGridView1.Rows(i).Cells(3).Value.ToString))
                    cmd.Parameters.AddWithValue("@StockInDateTime", Date.Parse(DataGridView1.Rows(i).Cells(4).Value.ToString))
                    cmd.Parameters.AddWithValue("@Product_ID", Date.Parse(currentdate))

                    cmd.ExecuteNonQuery()
                    conn.Close()
                    load_data()
                Next
            End If
        Catch ex As Exception
            conn.Close()
            MsgBox(ex.Message, vbCritical)
        End Try

When I run it, DataGridView2 has indeed copied the data from DataGridView1 but there's an error showing that the "Object reference not set to an instance of an object" and it doesn't save to the database. Here is what the details say:

************** Exception Text **************
System.NullReferenceException: Object reference not set to an instance of an object.
   at WindowsApp1.Form1.Button1_Click(Object sender, EventArgs e) in C:\Users\ASUS\Desktop\WindowsApp1\Form1.vb:line 21
   at System.Windows.Forms.Control.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ButtonBase.WndProc(Message& m)
   at System.Windows.Forms.Button.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

For reference, line 21 is the line for the first parameter (@Product_ID). I tried removing it but it just goes down to the second parameter. I have rewritten it but I'm still not sure which part I did wrong. Thanks in advance!

Robin
  • 11
  • 2
  • That's not how you should be inserting multiple rows from a DataGridView. Create a DataTable and bind it to the grid, then save all the data from that DataTable using a data adapter. – jmcilhinney Jan 07 '23 at 13:50

0 Answers0