Database Update

[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.

 

[Dale]

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)

' 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

Hope it helps...

Dale

 

[Guest]

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

 

[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