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("t odate")
notes = request.form("n otes")
job = Session("JOB_AD J")
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.Exe cute(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.Exe cute(sql)



What is wrong with the above code ?

Thanks

David

[Bob Barrows [MVP]]

Re: UPDATE record

David wrote:

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("t odate")
notes = request.form("n otes")
job = Session("JOB_AD J")
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.Exe cute(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.Exe cute(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.w rite 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&selm=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Lasse Edsvik]

Re: UPDATE record

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


"David" <davidgordon@sc ene-double.co.ukwro te in message
news:1188836794 .525535.127220@ 19g2000hsx.goog legroups.com...

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("t odate")
notes = request.form("n otes")
job = Session("JOB_AD J")
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.Exe cute(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.Exe cute(sql)
>
>
>
What is wrong with the above code ?
>
Thanks
>
David
>

[Daniel Crichton]

Re: UPDATE record

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