UPDATE record

[David]

Hi,

I have an asp page for which I am trying to update a record, but keep
getting errors in my SQL:::::

todate = request.form("todate")
notes = request.form("notes")
job = Session("JOB_ADJ")
sid = Session("SID")


dt=date()
yy = Year(dt)
mm = Month(dt)
dd = Day(dt)
v_date = yy & "/" & mm & "/" & dd


ShipD=todate
yy = Year(ShipD)
mm = Month(ShipD)
dd = Day(ShipD)
S_date = yy & "/" & mm & "/" & dd

uSQL = "SELECT * FROM PCBForecast WHERE PCBForecastID = " &
Session("SID") & ""
Set RS = adoDataConn.Execute(uSQL)


sql = "UPDATE PCBForecast"
sql = sql & " SET ShipQty = " & RS("ShipQty") & ","
sql = sql & " ShipETA = " & S_date & ","
sql = sql & " Notes = '" & RS("Notes") & "',"
sql = sql & " Entrydate = " & v_date & ","

sql = sql & " WHERE PCBForecastID = "&sid&""


set RS2 = adoDataConn.Execute(sql)



What is wrong with the above code ?

Thanks

David

 

[Bob Barrows [MVP]]

Hi,

I have an asp page for which I am trying to update a record, but keep
getting errors in my SQL:::::

todate = request.form("todate")
notes = request.form("notes")
job = Session("JOB_ADJ")
sid = Session("SID")


dt=date()
yy = Year(dt)
mm = Month(dt)
dd = Day(dt)
v_date = yy & "/" & mm & "/" & dd


ShipD=todate
yy = Year(ShipD)
mm = Month(ShipD)
dd = Day(ShipD)
S_date = yy & "/" & mm & "/" & dd

uSQL = "SELECT * FROM PCBForecast WHERE PCBForecastID = " &
Session("SID") & ""
Set RS = adoDataConn.Execute(uSQL)


sql = "UPDATE PCBForecast"
sql = sql & " SET ShipQty = " & RS("ShipQty") & ","
sql = sql & " ShipETA = " & S_date & ","
sql = sql & " Notes = '" & RS("Notes") & "',"
sql = sql & " Entrydate = " & v_date & ","

sql = sql & " WHERE PCBForecastID = "&sid&""


set RS2 = adoDataConn.Execute(sql)



What is wrong with the above code ?

I can't tell. Here is a list of the things you did not tell us:
1. database type and version
2. Datatypes of the fields involved in that update statement
3. The result of that string concatenation - we cannot debug a sql statement
without knowing what it is. You need to find out what it is by using
"response.write sql", running the page, and looking at the statement in the
browser window. This is usually enough to determine the problem. If not, you
should copy the statement from the browser window and use the query
execution tool of whatever database you are using to attempt to run it - you
will usually get a more informative error message. if your database's query
execution tool provides a query builder, then use the query builder to
create a statement that does what you want this statement to do, and compare
the result with the statement you built in your vbscript code. If none of
this helps, provide the information I requested in a followup post.

Further points to consider:
Your use of dynamic sql is leaving you vulnerable to hackers using sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

Personally, I prefer using stored procedures,
SQL Server:

http://groups.google.com/group/microsoft.public.inetserver.asp.general/msg/5d3c9d4409dc1701?hl=en&

or saved parameter queries as they are known in Access:

Access:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&[email protected]

http://groups.google.com/groups?hl=...=1&[email protected]

 

[Lasse Edsvik]

David,

Response.Write is your friend,


put Response.Write(sql) above "Set Rs2 = ....." to see what the sql-query
you're trying to execute looks like. I assume you see 2 things are wrong
with that query (if I get the datatypes right from what the columns are
called)

you have a comma before WHERE, remove that, EntryDate (which I assume is a
datetime datatype) should have ' around its value, i.e Entrydate = '" &
v_date & "'"


/Lasse

 

[Daniel Crichton]

Lasse wrote on Tue, 4 Sep 2007 10:57:58 +0200:

David,

Response.Write is your friend,

put Response.Write(sql) above "Set Rs2 = ....." to see what the
sql-query you're trying to execute looks like. I assume you see 2
things are wrong with that query (if I get the datatypes right from
what the columns are called)

you have a comma before WHERE, remove that, EntryDate (which I assume
is a datetime datatype) should have ' around its value, i.e Entrydate =
'" &
v_date & "'"

ShipETA also requires quoting as it's a date too.

Dan