MsAccess Database Updation using Dataset

  • Thread starter Thread starter Vibhu
  • Start date Start date
V

Vibhu

Hello All,

I am trying to update a database using dataset but seems that I am
missing out something. After going through a lot of posts and checking
my code, I am still at my wits end to figure out how to do a simple
update.

Here is the code. Any help would be greatly appreciated.

Regards,

Vibhu.

Dim conn As OleDbConnection
Dim da As OleDbDataAdapter
Dim ds As DataSet
Dim cb As OleDbCommandBuilder
Dim cm As CurrencyManager

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 = New OleDbDataAdapter("select * from tblCourseMaster",
conn)
cb = New OleDbCommandBuilder(da)
ds = New DataSet
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
 
Vibhu,

You don't use a typed dataset (not that it is needed)
However, how do you think that the commandbuilder gets it information about
the different fields when you have told that it are *.

I would do the commandbuilder after the fill.

While I would not do that getupdate command. You have placed your
dataadapter global, so that should be still there.

This all was to prevent other errors, your main problem is that when the
button is clicked the data is not pushed yet in the datatable.

To force that you can set as first statement in your button event.

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

I hope this helps?

Cor
 
Even after I changed the query to reflect the column names and put the
commandbuilder after fill, it still did not update the database. Then
when I added the BindingContext line, it gave an error at the line
where the update is called.

Also, all the declarations are global so they will be available.

A first chance exception of type 'System.Data.OleDb.OleDbException'
occurred in system.data.dll
 
Also, just for information when I checked the CommandTextof the Update
command, I found that the parameters were missing and so were the
values. If this is a problem, then how can it be resolved.
 
Vibhu

It looks to me if you only readed the first line in my message?

There are 3 things you have to change in my opinion.

Cor
 
I tried all three and also in various combinations. But as soon as I
put the statement BindingContext(ds.Tables(0)).EndCurrentEdit() as the
first line on button click, then I start to get the error

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

Also, just for information when I checked the CommandText of the Update
command, I found that the parameters were missing and so were the
values. If this is a problem, then how can it be resolved.

This is the query that is being generated..

"UPDATE tblCourseMaster SET Course = ? WHERE ( (CourseCode = ?) AND ((?
= 1 AND Course IS NULL) OR (Course = ?)) )"
 
Vibhu,

Probably is the problem that it is now processing while it was before an
empty dataset and therefore did update nothing. Can you show me how you made
the corrections?

After that you have deleted these two as well (Because of the other problems
I did not had my eye on those before).

ds.Tables(0).Constraints.Add("pk", ds.Tables(0).Columns(0),
True)
cm = Me.BindingContext.Item(ds.Tables(0))
cb.GetUpdateCommand()

And it does still not work.

Cor
 
Cor,
I am still unable to get this working. I am putting the code again
after making all the changes. I still keep on getting the errors that I
mentioned above in my previuos post.
I am also online on Yahoo Messenger with the id vibhub.

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
BindingContext(ds.Tables(0)).EndCurrentEdit()
Try
da.UpdateCommand = cb.GetUpdateCommand
da.Update(ds, "tblCourseMaster")
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
 
Vibhu,

I asked you directly to
To change those from place (now in the way I do it)

da.Fill(ds, "tblCourseMaster")
cb = New OleDbCommandBuilder(da)

You told you did that , however did not.

As well did you not delete this one.

da.UpdateCommand = cb.GetUpdateCommand

Probably you started to add all kind of commands while that endcurrentedit
was the problem.

Maybe can you change those and than try it, I am curious if it works of
course.

Cor
 
Cor,
I am still unable to get this thing working. I think I am doing
something wrong somewhere and unable to comprehend what you are
telling.
After seeing your last post, here's the new code, of course with all
commented lines deleted and all suggestions incorporated.

Private Sub Button8_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button8.Click
BindingContext(ds.Tables(0)).EndCurrentEdit()
Try
da.Update(ds, "tblCourseMaster")
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub

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
da.Fill(ds, "tblCourseMaster")
cb = New OleDbCommandBuilder(da)
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)
End Sub
 
Vibhu,

You changed it a little bit more than in your first sample (I would have
seen that however did not make any attention to that) and with that deleted
probably the primary key from your datatable.

Without that it will not work.

You can add that primary key to your select or use the * in that and than
use in the binding just the columnname as string (in the right case). (I
would try the first option first)

Cor
 
Still got the error

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

with this code. Had also included da.UpdateCommand =
cb.GetUpdateCommand in this piece of code.


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
da.Fill(ds, "tblCourseMaster")
cb = New OleDbCommandBuilder(da)
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)
End Sub

Private Sub Button8_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button8.Click
BindingContext(ds.Tables(0)).EndCurrentEdit()
Try
da.UpdateCommand = cb.GetUpdateCommand
da.Update(ds, "tblCourseMaster")
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
 
Can you please send me the exact code that I should write because I
have tried almost all permutations and combinations now and am unable
to proceed further. If this does not work, then I will have no option
but to use a typed dataset using wizards which will not give me much
leverage in terms of coding.
 
Thanks. Achieved the data updation but on a different code altogather.
For future reference of others, here is the complete code..

Public Class Form2
Inherits System.Windows.Forms.Form
Dim da As OleDbDataAdapter
Dim ds As DataSet
Dim cb As OleDbCommandBuilder

#Region " Windows Form Designer generated code "....

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)
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

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

Delete this one, this is as well not right
da.SelectCommand = New OleDbCommand("select CourseCode, Course
from tblCourseMaster", conn)

and replace that with

da = New OleDbDataAdapter("select PrimaryKey, CourseCode, Course
from tblCourseMaster", conn)

Use the right primarykey

And than
TextBox1.DataBindings.Add("text", ds.Tables(0), "CourseCode")
TextBox2.DataBindings.Add("text", ds.Tables(0), "Couirse")

Know that with this you only changes the first row of your datatable.
So there are probably better solutions, however now probably you get that
updating done.

Cor
 
Vibhu,
Thanks. Achieved the data updation but on a different code altogather.
For future reference of others, here is the complete code..
Click to expand...

Beg your pardon,

It is complete different code I now see a endcurrentedit in your code, the
first thing I pointed you on.

It is complete different code than your samples because now is the primary
key in your code as I pointed you on.

It is complete different code because you constructed the dataadapter as I
pointed you on

It is complete different code because you now use the string names as I
pointed you on.

It is complete different code because that strange use of the
buildingmanager is not any more in it.

Thanks for learning me something about you Vibhu.

Cor
 
Hello Cor,

I was again trying to figure out the previous code but sometimes I was
able to get that working and sometimes not. This I tried after I posted
the actual working code. Need to figure out the reason why sample code
was behaving that way.

Thanks for your valuable help. I now need to work on Add, Delete and
Cancel and hopefully I'll be able to get that working.

Regards,

Vibhu.
 
Back
Top