Problem updating a record

[jpinto]

Hi,

I've the following code used to update a record on my Access database:

con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=""C:\Programaçao\Visual Basic\Inapal\ReportIt\ReportIt.mdb"";"
con.Open()
sSQL = "SELECT * FROM [Utilizadores] WHERE (User= '" & Utilizador & "')"
da = New OleDb.OleDbDataAdapter(sSQL, con)
ds.Clear()
da.Fill(ds, "Utilizadores")
ds.Tables(0).Rows(0).Item("Password") = NewPasswordTxtBx.Text
da.Update(ds, "Utilizadores")
con.Close()
Me.Close()

When I try to update the record I get the following error: "Update requires a valid UpdateCommand when passed DataRow collection with modified rows"

Can annyone please help me?
Thanks,

João Pinto

 

[techsmith]

Have you set the UpdateCommand property of the OleDbDataAdapter?

 

[jebenson]

The DataAdapter has three command objects which handle adding, changing or deleting data from the database: the InsertCommand, UpdateCommand and DeleteCommand. Since you are trying to change existing data in your database, you need to supply an UpdateCommand to the dataadapter. For information on ADO .NET, check out this link:

ADO.NET Help, ADO.NET Tutorials, ADO.NET Programming, ADO.NET Code

The articles titled "All You Need To Know About ADO.NET: Part 1/2" and "All You Need To Know About ADO.NET: Part 2/2" should help.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira

 

[jpinto]

I didn't find anny help on the link that you provided.

Can annyone help me with the above code, please?

Thanks,

João Pinto

 

[jpinto]

Now I've the following code:

Imports System.Data.OleDb

con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=""C:\Programaçao\Visual Basic\Inapal\ReportIt\ReportIt.mdb"";"
con.Open()
sSQL = "SELECT * FROM [Utilizadores] WHERE (User= '" & Utilizador & "')"
da = New OleDb.OleDbDataAdapter(sSQL, con)
ds.Clear()
da.Fill(ds, "Utilizadores")
sSQL = "UPDATE Utilizadores SET Password='" & NewPasswordTxtBx.Text & "' WHERE (User= '" & Utilizador & "')"
da.UpdateCommand = New OleDbCommand(sSQL, con)
da.Update(ds, "Utilizadores")
con.Close()

But this is not updating the records. I don't have any error message but the records don't update.

Can annyone help me please?

Thanks,

João Pinto

http://senhorpires.blogspot.com/

 

[jebenson]

If you are trying to do the update through the dataset/dataadapter, you need to actually change the value in the dataset. This will set the RowState of the row that was modified to DataRowState.Modified. This is what the dataadapter uses when doing the update. If no rows show as DataRowState.Modified, then no update will be done. To do what you want, try this:

con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=""C:\Programaçao\Visual Basic\Inapal\ReportIt\ReportIt.mdb"";"
con.Open()
sSQL = "SELECT * FROM [Utilizadores] WHERE (User= '" & Utilizador & "')"
da = New OleDb.OleDbDataAdapter(sSQL, con)
ds.Clear()
da.Fill(ds, "Utilizadores")

[red]ds.Tables("Utilizadores").Rows(0).Item("Password") = NewPasswordTxtBx.Text[/red]

sSQL = "UPDATE Utilizadores SET Password=@Password WHERE (User= '" & Utilizador & "')"
da.UpdateCommand = New OleDbCommand(sSQL, con)
[red]
da.UpdateCommand.Parameters.Add("@Password", OleDbType.VarChar)
da.UpdateCommand.Parameters("@Password").SourceColumn = "Password"[/red]

da.Update(ds, "Utilizadores")
con.Close()


However, if you are only updating one row in the table, using a dataadapter and dataset is a little bit of overkill. You can do this much more easliy with just an OleDbCommand:

con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=""C:\Programaçao\Visual Basic\Inapal\ReportIt\ReportIt.mdb"";"
con.Open()

Dim cmd as OleDbCommand

cmd=New OleDbCommand()
cmd.Connection = con
cmd.CommandType = CommandType.Text

sSQL = "UPDATE Utilizadores SET Password='" & NewPasswordTxtBx.Text & "' WHERE (User= '" & Utilizador & "')"

cmd.CommandText = sSQL

cmd.ExecuteNonQuery()

con.Close()

cmd.Dispose()
cmd = Nothing

This avoids the whole mess of creating a dataset, assigning the command, etc. The only time I do that is when I am updating more than one record at a time.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira

 

[jpinto]

I've tryed with the OleDbCommand code that you provided but I get a sintax error on the UPDATE.

I don't see nothing wrong on your code, do you?

Thanks,

João Pinto

 

[djj55]

Make sure the quotes are ok. Is the error from VB or database?
djj

 

[jebenson]

Try this:

Try
cmd.ExecuteNonQuery()
Catch ex as Exception
MsgBox(ex.Message)
End Try

Hopefully this will give you a better error message, including what is actually causing the problem.



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira

 

[jpinto]

Jebenson,

Thanks for your help but I get the same error message saying that there is a sintax error on the UPDATE. Do you see anything wrong on the code? Maybe the SQL statment is not correct?

João Pinto

 

[jebenson]

I don't see anything wrong with the SQL either.


Try putting a breakpoint on this line:

cmd.CommandText = sSQL

Then in Access create a query with the text from sSQL and run it. Maybe this will give you a more descriptive error message.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira

 

[jpinto]

Even tryed to use the code provided by you for update throught dataset/dataadapter but I get the same sintax error message.

I've tryed to do what you told me on access but I didn't got more information about the error.

João Pinto

 

[jebenson]

Okay, the only other thing I can think of right now:

Is there an apostrophe in either the password or the username you are using?

Oh, also, try removing the parentheses from around the where clause:

"' WHERE (User= '" & Utilizador & "')"

becomes

"' WHERE User= '" & Utilizador & "'"



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira

 

[jpinto]

I still get the sintax error message on the da.Update(ds, "Utilizadores") line of the code!

João Pinto

 

[jpinto]

Annyone can help, please? I'm stuck with this problem for days!

Thanks,

João Pinto

 

[jebenson]

I'm sorry, but I can't see anything wrong with the code. Is the User field a text field?

At this point I think you should be looking at the SQL, not the VB code. The ANSI SQL forum may be a good place to attempt to resolve this problem:

forum220



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira

 

[djj55]

Hello jpinto,
Please post your current code.
Also if you could post what sSQL has as text at the time of the call. (Set break after sSql is defined, then in VBs command window type ?ssql)
Thanks
djj

 

[jpinto]

The problem was with the field "Password" on the SQL statment because "Password" is a reserved word from VB. So is as simple as doing this:

sSQL = "UPDATE Utilizadores SET [Password]=@Password WHERE User= '" & Utilizador & "'"

instead of this:

sSQL = "UPDATE Utilizadores SET Password=@Password WHERE User= '" & Utilizador & "'"

Thanks to all of you for your help on trying to solve this problem.

João Pinto

 

[jebenson]

I actually looked up the ACCESS keywords, and Password wasn't one of them. It didn't occur to me that it is a VB keyword.

DOH!

Well, I'm glad you finally got it.



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira