1 Attachment(s)
update database with DataGridView
hi all
i work with vb.net 2005 and Access 2003
i have 2 table in databse( Orders and Order_Details )
table Orders fields=OrderID,custid,employeeid,OrderDate
table Order_Details fields=OrderDetailID,OrderID,ProductID,Quantity,UnitPrice
Relationships by OrderID between 2 table
i have a dataset that fill it with Order_Details filds .
then i fill datagirdview with this dataset and edit it.
finally i want to update the database but nothing happend.
please someone help me.
thanks
Code:
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Load_DataGridview()
End Sub
Private Sub Load_DataGridview()
Dim CNN As OleDbConnection
Dim DAP As OleDbDataAdapter
Dim DS As DataSet
Dim cnnstr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\db.mdb"
Dim SqlStr As String = "SELECT Order_Details.OrderDetailID,Order_Details.OrderID, Order_Details.ProductID, Order_Details.Quantity, Order_Details.UnitPrice FROM Order_Details;"
CNN = New OleDbConnection(cnnstr)
DAP = New OleDbDataAdapter(SqlStr, cnnstr)
DS = New DataSet
DAP.Fill(DS, "Order_Details")
DataGridView1.DataSource = DS.Tables("Order_Details")
DAP.Dispose()
End Sub
Code:
Private Sub ActionUpdate()
Dim intRowsAffected As Integer
Dim CNN As OleDbConnection
Dim Command As OleDb.OleDbCommand
Dim cnnstr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\db.mdb"
CNN = New OleDbConnection(cnnstr)
Dim SqlStr As String = "UPDATE Order_Details SET Order_Details.ProductID = [@Product],Order_Details.Quantity = [@Quantity],Order_Details.UnitPrice=[@UnitPrice]WHERE Order_Details.OrderID=[@OrderID]AND Order_Details.OrderDetailID=[@OrderDetailID]"
CNN.Open()
Command = New OleDbCommand(SqlStr, CNN)
Command.CommandType = CommandType.Text
For i As Integer = 0 To DataGridView1.Rows.Count - 1
Command.Parameters.Add("@Product", Data.OleDb.OleDbType.VarChar, 50).Value = DataGridView1.Rows(i).Cells(1).Value
Command.Parameters.Add("@Quantity", Data.OleDb.OleDbType.VarChar, 50).Value = DataGridView1.Rows(i).Cells(2).Value
Command.Parameters.Add("@UnitPrice", Data.OleDb.OleDbType.VarChar, 50).Value = DataGridView1.Rows(i).Cells(3).Value
Command.Parameters.Add("@OrderID", Data.OleDb.OleDbType.VarChar, 50).Value = DataGridView1.Rows(i).Cells(0).Value
Command.Parameters.Add("@OrderDetailID", Data.OleDb.OleDbType.VarChar, 50).Value = i + 1
intRowsAffected = Command.ExecuteNonQuery()
Next i
CNN.Close()
If intRowsAffected = 1 Then
MsgBox("update")
ElseIf intRowsAffected = 0 Then
MsgBox("not update")
End If
End Sub