Loading

Update Problem in Multiple Table

2 posts / 0 new
Last post
Offline
Joined: 07/21/2010
Update Problem in Multiple Table

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
admin's picture
Offline
Joined: 11/14/2008
See this example: How to

Pages

Add new comment

Filtered HTML

  • You may insert videos with [video:URL]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.