Question OledbDataAdaptor fails to update database

sapkichu

New member
Joined
Oct 10, 2009
Messages
3
Programming Experience
Beginner
Friends,

I am a beginner to VB.net programming and Im trying some DB application. Using Visual Studio 2008 and MS Access 2003 . The following code is filling dataset correctly, but fails to add the newly added row to database. There is no error/exception. Could you please help in getting this corrected?

Dim connection As OleDbConnection
Dim adaptor As OleDbDataAdapter
Dim dset As DataSet
Dim cb As OleDbCommandBuilder

dset = New DataSet()
connection = New OleDbConnection(ConnectionString)
adaptor = New OleDbDataAdapter()
adaptor.SelectCommand = New OleDbCommand(GetAllPatients, connection)
cb = New OleDbCommandBuilder(adaptor)
connection.Open()
adaptor.Fill(dset, "Patients")

' add new row to dataset
Console.WriteLine(dset.Tables(0).Rows.Count.ToString)
Dim table As DataTable = dset.Tables("Patients")
Dim row As DataRow = table.NewRow()

row("PatientID") = Convert.ToString((dset.Tables(0).Rows.Count + 1))
row("FirstName") = TxtFirstName.Text
Row("Surname") = TxtSurname.Text
Row("Age") = Txtage.Text
If RbtMale.Checked = True Then
Row("Gender") = "Male"
ElseIf Rbtfemale.Checked = True Then
Row("Gender") = "Female"
End If
Row("HouseName") = TxtHouseName.Text
Row("Street") = TxtStreet.Text
Row("Post") = TxtPost.Text
Row("Place") = Txtplace.Text
Row("District") = TxtDistrict.Text
Row("State") = TxtState.Text
Row("PinCode") = TxtPinCode.Text
Row("Phone1") = TxtPhone.Text
Row("Phone2") = TxtAltPhone.Text
Row("Mobile") = TxtMobile.Text
Row("Email1") = TxtEmail.Text
Row("Email2") = TxtAltEmail.Text
Row("DateOfEntry") = Now.Date
Row("Notes") = Rtbnotes.Text

dset.Tables(0).Rows.Add(row)
Console.WriteLine(dset.Tables(0).Rows.Count.ToString)

adaptor.Update(dset, "Patients")
dset.AcceptChanges()


connection.Close()
 

Tom

Well-known member
Joined
Aug 23, 2005
Messages
746
Programming Experience
5-10
You created a commandbuilder based on your dataadapter's select command. Your commandbuilder now holds the generated commands but your dataadapter still doesnt have those commands.

Code:
   adaptor.SelectCommand = New OleDbCommand(GetAllPatients, connection)
   cb = New OleDbCommandBuilder(adaptor)
   
    adapter.InsertCommand = cb.GetInsertCommand
    adapter.DeleteCommand = cb.GetDeleteCommand
    adapter.UpdateCommand = cb.GetUpdateCommand

    'connection.Open() (not needed)
    adaptor.Fill(dset, "Patients")
 

JohnH

VB.NET Forum Moderator
Staff member
Joined
Dec 17, 2005
Messages
15,334
Location
Norway
Programming Experience
10+
Calling Get-Command and assigning them is not necessary here, the builder provides for empty commands during Update. Opening and closing the connection is also something the adapter takes care of. AcceptChangesDuringFill property of adapter is True by default so you don't need to call AcceptChanges again. Are you sure the Update didn't add the row? What number did the Update function return?
 

Tom

Well-known member
Joined
Aug 23, 2005
Messages
746
Programming Experience
5-10
Calling Get-Command and assigning them is not necessary here, the builder provides for empty commands during Update.
I'm not following you here, can you explain further. The first example below would throw an error.

Error Msg: "Object reference not set to an instance of an object."
Code:
[COLOR="Blue"]Using [/COLOR]con [COLOR="blue"]As New[/COLOR] SqlConnection(conBldr.ToString)
    [COLOR="blue"]Dim [/COLOR]da [COLOR="blue"]As New [/COLOR]SqlDataAdapter([COLOR="DarkRed"]"Select * From tblItems"[/COLOR], con)
    [COLOR="blue"]Dim [/COLOR]ds [COLOR="blue"]As New [/COLOR]DataSet
    [COLOR="blue"]Dim [/COLOR]bldr [COLOR="blue"]As New [/COLOR]SqlCommandBuilder(da)

    da.Fill(ds)
    Console.WriteLine([COLOR="darkred"]"da Update Command : {0}"[/COLOR], da.UpdateCommand.CommandText)
[COLOR="blue"]End Using[/COLOR]
No Error(s)
Code:
[COLOR="Blue"]Using [/COLOR]con [COLOR="blue"]As New[/COLOR] SqlConnection(conBldr.ToString)
    [COLOR="blue"]Dim [/COLOR]da [COLOR="blue"]As New [/COLOR]SqlDataAdapter([COLOR="DarkRed"]"Select * From tblItems"[/COLOR], con)
    [COLOR="blue"]Dim [/COLOR]ds [COLOR="blue"]As New [/COLOR]DataSet
    [COLOR="blue"]Dim [/COLOR]bldr [COLOR="blue"]As New [/COLOR]SqlCommandBuilder(da)

    da.Fill(ds)
    da.UpdateCommand = bldr.GetUpdateCommand
    Console.WriteLine([COLOR="darkred"]"da Update Command : {0}"[/COLOR], da.UpdateCommand.CommandText)
[COLOR="blue"]End Using[/COLOR]
 

sapkichu

New member
Joined
Oct 10, 2009
Messages
3
Programming Experience
Beginner
Update returns 1 .
Still its not updating table in Access DB. If I press save button again and reset the PatientID(Primary key) to the ID to previous value it gives exeption for duplicate record. This proves that dataset is filled with new row.

Is it failing because Update returns before it actually commits in DB? Should I do some commit / commit work ..something like that.. Please help
 

sapkichu

New member
Joined
Oct 10, 2009
Messages
3
Programming Experience
Beginner
Issue resolved, But Why it happend?

Thanks a lot for all replies..

The problem was in the connection string. The first one below,(commented) while worked for populating rows from DB to dataset, but failed in updating values back to DB. Any idea why it behaved like that?

Can I have any way to specify data source without specifying full path and still update db successfully?

'Public Const ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Clinic.mdb;"

Public Const ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=H:\Shakthi Clinic\Clinic\Clinic\clinic.mdb"
 

JohnH

VB.NET Forum Moderator
Staff member
Joined
Dec 17, 2005
Messages
15,334
Location
Norway
Programming Experience
10+
I'm not following you here, can you explain further.
The builder was created from a adapter with a valid Select command, which means it's connected to the adapter (exposed by builder.Adapter property). When you call adapter.Update the builder will provide the genereted commands if none is set. No command object is set for the adapter though, and none is needed. In short the original posted code is perfectly valid. Try it and you'll see that it works.
Update returns 1 .
...
The problem was in the connection string.
Return value 1 mean one row was affected by the Update (the added row). Your db is probably included in project, and it's Copy To Output mode is 'copy always' by default, this means that the empty project db is copied to runtime directory every build. You can change this mode to for example 'copy if newer'.
 

Tom

Well-known member
Joined
Aug 23, 2005
Messages
746
Programming Experience
5-10
The builder was created from a adapter with a valid Select command, which means it's connected to the adapter (exposed by builder.Adapter property). When you call adapter.Update the builder will provide the genereted commands if none is set. No command object is set for the adapter though, and none is needed. In short the original posted code is perfectly valid. Try it and you'll see that it works.
Thanks John. As you can see my check was only to see if the cb put the action command(s) in the dataadapter; which it wasnt showing when I called to display the statements, unless explicitly assigned first but I havent gone further in actually using it to update my db for the test. Personally I prefer typed datasets and writing each of my commands out in stored procedures and stay away from command builders.
 

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,052
Programming Experience
10+
Friends,

I am a beginner to VB.net programming and Im trying some DB application. Using Visual Studio 2008 and MS Access 2003 . The following code is filling dataset correctly, but fails to add the newly added row to database. There is no error/exception. Could you please help in getting this corrected?
Read the DNU link in my signature
 
Top Bottom