How to save integer and decimal datatype from datagridview to database ms access in VB.NET

Question

I have been searching for a solution for my code error, which says that there is a data mismatch to my database declared datatype. I am saving my data grid view's data into ms access database but it show an error of data mismatch for saving PRICE and QUANTITY data.

I have tried adding parameters and it shows the error System.NullReferenceException: 'Object reference not set to an instance of an object.'

'After comfirm only save in database
Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
    If (MessageBox.Show("Comfrim the orders?", "Comfirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = DialogResult.Yes) Then


        If OrderDataGridView.Rows.Count > 0 Then
            DBConnect = New OleDbConnection
            DBConnect.ConnectionString = "Provider=Microsoft.jet.oledb.4.0;data source = ViewOrder.mdb"
            For i As Integer = OrderDataGridView.Rows.Count - 1 To 0 Step -1
                Dim Query As String
                Query = "INSERT INTO ViewOrder.Order (Serve,Table_No, Item_Code, Item_Name, Quantity, Price, Remarks) 
                VALUES (@Serve, @Table_No, @Item_Code, Item_Name, @Quantity, @Price, @Remarks)"

                Dim cmd As New OleDb.OleDbCommand(Query, DBConnect)
                cmd.Parameters.Add("@Serve", OleDbType.VarChar).Value = Label8.Text
                cmd.Parameters.Add("@Table_No", OleDbType.VarChar).Value = Label10.Text
                cmd.Parameters.Add("@Item_Code", OleDbType.VarChar).Value = OrderDataGridView.Rows(i).Cells(0).Value
                cmd.Parameters.Add("@Item_Name", OleDbType.VarChar).Value = OrderDataGridView.Rows(i).Cells(1).Value
                cmd.Parameters.Add("@Quantity", OleDbType.Integer).Value = OrderDataGridView.Rows(i).Cells(2).Value
                cmd.Parameters.Add("@Price", OleDbType.Decimal).Value = OrderDataGridView.Rows(i).Cells(3).Value
                cmd.Parameters.Add("@Remarks", OleDbType.VarChar).Value = OrderDataGridView.Rows(i).Cells(4).Value


                DBConnect.Open()
                Dim Reader As OleDbDataReader



                Reader = command.ExecuteReader
                DBConnect.Close()
            Next


        End If
    End If

End Sub

But it still show the error.

This is my current code with the error of data mismatch.

Dim Query As String
Query = "INSERT INTO ViewOrder.Order (Serve,Table_No, Item_Code, Item_Name, Quantity, Price, Remarks) VALUES ('" & Label8.Text & "','" & Label10.Text & "','" & OrderDataGridView.Rows(i).Cells(0).Value & "', '" & OrderDataGridView.Rows(i).Cells(1).Value & "','" & OrderDataGridView.Rows(i).Cells(2).Value & "','" & OrderDataGridView.Rows(i).Cells(3).Value & "','" & OrderDataGridView.Rows(i).Cells(4).Value & "')"

Dim Reader As OleDbDataReader
command = New OleDbCommand(Query, DBConnect)
Reader = command.ExecuteReader
DBConnect.Close()

I just need to save the data in database.

No, you need first to learn how to write parameterized queries. In this way you are just concatenating together a bunch of strings and you expect that your database can understand what is every value you give. Parameters instead contains the correct datatype with a value of that type. You are forced to be precise and correct when you use parameters — Steve, Mar 24 '19 at 15:40
@Steve Hi, Steve I have been trying out the parameters cmd.Parameters.Add("@Price", OleDbType.Decimal).Value = OrderDataGridView.Rows(i).Cells(3).Value cmd.Parameters.Add("@Remarks", OleDbType.VarChar).Value = OrderDataGridView.Rows(i).Cells(4).Value But it show System.Data.OleDb.OleDbException: 'Parameter Price has no default value.' DO you have any idea? — Erica Xiin Z, Mar 24 '19 at 16:17
Please [edit] your post with the current query text and the list of the parameters added to the OleDbCommand — Steve, Mar 24 '19 at 17:28
Why are you only using parameters for 2 out of the 7 values? — Olivier Jacot-Descombes, Mar 24 '19 at 17:55
@OlivierJacot-Descombes Because only these two variable cannot pass data others, cause i have set my database datatype to Integer and Decimal. The Error just show datatype are not correct for these two variables. — Erica Xiin Z, Mar 24 '19 at 17:59
You specified `Varchar` for the @Quantity parameter. Also, the value returned by the gridvew cell must be of the right type. In the version without parameters you are using single quotes for the numeric parameters. Remove them. E.g., for the quantity `...& "'," & OrderDataGridView.Rows(i).Cells(2).Value & ","...` — Olivier Jacot-Descombes, Mar 24 '19 at 18:08
@Steve Hi,Sir. I have updated my code with parameters, but it still run with error. — Erica Xiin Z, Mar 24 '19 at 22:14
@OlivierJacot-Descombes Hi, Oliver. I have remove remove single quotes it show "INSERT INTO" statement error. — Erica Xiin Z, Mar 24 '19 at 22:17
Use parameters for each value you want to insert. And specify the correct datatype for each parameter and add a value of the expected datatype. Single quotes transform everything in a string. This will create a lot of problems as you are discovering (Also search about Sql Injection) — Steve, Mar 24 '19 at 22:22
@Steve I have try changing it all to parameter but now showing _System.NullReferenceException: 'Object reference not set to an instance of an object.'_ I have update the post with parameter added. — Erica Xiin Z, Mar 25 '19 at 05:05
You execute _command_ but you build a _cmd_ object. Please read carefully the duplicate link posted. — Steve, Mar 25 '19 at 07:26

score 1 · Accepted Answer · answered Mar 25 '19 at 05:37

From the MS Docs

the order in which OleDbParameter objects are added to the OleDbParameterCollection must directly correspond to the position of the question mark placeholder for the parameter in the command text.

Instead of question marks, I like to use parameter names. It is easier to see if I have the order correct. We add the parameters outside the loop because each iteration uses the same parameters. We don't want to keep adding them. Only the values change but not for Serve and TableNo which stay the same for all the Inserts.

Pass the connection string directly to the constructor of the connection.

The Using...End Using blocks ensure that your database objects are closed and disposed even if there is an error.

You do not want a reader for an Insert. A reader is for returned records. You need .ExecuteNonQuery.

You need to check the datatypes of the fields because I just guessed.

The DataGridView.Rows.Count is -2 because subtract one for counting starting at one and collections starting at zero. And subtract another one for the empty row at the bottom of the grid that is included in the count.

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim Query As String = "INSERT INTO ViewOrder.Order (Serve,Table_No, Item_Code, Item_Name, Quantity, Price, Remarks) 
                          Values(@Serve, @TableNo, @ItemCode,@ItemName, @Quantity, @Price, @Remarks);"
    Using DBConnect = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source = ViewOrder.mdb")
        Using cmd As New OleDbCommand(Query, DBConnect)
            cmd.Parameters.Add("@Serve", OleDbType.VarChar).Value = Label8.Text
            cmd.Parameters.Add("@TableNo", OleDbType.VarChar).Value = Label10.Text
            cmd.Parameters.Add("@ItemCode", OleDbType.VarChar)
            cmd.Parameters.Add("@ItemName", OleDbType.VarChar)
            cmd.Parameters.Add("@Quantity", OleDbType.Integer)
            cmd.Parameters.Add("@Price", OleDbType.Decimal)
            cmd.Parameters.Add("@Remarks", OleDbType.VarChar)
            DBConnect.Open()
            For i As Integer = 0 To DataGridView1.Rows.Count - 2
                cmd.Parameters("@ItemCode").Value = OrderDataGridView.Rows(i).Cells(0).Value
                cmd.Parameters("@ItemName").Value = OrderDataGridView.Rows(i).Cells(1).Value
                cmd.Parameters("@Quantity").Value = CInt(OrderDataGridView.Rows(i).Cells(2).Value)
                cmd.Parameters("@Price").Value = CDec(OrderDataGridView.Rows(i).Cells(3).Value)
                cmd.Parameters("@Remarks").Value = OrderDataGridView.Rows(i).Cells(4).Value
                cmd.ExecuteNonQuery()
            Next
        End Using
    End Using
End Sub

How to save integer and decimal datatype from datagridview to database ms access in VB.NET

1 Answers1