Update statement not working?

M

mrdutchie

Well-known member
Joined
Jul 7, 2003
Messages
78
Location
WI, USA
Im stuck with my update statement for a access2000 file.

I have a table called email with 2 fields. (name and email)
I can Add to it just fine, but I cant update.
This is what I have

in my access table I have

Name Email
perry perry@hotmail.com
peter peter@hotmail.com



value1 = "newname"
Dim mycn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:/donation.mdb")

Dim mycmd As OleDbCommand = New OleDbCommand
mycmd.Connection = mycn
mycmd.CommandText = "UPDATE email SET name=value1 WHERE name=perry AND email=perry@hotmail.com)"
mycn.Open()
mycmd.ExecuteNonQuery()
mycn.Close()

it will fail on mycmd.ExecuteNonQuery()

What am I doing wrong?
 
I dont know any SQL, but from the looks of your command the
variable value1 needs to be outside the string so it is added to
the command as "newname" rather than "value1":

Code: 
mycmd.CommandText = "UPDATE email SET name=" & value1 & " WHERE name=perry AND email=perry@hotmail.com)"
 
Also, you forgot the single quotes around the name value:
Code: 
myCmd.CommandText = "UPDATE email SET name=" & value1 & " WHERE etc"
Also, youll probably want to make sure that all single quotes in the value1 string are doubled up, so it doesnt cause syntax errors:

James ONeill would need to be added in the SQL statement as James ONeill, so
Code: 
value1 = value1.Replace("", "")
 
Still doesnt work..

I am getting Object reference not set to an instance of an object
with

mycmd.CommandText
and mycmd.ExecuteNonQuery()

so something else must be wrong too.
 
Your declaration for mycmd must be incorrect, then. It should be
something like this:

Code: 
Dim mycmd As OleDbCommand = New OleDbCommand()

In fact, to save a few lines of code, you could set the commands
connection and command text right in its constructor:
Code: 
Dim mycmd As OleDbCommand = New OleDbCommand("UPDATE email SET name=" & value1 & " WHERE name=perry AND email=perry@hotmail.com", mycn)

If that does not solve your problem, then mycn must not be
initialized. Either you didnt initialize it yet, or the initialization
failed.
 
now I am really puzzled.

getting no error this time, but it does not update either

Dim mycn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:/donation.mdb")

Dim mycmd As OleDbCommand = New OleDbCommand("UPDATE email SET name=hello WHERE name=perry AND email=perry@hotmail.com", mycn)

mycn.Open()
mycmd.ExecuteNonQuery()
mycn.Close()
 
please forget my last comment.
Its working now...
forgot that "perry@hotmail.com" was not in the database. duh....

Thanks so much for helping me out.
 
Back
Top