Syntax error in UPDATE statement.

doctorallia said:
got it.. if you want me to explain what i changed let me know.

it was all in the modify data page.. so you can see it if you compare the old and new ones

thanx alot! i've seen the codes you have `` on my variables. actually i'm quite confused when to use "" , `` and ''
 
okay here goes:


use `` around your Column names and Table names.

use '' around your data being added.

and use "" around your variables (only if you are using variables)

also use "" to begin and end the entire sql string

like so:

if not using a variable:
sql = "UPDATE `tablename` SET `fieldname`= 'data' WHERE `fieldname2` = 'data2'"

if using a variable (usually you are when you using asp):
sql = "UPDATE `tablename` SET `fieldname`= '" & variable1 &"' WHERE `fieldname2` = '" & variable2 &"'"
 
doctorallia said:
okay here goes:


use `` around your Column names and Table names.

use '' around your data being added.

and use "" around your variables (only if you are using variables)

also use "" to begin and end the entire sql string

like so:

if not using a variable:
sql = "UPDATE `tablename` SET `fieldname`= 'data' WHERE `fieldname2` = 'data2'"

if using a variable (usually you are when you using asp):
sql = "UPDATE `tablename` SET `fieldname`= '" & variable1 &"' WHERE `fieldname2` = '" & variable2 &"'"

ThanX alot for your help! : o )
 
Hi doctorilla / HighFlyer,

I've looked through urs whole posting on this as i've search through the internet for my error that i'm having now..

And i've just modified my code according to your suggestion into this..

SQL = "UPDATE `userInfo` SET `vlevel` = '" &varlevel& "' WHERE `fullname` = '" &varName& "'"


objConn.Open StrCon
objRec.Open SQL, objConn

But it appears that i'm having another error message : " Operation must use an updateable query." And i've tried many solutions provided in Mircosoft but to no avail..

Thanks in advance for any advice to me=)
 
You need to use
Code:
objConn.Execute SQL
rather than your objRec line. An UPDATE statement does not return any records like a SELECT statement does.
 
Hi Subspace, thanks for replying...

Seems like the error message has now move to point at the execute statement now...

-----------------------------
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query.
/Prj4/FYPLevel1.asp, line 385
------------------------------

Ok, i've change my few sentences into these, have a look if there's any problem:


<%
if varlevel < 3 then

varlevel = varlevel + 1

'Increment database by 1
dim objConn,objRec,SQL

Set objConn = Server.CreateObject("ADODB.Connection")
Set objRec = server.CreateObject("ADODB.Recordset")

objConn.Mode = 0

SQL = "UPDATE `userInfo` SET `vlevel` = '" &varlevel& "' WHERE `fullname` = '" &varName& "'"


objConn.Open StrCon
' objRec.Open SQL, objConn //taken out already

objConn.Execute(SQL) <--Line 385

objRec.Close
objConn.Close
set objRec = nothing
set objConn = nothing
%>
alert("increase!")
<%
end if
%>
 
Ok, i've change my few sentences into these, have a look if there's any problem:


SQL = "UPDATE `userInfo` SET `vlevel` = '" & varlevel & "' WHERE `fullname` = '" & varName & "'"

%>

You need spaces between your variables and the & characters.

riley
 
Thanks riley, I've tried it before..

To SubSpace,
I've read that particular microsoft article regarding this error message, and before i post it i had already went to my IIS and edit my profile..

To reconfirm, the steps is it:

- IIS prog
- Left sidebar (Right click (Go to Properties )-> Default Web Site)
- Find on (Home Directory) tab
- Under that sub page, make sure i've checked the checkbox , Read.. Write..

Am i right?

I was thinking if i really couldn't update database anymore, i might change my program to write into a textfile instead to store, which might be another way out. :)
 
Hi Riley and SubSpace,

Finally i know the reason behind all my problems, again it is not the syntax or codes! Argzhh, actually is that my project folder in the first place did not enable sharing permission untill i went to enable it just now.

from the beginning i thought (from previous post) that just by enable the read/write permission those in IIS setting is enough, but i was wrong, should have at least making sure everything is properly set.

So Sorry to have waste your time previously by suggesting here and there, but I'm thankful to learn from you guys anyway=)
 
Back
Top