Update statement not working?

[mrdutchie]

I'm 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 can't 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?

[Bucky]

I don't 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":

 

mycmd.CommandText = "UPDATE email SET name=" & value1 & " WHERE name='perry' AND email='perry@hotmail.com')"

[Volte]

Also, you forgot the single quotes around the name value:

myCmd.CommandText = "UPDATE email SET name='" & value1 & "' WHERE etc"

Also, you'll probably want to make sure that all single quotes in the value1 string are doubled up, so it doesn't cause syntax errors:

 

James O'Neill would need to be added in the SQL statement as James O''Neill, so

value1 = value1.Replace("'", "''")

[mrdutchie]

Still doesn't 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.

[Bucky]

Your declaration for mycmd must be incorrect, then. It should be

something like this:

 

Dim mycmd As OleDbCommand = New OleDbCommand()

 

In fact, to save a few lines of code, you could set the command's

connection and command text right in its constructor:

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 didn't initialize it yet, or the initialization

failed.

[mrdutchie]

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()

[mrdutchie]

please forget my last comment.

It's working now...

forgot that "perry@hotmail.com" was not in the database. duh....

 

Thanks so much for helping me out.