UPDATE works in access but not in ASP

[DannyTmoov2]

I have tried the following sql statement in access and it works fine, however when I run it as part of my asp it seems to just ignore it? I get no error and the code continues it just doesn't update the database?

here is the ASP: -

contactno = session("contactno")
strSQL = "UPDATE client SET contact_no='" & contactno &"' WHERE client.client_id = '" & clientid & "';"
myConn.execute strSQL
response.write(strSQL)

and here is the output: -
UPDATE client SET contact_no='0123456789' WHERE client.client_id = 'client@mailaddress.com';

If I copy and paste the output into access it works fine, but just doesnt work in the asp??????

any help greatly appreciated

(I also have an INSERT statement that DOES work so I am confident all permissions are OK)

 

[DreXor]

if it's a number value you may need to strip the single quotes, but you should be getting an ODBC data type error, unless you have an on error move next, try remming this statement out in your code if you have 1+ of them

also as covered a couple times in a few recent threads, you may need to put []'s around your field names, because sometimes "_" is interpreted as an escape character, and it's better safe than sorry.

 

[QatQat]

There is nothing wrong with _ and contactno is likely to be a char() field so you need quotes


strSQL = "UPDATE client SET contact_no='" & contactno &"' WHERE client.client_id =" & clientid


this should work


Bye

Qatqat

Life is what happens when you are making other plans.

 

[vikramonline]

I had the same problem

Just managed to solve it

strSQL = "UPDATE client SET [contact_no]='" & contactno &"' WHERE client.client_id =" & clientid

Enclose all column names into []

 

[Tarwn]

In this case I would also enclose the table name in brackets. Generally when a statement will run in Acces and not run from ASP it's a case of having a reserved word as a table or colum name. The acess query analyzer can figure it out but external queries need a little nudge in the right direction.

strSQL = "UPDATE [client] SET [contact_no]='" & contactno &"' WHERE [client].[client_id] = '" & clientid & "';"

I use underscores quite often in my fieldnames and have never had a problem with them in any of the databases I use them on (Access, SQL Server, MySQL, Paradox, etc). As someone mentioned earlier be sure to check the field type for your two fields. They look like strings/text/memo from the query, if they aren't get rid of the single quotes around the value.

-T

_{01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111}
The never-completed website:

http://www.tiernok.com/