can someone check my UPDATE syntax below?

sql="UPDATE tblAbrasions SET (TestRequestNo = '" & varTestRequestNo & "', TestID = '" & vTID & "', Barb = '" & varBarb & "', WhichTest = '" & varWhichTest & "', Description = '" & varColorway & "', Sample = '" & varSample & "', Sample2 = '" & varSample2 & "', Sample3 = '" & varSample3 & "', Sample4 = '" & varSample4 & "', Sample5 = '" & varSample5 & "', Sample6 = '" & varSample6 & "', AbrasionHeadNo = '" & varAbrasionHead & "', TestMethod = '"& varTestMethod &"', Notes = '"& varNotes &"') WHERE TestID = ' " & Request.Form("txtTestID") & " ' "

------------------------------------
this is the error msg i get when i ran the query: Microsoft JET Database Engine (0x80040E14)
Syntax error in UPDATE statement.
/project/abrasions.asp, line 45
-------------------------------------

i have the same variable names on my INSERT statement and there is no error.

thanks

Right after you build your SQL string, put this:
response.write sql

then we will be able to see exactly what the database sees when you execute it. You've probably got a variable with no value somewhere in there.

it didn't even get to the sql statement. at the top of the page, i did this:

vTID = request.form ("txtTestID")
response.write("<p>TEST id is " & vTID & " !</p>")

it didn't print anything. The txtTestID didn't get pass from the previous page. so i guess that's problem.

thanks

Sounds like you've got it.

i got the txtTestID variable to pass, but still the same error msg. here's the complete code.


<%

' Declaring variables
Dim vCID, vTID, varTestRequestNo, varBarb, varWhichTest, varColorway, varSample, varSample2, varSample3, varSample4, varSample5, varSample6, varAbrasionHead, varTestMethod, varNotes, ID
'vCID=Request.form("txtCustID")
'response.write("<p>customer id is " & vCID & " !</p>")
vTID = request.form ("txtTestID")
response.write("<p>TEST id is " & vTID & " !</p>")

varTestRequestNo = Request.form ("txtTestRequestNo")
varBarb = Request.form ("txtBarb")
varWhichTest = Request.Form("WhichTest")
varColorway = Request.Form("txtColorway")
varSample = Request.Form("txtSample")
varSample2 = Request.Form("txtSample2")
varSample3 = Request.Form("txtSample3")
varSample4 = Request.Form("txtSample4")
varSample5 = Request.Form("txtSample5")
varSample6 = Request.Form("txtSample6")
varAbrasionhead = Request.Form ("txtAbrasionHead")
varTestMethod = Request.Form ("TestName")
varNotes = Request.Form ("txtNotes")
'AppliedDecor = Request ("chkAppliedDeco")

set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "C:/Inetpub/wwwroot/database/HTC.mdb"

if varSample = 0 and varTestRequestNo = "" then
sql="INSERT INTO tblAbrasions (TestRequestNo, TestID, Barb, WhichTest, Description, Sample, Sample2, Sample3, Sample4, Sample5, Sample6, AbrasionHeadNo, TestMethod, Notes) values ('" & varTestRequestNo & "', '" & vTID & "', '" & varBarb & "', '" & varWhichTest & "', '" & varColorway & "', '" & varSample & "', '" & varSample2 & "', '" & varSample3 & "','" & varSample4 & "','" & varSample5 & "','" & varSample6 & "','" & varAbrasionHead & "','"& varTestMethod &"','"& varNotes &"')"
response.write sql
else
sql="UPDATE tblAbrasions SET (TestRequestNo = '" & varTestRequestNo & "', TestID = '" & vTID & "', Barb = '" & varBarb & "', WhichTest = '" & varWhichTest & "', Description = '" & varColorway & "', Sample = '" & varSample & "', Sample2 = '" & varSample2 & "', Sample3 = '" & varSample3 & "', Sample4 = '" & varSample4 & "', Sample5 = '" & varSample5 & "', Sample6 = '" & varSample6 & "', AbrasionHeadNo = '" & varAbrasionHead & "', TestMethod = '"& varTestMethod &"', Notes = '"& varNotes &"') WHERE TestID = ' " & Request.Form("txtTestID") & " ' "
response.write sql

end if


%>
<p>Updating.</p>
<a href="javascript:window.close()">close window</a>
<%
'on error resume next
conn.Execute sql

conn.close
set conn = nothing

%>

---------------------
when i comment out the "UPDATE" statement, the INSERT statement works fine.

can someone help?

Are all your fields in the database Text, if not you have an issue with that, I see that your first field refers to a number, you must leave out the single quotes for number.

new UPDATE statement:

sql="UPDATE tblAbrasions SET (TestRequestNo = " & varTestRequestNo & ", TestID = " & vTID & ", Barb = '" & varBarb & "', WhichTest = '" & varWhichTest & "', Description = '" & varColorway & "', Sample = " & varSample & ", Sample2 = " & varSample2 & ", Sample3 = " & varSample3 & ", Sample4 = " & varSample4 & ", Sample5 = " & varSample5 & ", Sample6 = " & varSample6 & ", AbrasionHeadNo = " & varAbrasionHead & ", TestMethod = '"& varTestMethod &"', Notes = '"& varNotes &"') WHERE TestID = '" & Request.Form("txtTestID") & "'"

it is still given me the same error msg.

help!

Right after that line of code, before you execute the statement. Put :
reponse.write sql
response.end

this way we can see the query and see exactly why it's failing.

You are WONDERFUL! i just add "response.end" to each of my "response.write sql" and the error is gone. It is updating correctly.

BIG thanks

:~D

the response.write and response.end are only debugging techniques, if your pages don't work when you remove them, then there is still something wrong.

goodness. it didn't update anything in my database. I was totally happy that it didn't gives me anymore errors, but in fact, it didn't do anything. here's the UPDATE response.write sql statement:


UPDATE tblAbrasions SET (TestRequestNo = TS3120, TestID = 100, Barb = '1 green barb', WhichTest = 'Abrasions, Abrasion Resistance - Sock Testing Consortium Martindale Abrasion Method, Standard Practice for Conditioning and Testing Textiles - ASTM Method D1776, Abrasion Resistance - Sock Testing Consortium Martindale Abrasion Method, select Test Method', Description = 'Not seamed, not washed 3 times, abrasion in the heel, not conditioned', Sample = 7, Sample2 = 9, Sample3 = 12, Sample4 = 12, Sample5 = 14, Sample6 = 12, AbrasionHeadNo = 7, TestMethod = '', Notes = 'socks do not fit the "circile"') WHERE TestID = '100'

but when i check the database, the data in the database is the old data and not the new data i just enter.

so what is wrong? :~(


thanks

I see a couple things wrong.

TestRequestNo = TS3120 Should be in quotes because TS3120 is a char, not an integer.

Also, your updating TestID = 100, looks like an integer. In the WHERE clause your saying TestID = '100', looks like a char.... which is it?

That's all I'm seeing right now, fix those and see what happens.

still not updating.

here's new the response.write sql after changing TestRequestNo to a string & TestID to a number

UPDATE tblAbrasions SET (TestRequestNo = 'TS3120', TestID = 100, Barb = '1 green barb', WhichTest = 'Abrasions, Abrasion Resistance - Sock Testing Consortium Martindale Abrasion Method, Standard Practice for Conditioning and Testing Textiles - ASTM Method D1776, Abrasion Resistance - Sock Testing Consortium Martindale Abrasion Method, Standard Practice for Conditioning and Testing Textiles - ASTM Method D1776', Description = 'Not seamed, not washed 3 times, abrasion in the heel, not conditioned', Sample = 8, Sample2 = 8, Sample3 = 8, Sample4 = 12, Sample5 = 14, Sample6 = 12, AbrasionHeadNo = 7, TestMethod = '', Notes = 'socks do not fit the "circile"') WHERE TestID = 100

thanks

Did you remove the reponse.end ? You'll need to do that.

when i removed the response.end; i got this error message:

Microsoft JET Database Engine (0x80040E14)
Syntax error in UPDATE statement.
/project/abrasions.asp, line 46

-----------------------------------------

i got this error message when i take out the opening and closing parenthesis:

Microsoft JET Database Engine (0x80040E57)
The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.

any idea?

Ok, there's your problem. One of the values your trying to put into the database is too big. Compare the size of each column in the database with the size of the text your inserting. You'll probably need to make the column size bigger in the db.

How much (if compare to a paragraph form) i can store in the database when choosing a field in MS ACCESS in the data field as "MEMO" ?

Memo fields are stored on the disk and not in the database, so you can store as much as you need to in there. The database stores a small (16 byte) pointer to the acuall data.

i am not quite sure i understand you when you refer "Memo fields are stored on the disk, not in the database". how do i store a "textarea" from a form to the database? what about storing multiple selection from a dropdown box to a database?

many thanks