MsAccess Database Updation using Dataset

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
 
C

Cor Ligthert

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
 
V

Vibhu

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
 
V

Vibhu

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

Cor Ligthert

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
 
V

Vibhu

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 = ?)) )"
 
C

Cor Ligthert

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
 
V

Vibhu

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
 
C

Cor Ligthert

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
 
V

Vibhu

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
 
C

Cor Ligthert

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
 
V

Vibhu

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
 
V

Vibhu

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

Vibhu

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
 
C

Cor Ligthert

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
 
C

Cor Ligthert

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
 
V

Vibhu

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

getting error on record duplication 18
error on adding record to database 2
dataset access 4
Updating DataSet using InsertCommand property... 1
Calling SelectedIndexChanged from initial comboBox fill 2
deleted row information cannot be accessed... 5
ADO.NET Problem write or wrong 12
problem reading array data from structure 20

Top