Cannot Update Database

[Patty05]

I am going crazy trying to do the simplest thing in my code. I just want to update a database with new data. I cannot get this test code to work and I've googled for help for 4 days and even copied code word for word in some cases!!! The form loads correctly the problem is in the button click event. Can someone please tell me what is wrong with this code???? I recieve this error during runtime: An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll. Thank you so much in advance.

Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load

myOleDbConnection = New _
OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0;" _
& "User ID=Admin;Data Source=C:\Test\test.mdb")
Try
myOleDbDataAdapter = _
New OleDbDataAdapter("select * from adminPassword", _
myOleDbConnection)
myOleDbDataAdapter.Fill(myDataTable)
lstAboutVBData.DataSource = myDataTable
lstAboutVBData.DisplayMember = "password"
Catch
Console.WriteLine("Error Opening {0}", _
myOleDbConnection.DataSource)
End Try

myCommand = New OleDbCommandBuilder(myOleDbDataAdapter)
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

myOleDbDataAdapter.Fill(myDataSet, "adminPassword")
myDataTable = myDataSet.Tables("adminPassword")

Dim rowArticle As DataRow = myDataTable.NewRow()
rowArticle("password") = txtTitle.Text
rowArticle("name") = "Don"
myDataTable.Rows.Add(rowArticle)

Dim newDataSet As DataSet
newDataSet = myDataSet.GetChanges()

'THE PROBLEM SEEMS TO BE HERE
myOleDbDataAdapter.Update(newDataSet, "adminPassword")
myDataSet.Tables("adminPassword").AcceptChanges()
End Sub
End Class

 

[Patty05]

Just in case, the database has 2 columns, name and password, with name as the primary key. Both fields are text. I'm not sure if that will help, but it seems through all the reading I've done, the primary key has something to do with updating. Thanks again for any help!

 

[vis781]

It seems like you haven't got the update command from your commandbuilder object.

Dim MyUpdateCommand As OleDbCommand
 
MyUpdateCommand = Mycommand.GetUpdateCommand
 
MyOleDataAdapter.UpdateCommand = MyUpdateCommand

I don't use the command builder myself, but i think i've seen the article that you are using and it isn't a very good example of how to use ADO.Net, infact it over complicates it somewhat.

 

[Patty05]

I have added your code to mine and even put in a msgbox to show the update command as follows

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

myOleDbDataAdapter.Fill(myDataSet, "adminPassword")
myDataTable = myDataSet.Tables("adminPassword")
Dim rowArticle As DataRow = myDataTable.NewRow()
rowArticle("password") = txtTitle.Text
rowArticle("name") = "Don"
myDataTable.Rows.Add(rowArticle)
Dim newDataSet As DataSet
newDataSet = myDataSet.GetChanges()

Dim MyUpdateCommand As OleDbCommand
MyUpdateCommand = myCommand.GetUpdateCommand
myOleDbDataAdapter.UpdateCommand = myCommand.GetUpdateCommand

MsgBox(myCommand.GetUpdateCommand.CommandText.ToString)

'THE PROBLEM SEEMS TO BE HERE
myOleDbDataAdapter.Update(newDataSet, "adminPassword")
myDataSet.Tables("adminPassword").AcceptChanges()
End Sub

The update command reads in msg box:

UPDATE adminPassword Set password = ?, name=? WHERE ( ((?=1 AND password IS NULL) OR (password=?)) AND (name=?))

and I am still getting the error.

I don't know what to do to get my program to update the database. Can you please continue to help?

Thanks so much.

 

[Patty05]

Is there a better way to update the database? All I want to do in my main program is have a user click a button to update a couple of date fields in a database. Is there a better way??? I know it is asking alot, but could you offer some simple code (be throughough because I am relatively new) to do this simple action.

For example, I have a database with a few fields, but I only want to write to 3 of the fields, Emp#, date, and timeIn. Emp# is a global variable, date=Today(), and timeIn is the TimeOfDay().

It is such a simple thing I want to do, but I just am spending days trying to figure out this crazy syntax. Its so frustrating!

Thank you so very much!

 

[vis781]

Reply to your first post......

PrivateSub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 
 
Dim rowArticle As DataRow = myDataTable.NewRow()
rowArticle("password") = txtTitle.Text
rowArticle("name") = "Don"
myDataTable.Rows.Add(rowArticle)
 
Dim MyUpdateCommand As OleDbCommand
MyUpdateCommand = myCommand.GetUpdateCommand
myOleDbDataAdapter.UpdateCommand = MyUpdateCommand
 
'THE PROBLEM SEEMS TO BE HERE
myOleDbDataAdapter.Update(MyDataTable)
EndSub

Thats how it should look to me. If it still doesn't work then i'd need to check it out on my PC, but from memory it all looks ok. You have to have sure that Don doesn't exist already in the database as it is the primary key.

Reply to your second post...

Yes, i can knock something up a bit later for you to take a look at.

 

[Patty05]

Wellllllll....... tried your code and for some crazy reason it did not work. No, Don is not in db in any row as primary key. I am so sorry, but I am sure you can understand my frustration. I'm still getting the same error message on the myOleDbDataAdapter.Update(myDataTable) line.

Again... here is the code I am using:

Dim myOleDbDataAdapter As OleDbDataAdapter
Dim myDataSet As DataSet = New DataSet
Dim myDataTable As DataTable = New DataTable
Dim myOleDbConnection As OleDbConnection
Dim myCommand As OleDbCommandBuilder
Dim artKeySave AsInteger
PrivateSub Form1_Load(ByVal sender AsObject, ByVal e As System.EventArgs) HandlesMyBase.Load
myOleDbConnection = New _
OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0;" _
& "User ID=Admin;Data Source=C:\Test\test.mdb")
Try
myOleDbDataAdapter = _
New OleDbDataAdapter("select * from adminPassword", _
myOleDbConnection)
myOleDbDataAdapter.Fill(myDataTable)
lstAboutVBData.DataSource = myDataTable
lstAboutVBData.DisplayMember = "password"
Catch
Console.WriteLine("Error Opening {0}", _
myOleDbConnection.DataSource)
EndTry
myCommand = New OleDbCommandBuilder(myOleDbDataAdapter)
EndSub
 
PrivateSub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim rowArticle As DataRow = myDataTable.NewRow()
rowArticle("password") = txtTitle.Text
rowArticle("name") = "Don"
myDataTable.Rows.Add(rowArticle)
Dim MyUpdateCommand As OleDbCommand
MyUpdateCommand = myCommand.GetUpdateCommand
myOleDbDataAdapter.UpdateCommand = myCommand.GetUpdateCommand
'THE PROBLEM SEEMS TO BE HERE
myOleDbDataAdapter.Update(myDataTable)
 
EndSub
EndClass

Thank you again for your help.

 

[vis781]

Dim MyUpdateCommand As OleDbCommand
MyUpdateCommand = myCommand.GetUpdateCommand
myOleDbDataAdapter.UpdateCommand = myCommand.GetUpdateCommand

That bit above should look like this....

Dim MyUpdateCommand As OleDbCommand
MyUpdateCommand = myCommand.GetUpdateCommand
myOleDbDataAdapter.UpdateCommand = MyUpdateCommand

But i don't think that is our problem. I was just thinking that this couldn't be problem with a keyword. It is possible that 'name' is a reserved word, i don't have the list so i can't be sure. Otherwise i can't really see a lot wrong with your code. Put a try catch block around the update line and post the error message it displays.

Try
myOleDbDataAdapter.Update(myDataTable)
Catch Ex as Exception
Messagebox.show(ex.Message)
End Try

 

[Patty05]

vis781 - you are a God! I don't know if you are male or female... at this point I don't care - but I could just kiss you ALL over!!!!!!!!!

The problem was a syntax error in UPDATE . So, I changed the database field names to other names, and WALA... it works like a charm in my test program. I just hope it all works ok in my real program with an AutoUpdate field in database as a primary key.

I am so excited and so relieved - you just have no idea. Thank you a million times over for your help!!!!!