1. Forums
  2. Programming
  3. Visual Basic .NET
  4. Update Problem in Multiple Table

Update Problem in Multiple Table

Submitted by Naveed_786 on
Dear All, I have designed a form for a table Instalment from this form i am trying to update a column in other table which is hiring table please guide me how can i do this the code is as folloes thanks in advance.
  1. Dim cnString As String
  2.  
  3.         cnString = ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\PaymentSystem\PaymentSystem\bin\Debug\Payment.mdb;")
  4.  
  5.  
  6.         Dim sqlQRY As String = "SELECT * FROM Instalment"
  7.         'Dim sqlQRY1 As String = "UPDATE hiring(TotalAmount)SET" & _
  8.         '" TotalAmount = " & TextBox7.Text & "WHERE PlotNo =  & TextBox1.Text"
  9.         'Dim sqlQRY1 As String = "Select * from hiring"
  10.         'Create connection
  11.         Dim conn As OleDbConnection = New OleDbConnection(cnString)
  12.  
  13.         Try
  14.             ' Open connection
  15.             conn.Open()
  16.  
  17.             'create data adapter
  18.             Dim da As OleDbDataAdapter = New OleDbDataAdapter(sqlQRY, conn)
  19.             Dim da1 As OleDbDataAdapter = New OleDbDataAdapter(sqlQRY1, conn)
  20.             'create command builder
  21.             Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(da)
  22.             Dim cb1 As OleDbCommandBuilder = New OleDbCommandBuilder(da1)
  23.             'create dataset
  24.             Dim ds As DataSet = New DataSet
  25.             Dim ds1 As DataSet = New DataSet
  26.             'fill dataset
  27.             da.Fill(ds, "Instalment")
  28.             da1.Fill(ds1, "Hiring")
  29.             'get data table
  30.             Dim dt As DataTable = ds.Tables("Instalment")
  31.             Dim dt1 As DataTable = ds1.Tables("Hiring")
  32.             Dim newRow As DataRow = dt.NewRow()
  33.             Dim newrow1 As DataRow = dt1.NewRow()
  34.             newRow("PlotNo") = TextBox1.Text
  35.             newRow("CustomerName") = TextBox2.Text
  36.             newRow("Fname") = TextBox3.Text
  37.             newRow("Area") = TextBox4.Text
  38.             newRow("TotalAmount") = TextBox5.Text
  39.             newRow("RecivedAmount") = TextBox6.Text
  40.             newRow("RemainingAmount") = TextBox7.Text
  41.             newRow("PaymentDate") = TextBox8.Text
  42.             newRow("PaymentReciverName") = TextBox9.Text
  43.             newrow1("TotalAmount") = TextBox7.Text
  44.             dt.Rows.Add(newRow)
  45.             'dt1.Rows.Add(newrow1)
  46.             'updat Instalment and Hiring table
  47.             If TextBox1.Text = ds.Tables("Instalment").Rows(1).Item(1) Then
  48.  
  49.                 Dim sqlQRY1 As String = "UPDATE hiring(TotalAmount)SET" & _
  50.                 " TotalAmount = " & TextBox7.Text & "WHERE PlotNo =  & TextBox1.Text"
  51.             End If
  52.             da.Update(ds, "Instalment")
  53.             da1.Update(ds1, "Hiring")
  54.             MsgBox("Record successfully saved...", MsgBoxStyle.Information)
  55.         Catch ex As OleDbException
  56.             MsgBox("Error: " & ex.ToString & vbCrLf)
  57.         Finally
  58.             ' Close connection
  59.             conn.Close()
  60.         End Try