Results 1 to 1 of 1

Thread: update database with DataGridView

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2008
    Posts
    1

    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
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width