Question problem with saving data from datagridview into database

[siang93]

error "parameter 1 has no default value"

i try to save data from datagridview into database ,where i insert data into datagridview then save..but when save the data into db it got that error..
the same code where i download no error, but when i edit ,it got..


success code:

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click


        Dim MyConnection As OleDb.OleDbConnection = Nothing
        Dim MyTransaction As OleDb.OleDbTransaction = Nothing


        Try


            ' create the connection and  transaction object
            myconnection = New OleDb.OleDbConnection(My.Settings.dbConnectionString)
            MyConnection.Open()
            MyTransaction = MyConnection.BeginTransaction


            ' insert the new recipt
            Dim SQL As String = "insert into recipts (reciptdate,recipttotal) values (:0,:1)"
            Dim CMD1 As New OleDb.OleDbCommand
            CMD1.Connection = MyConnection
            CMD1.Transaction = MyTransaction
            CMD1.CommandText = SQL
            CMD1.Parameters.AddWithValue(":0", Now.Date)
            CMD1.Parameters.AddWithValue(":1", TextBox4.Text)
            CMD1.ExecuteNonQuery()
            CMD1.Dispose()


            ' get the id for the recipt
            SQL = "select max(reciptid) as MAXID from recipts"
            Dim CMD2 As New OleDb.OleDbCommand
            CMD2.Connection = MyConnection
            CMD2.Transaction = MyTransaction
            CMD2.CommandText = SQL
            Dim ReciptID As Long = CMD2.ExecuteScalar()
            CMD2.Dispose()


            ' insert the details of the recipt
            Dim I As Integer
            For I = 0 To DGV2.Rows.Count - 1


                ' get the values
                Dim Barcode As String = DGV2.Rows(I).Cells(0).Value
                Dim BuyPrice As Decimal = DGV2.Rows(I).Cells(2).Value
                Dim SellPrice As Decimal = DGV2.Rows(I).Cells(3).Value
                Dim ItemCount As Integer = DGV2.Rows(I).Cells(4).Value


                ' next create a command
                Dim CMD3 As New OleDb.OleDbCommand
                SQL = "insert into ReciptDetails " & _
                      "(reciptid,barcode,itemcount,itembuyprice,itemsellprice) " & _
                      "values " & _
                      "(:0      ,:1     ,:2       ,:3          ,:4       )"
                CMD3.Connection = MyConnection
                CMD3.Transaction = MyTransaction
                CMD3.CommandText = SQL
                CMD3.Parameters.AddWithValue(":0", ReciptID)
                CMD3.Parameters.AddWithValue(":1", Barcode)
                CMD3.Parameters.AddWithValue(":2", ItemCount)
                CMD3.Parameters.AddWithValue(":3", BuyPrice)
                CMD3.Parameters.AddWithValue(":4", SellPrice)


                CMD3.ExecuteNonQuery()
                CMD3.Dispose()


            Next




            ' all well save the changes
            MyTransaction.Commit()


            ' close connection
            MyTransaction.Dispose()
            MyConnection.Close()
            MyConnection.Dispose()


            DGV2.Rows.Clear()
            TextBox4.Text = ""


        Catch ex As Exception
            If MyTransaction IsNot Nothing Then
                MyTransaction.Rollback()
            End If
            If myconnection IsNot Nothing Then
                If MyConnection.State = ConnectionState.Open Then
                    MyConnection.Close()
                End If
            End If


            MsgBox(ex.Message, MsgBoxStyle.Critical Or MsgBoxStyle.OkOnly, "Error")
        End Try
      
    End Sub

with error:

Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click


Dim MyConnection As OleDb.OleDbConnection = Nothing
Dim MyTransaction As OleDb.OleDbTransaction = Nothing


Try


' create the connection and transaction object
MyConnection = New OleDb.OleDbConnection(My.Settings.FSSConnectionString)
MyConnection.Open()
MyTransaction = MyConnection.BeginTransaction


' insert the new recipt
Dim SQL As String = "insert into recipts (reciptdate,recipttotal) values (:0,:1)"
Dim CMD1 As New OleDb.OleDbCommand
CMD1.Connection = MyConnection
CMD1.Transaction = MyTransaction
CMD1.CommandText = SQL
CMD1.Parameters.AddWithValue(":0", Now.Date)
CMD1.Parameters.AddWithValue(":1", TextBox4.Text)
CMD1.ExecuteNonQuery()
CMD1.Dispose()


' get the id for the recipt
SQL = "select max(reciptid) as MAXID from recipts"
Dim CMD2 As New OleDb.OleDbCommand
CMD2.Connection = MyConnection
CMD2.Transaction = MyTransaction
CMD2.CommandText = SQL
Dim ReciptID As Long = CMD2.ExecuteScalar()
CMD2.Dispose()


' insert the details of the recipt
Dim I As Integer
For I = 0 To DataGridView1.Rows.Count - 1


' get the values
Dim ProductCode As String = DataGridView1.Rows(I).Cells(0).Value
Dim Price As Decimal = DataGridView1.Rows(I).Cells(2).Value
Dim SellPrice As Decimal = DataGridView1.Rows(I).Cells(3).Value
Dim Quantity As Integer = DataGridView1.Rows(I).Cells(4).Value


' next create a command
Dim CMD3 As New OleDb.OleDbCommand
SQL = "insert into ReciptDetails " & _
"(reciptid,productcode,quantity,price,sellprice) " & _
"values " & _
"(:0 ,:1 ,:2 ,:3 ,:4 )"
CMD3.Connection = MyConnection
CMD3.Transaction = MyTransaction
CMD3.CommandText = SQL
CMD3.Parameters.AddWithValue(":0", ReciptID)
CMD3.Parameters.AddWithValue(":1", ProductCode)
CMD3.Parameters.AddWithValue(":2", Quantity)
CMD3.Parameters.AddWithValue(":3", Price)
CMD3.Parameters.AddWithValue(":4", SellPrice)


CMD3.ExecuteNonQuery()
CMD3.Dispose()


Next




' all well save the changes
MyTransaction.Commit()


' close connection
MyTransaction.Dispose()
MyConnection.Close()
MyConnection.Dispose()


DataGridView1.Rows.Clear()
TextBox4.Text = ""


Catch ex As Exception
If MyTransaction IsNot Nothing Then
MyTransaction.Rollback()
End If
If MyConnection IsNot Nothing Then
If MyConnection.State = ConnectionState.Open Then
MyConnection.Close()
End If
End If


MsgBox(ex.Message, MsgBoxStyle.Critical Or MsgBoxStyle.OkOnly, "Error")
End Try


End Sub
Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click


Dim MyConnection As OleDb.OleDbConnection = Nothing
Dim MyTransaction As OleDb.OleDbTransaction = Nothing


Try


' create the connection and transaction object
MyConnection = New OleDb.OleDbConnection(My.Settings.FSSConnectionString)
MyConnection.Open()
MyTransaction = MyConnection.BeginTransaction


' insert the new recipt
Dim SQL As String = "insert into recipts (reciptdate,recipttotal) values (:0,:1)"
Dim CMD1 As New OleDb.OleDbCommand
CMD1.Connection = MyConnection
CMD1.Transaction = MyTransaction
CMD1.CommandText = SQL
CMD1.Parameters.AddWithValue(":0", Now.Date)
CMD1.Parameters.AddWithValue(":1", TextBox4.Text)
CMD1.ExecuteNonQuery()
CMD1.Dispose()


' get the id for the recipt
SQL = "select max(reciptid) as MAXID from recipts"
Dim CMD2 As New OleDb.OleDbCommand
CMD2.Connection = MyConnection
CMD2.Transaction = MyTransaction
CMD2.CommandText = SQL
Dim ReciptID As Long = CMD2.ExecuteScalar()
CMD2.Dispose()


' insert the details of the recipt
Dim I As Integer
For I = 0 To DataGridView1.Rows.Count - 1


' get the values
Dim ProductCode As String = DataGridView1.Rows(I).Cells(0).Value
Dim Price As Decimal = DataGridView1.Rows(I).Cells(2).Value
Dim SellPrice As Decimal = DataGridView1.Rows(I).Cells(3).Value
Dim Quantity As Integer = DataGridView1.Rows(I).Cells(4).Value


' next create a command
Dim CMD3 As New OleDb.OleDbCommand
SQL = "insert into ReciptDetails " & _
"(reciptid,productcode,quantity,price,sellprice) " & _
"values " & _
"(:0 ,:1 ,:2 ,:3 ,:4 )"
CMD3.Connection = MyConnection
CMD3.Transaction = MyTransaction
CMD3.CommandText = SQL
CMD3.Parameters.AddWithValue(":0", ReciptID)
CMD3.Parameters.AddWithValue(":1", ProductCode)
CMD3.Parameters.AddWithValue(":2", Quantity)
CMD3.Parameters.AddWithValue(":3", Price)
CMD3.Parameters.AddWithValue(":4", SellPrice)


CMD3.ExecuteNonQuery()
CMD3.Dispose()


Next




' all well save the changes
MyTransaction.Commit()


' close connection
MyTransaction.Dispose()
MyConnection.Close()
MyConnection.Dispose()


DataGridView1.Rows.Clear()
TextBox4.Text = ""


Catch ex As Exception
If MyTransaction IsNot Nothing Then
MyTransaction.Rollback()
End If
If MyConnection IsNot Nothing Then
If MyConnection.State = ConnectionState.Open Then
MyConnection.Close()
End If
End If


MsgBox(ex.Message, MsgBoxStyle.Critical Or MsgBoxStyle.OkOnly, "Error")
End Try


End Sub

 

[jmcilhinney]

Firstly, I have fixed the formatting in your post.

tags are just for quotes. As you would have seen, they did not help to make your code readable at all.

Secondly, if you're going to post code then please post just the RELEVANT code. With all that code you've posted, we first have to work out where it is that error is occurring and that is information that you already have and could have provided but instead obscured.

The code itself is unnecessarily long to begin with though. I keep seeing people looping through grids and calling ExecuteNonQuery. No! Use a DataTable bound to the grid and then save changes by calling Update on a data adapter.

Using a bound DataTable should fix your error too. It sounds like one of your cell values is Nothing, which cannot be saved to a database. If you want to save no value to a database then you must save a database NULL, which is represented by DBNull.Value rather than Nothing. If you use a bound DataTable then "empty" cells will be populated with a NULL value by default.

 

[siang93]

i'm saving the data into 'reciptdetail' table and 'recipts' table with relationship between it.can u give some code example on what u commend?please give some example on how to save
data from datagriedview into database.. sorry,i learn vb just a few month..tq​