Dit is de code die ik gebruik
Private Sub saveOrderbutton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles saveOrderbutton.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.DB2ConnectionString)
MyConnection.Open()
MyTransaction = MyConnection.BeginTransaction
' insert the new recipt
Dim SQL As String = "insert into Bonnr (Bondatum) values
0)"
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", aantaldagtext.Text)
CMD1.ExecuteNonQuery()
CMD1.Dispose()
' get the id for the recipt
SQL = "select max(Bonid) as MAXID from Bonnr"
Dim CMD2 As New OleDb.OleDbCommand
CMD2.Connection = MyConnection
CMD2.Transaction = MyTransaction
CMD2.CommandText = SQL
Dim ReciptID As Integer = CMD2.ExecuteScalar()
CMD2.Dispose()
' insert the details of the recipt
Dim I As Integer
For I = 0 To DGV3.Rows.Count - 1
' get the values
Dim Itemid As Integer = DGV3.Rows(I).Cells(0).Value
'' Dim itemnm As String = DGV3.Rows(I).Cells(1).ToString
Dim SellPrice As Decimal = DGV3.Rows(I).Cells(2).Value
Dim ItemCount As Integer = DGV3.Rows(I).Cells(3).Value
Dim klantid As Integer = Klid.Text
Dim aantaldag As Integer = aantaldagtext.Text
Dim klantnaam As String = Klnaam.Text
Dim klantadres As String = Kladres.Text
Dim klantpostcode As String = Klpostcode.Text
Dim klantcontact As String = Klantcontacttext.Text
Dim ext1 As String = extra1.Text
Dim ext2 As String = extra2.Text
Dim ext3 As String = extra3.Text
Dim ext4 As String = extra4.Text
Dim dingang As Date = datumingang.Value
Dim deinde As Date = datumbeeindig.Value
Dim daf As Date = datumaflever.Value
Dim dop As Date = datumophaal.Value
Dim tin As String = tijdingang.Text
Dim teind As String = tijdbeeindigd.Text
Dim taf As String = tijdaflever.Text
Dim top As String = tijdophaal.Text
Dim ref As String = referentietext.Text
' next create a command
Dim CMD3 As New OleDb.OleDbCommand
SQL = "insert into Bondetails" & _
"(Bonid,Itemid,Itemnaam,Itemprijs,Itemaantal,Klantid,Klantnaam,Klantadres,Klantpostcode,Klantcontact,Extra1,Extra2,Extra3,Extra4,Datumingang,Tijdingang,Datumbeeindigd,tijdbeeindigd,Datumaflever,tijdaflever,Datumophaal,tijdophaal,Aantaldagen,Referentie)" & _
"values " & _
"
0 ,:1 ,:2,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:9 ,:10 ,:11 ,:12 ,:13 ,:14 ,:15 ,:16 ,:17 ,:18 ,:19 ,:20 ,:21 ,:22 ,:23 )"
CMD3.Connection = MyConnection
CMD3.Transaction = MyTransaction
CMD3.CommandText = SQL
CMD3.Parameters.AddWithValue(":0", ReciptID)
CMD3.Parameters.AddWithValue(":1", Itemid)
CMD3.Parameters.AddWithValue(":2", DGV3.Rows(I).Cells(1).FormattedValue)
CMD3.Parameters.AddWithValue(":3", SellPrice)
CMD3.Parameters.AddWithValue(":4", ItemCount)
CMD3.Parameters.AddWithValue(":5", klantid)
CMD3.Parameters.AddWithValue(":6", klantnaam)
CMD3.Parameters.AddWithValue(":7", klantadres)
CMD3.Parameters.AddWithValue(":8", klantpostcode)
CMD3.Parameters.AddWithValue(":9", klantcontact)
CMD3.Parameters.AddWithValue(":10", ext1)
CMD3.Parameters.AddWithValue(":11", ext2)
CMD3.Parameters.AddWithValue(":12", ext3)
CMD3.Parameters.AddWithValue(":13", ext4)
CMD3.Parameters.AddWithValue(":14", dingang)
CMD3.Parameters.AddWithValue(":15", tin)
CMD3.Parameters.AddWithValue(":16", deinde)
CMD3.Parameters.AddWithValue(":17", teind)
CMD3.Parameters.AddWithValue(":18", daf)
CMD3.Parameters.AddWithValue(":19", taf)
CMD3.Parameters.AddWithValue(":20", dop)
CMD3.Parameters.AddWithValue(":21", top)
CMD3.Parameters.AddWithValue(":22", aantaldag)
CMD3.Parameters.AddWithValue(":23", ref)
CMD3.ExecuteNonQuery()
CMD3.Dispose()
Next
' all well save the changes
MyTransaction.Commit()
' close connection
MyTransaction.Dispose()
MyConnection.Close()
MyConnection.Dispose()
DGV3.Rows.Clear()
itemnrtext.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
