Database Update

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello all,
I am using ADO.NET and trying to update the MsAccess database but
unable to do so.
Here is the code below that I used.

Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim sql As String
Connect()
sql = "select key, title from tblISBN"
ds = New DataSet
da = New OleDbDataAdapter(sql, conn)
da.Fill(ds, "tblISBN")
TextBox1.DataBindings.Add("Text", ds, "tblISBN.title")
TextBox2.DataBindings.Add("Text", ds, "tblISBN.key")
ds.Tables(0).Constraints.Add("PK", ds.Tables(0).Columns(0),
True)
End Sub

Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles cmdSave.Click
Dim cb As OleDbCommandBuilder
cb = New OleDbCommandBuilder(da)
Try
da.UpdateCommand = cb.GetUpdateCommand
da.Update(ds, "tblISBN")
Me.BindingContext(ds, "tblISBN").EndCurrentEdit()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub

It updates the database but when I check the actual msaccess database
file, the data is not updated in that.

Any help will be beneficial.

Thanking You,

Regards,

Vibhu.
 
If I understand the CommandBuilder concept correctly, then you will need to
instantiate the Commandbuilder prior to any changes being made to the
Dataset.
Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim sql As String
Connect()
sql = "select key, title from tblISBN"
ds = New DataSet
da = New OleDbDataAdapter(sql, conn)
Click to expand...

' This will allow "cb" to "see" changes that happen before you
call Update on the Adapter
cb = New OleDbCommandBuilder(da)
da.Fill(ds, "tblISBN")
TextBox1.DataBindings.Add("Text", ds, "tblISBN.title")
TextBox2.DataBindings.Add("Text", ds, "tblISBN.key")
ds.Tables(0).Constraints.Add("PK", ds.Tables(0).Columns(0),
True)
End Sub
Click to expand...

Hope it helps...

Dale
 
Hello
After doing what you mentioned it still didnot work
In fact on checking the update in watch, the query looks like this
"UPDATE tblISBN SET Title = ? WHERE ( (Key = ?) AND ((? = 1 AND Title IS
NULL) OR (Title = ?)) )"
Please let e know what needs to be done
Vibhu
 
Private Sub Form5_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles MyBase.Load
conn = New OleDbConnection
conn.ConnectionString = "PROVIDER=MICROSOFT.JET.OLEDB.4.0; DATA
SOURCE=C:\ceskpc1.mdb"
conn.Open()
da.SelectCommand = New OleDbCommand("select CourseCode, Course
from tblCourseMaster", conn)
ds = New DataSet
cb = New OleDbCommandBuilder(da)
da.Fill(ds, "tblCourseMaster")
ds.Tables(0).Constraints.Add("pk", ds.Tables(0).Columns(0),
True)
TextBox1.DataBindings.Add("text", ds.Tables(0),
ds.Tables(0).Columns(0).ToString)
TextBox2.DataBindings.Add("text", ds.Tables(0),
ds.Tables(0).Columns(1).ToString)
cm = Me.BindingContext.Item(ds.Tables(0))
cb.GetUpdateCommand()
End Sub

Private Sub Button8_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button8.Click
Try
da.UpdateCommand = cb.GetUpdateCommand
MsgBox(da.UpdateCommand.CommandText)
da.Update(ds, "tblCourseMaster")
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub

Even after changing the code, I am unable to save the data back into
the database. I donot think this is such a major issue and definitely I
am missing something here. I even tried adding the line

BindingContext(ds.Tables(0)).EndCurrentEdit()

before the Update command but then I start getting the error

A first chance exception of type 'System.Data.OleDb.OleDbException'
occurred in system.data.dll
 
Back
Top