Update function (MS Access >>> SQL Server 2005)

Hi,
I started developing a project using access and am new to SQL server.
One of the scripts I have updates a table entry using access no problem yet
throws an error with SQL server on line "RS_Utility.execute(strSQL)".
Is there a difference between a standard update statement between Access and
SQL sever?
If there is, can someone point me in the right direction please.....
My function is below... sorry for the word wrapping though... donno how to
prevent that when showing others code through a newsgroup.
Thanks R
----------------------------------------------
function updateRec()
dDay =
DateSerial(request.queryString("yr"),request.queryString("mt h"),request.queryString("dy"))
set RS_Utility = server.CreateObject("ADODB.Connection")
RS_Utility.open MM_conn_STRING
strSQL = "UPDATE events, eventDays SET
eventDays.evt_time='"&request("todActive")&"',
eventDays.evt_memo='"&request("memoText")&"' WHERE
eventDays.evtdayID="&request("idVal")&""
RS_Utility.execute(strSQL)
response.redirect
"eventEdit.asp?id="&id&"&mth="&Month(dDay)&"&dy="&Day(dDay)& "&yr="&Year(dDay)&"&task=edit"
RS_Utility.close()
set RS_Utility = nothing
end function
-----------------------------------------------

Re: Update function (MS Access >>> SQL Server 2005)

Russell wrote:
> Hi,
> I started developing a project using access and am new to SQL server.
> One of the scripts I have updates a table entry using access no
> problem yet throws an error with SQL server on line
> "RS_Utility.execute(strSQL)".
> Is there a difference between a standard update statement between
> Access and SQL sever?

Yes, there may be. There is nothing standard about the dialect of SQL
(JetSQL) used by Jet.

> If there is, can someone point me in the right direction please.....
> My function is below... sorry for the word wrapping though... donno
> how to prevent that when showing others code through a newsgroup.
> Thanks R
> ----------------------------------------------
> function updateRec()
> dDay =
>
DateSerial(request.queryString("yr"),request.queryString("mt h"),request.
queryString("dy"))
> set RS_Utility = server.CreateObject("ADODB.Connection")
> RS_Utility.open MM_conn_STRING
> strSQL = "UPDATE events, eventDays SET

You can have only one table in the UPDATE FROM clause in Transact-SQL
(the version of SQL used by MS SQL Server).

> eventDays.evt_time='"&request("todActive")&"',
> eventDays.evt_memo='"&request("memoText")&"' WHERE
> eventDays.evtdayID="&request("idVal")&""

I'm not sure why you have both events and eventDays in the FROM clause -
you don't seem to be doing anything with the events table. From what I
can see, all you need to do is change it to

strSQL = "UPDATE eventDays SET

and it will work.

> RS_Utility.execute(strSQL)

This line should be:
RS_Utility.execute strSQL,,1 'adCmdText

Always tell ADO the CommandType - don't make it guess.


> response.redirect
>
"eventEdit.asp?id="&id&"&mth="&Month(dDay)&"&dy="&Day(dDay)& "&yr="&Year(
dDay)&"&task=edit"

The redirect should go AFTER the following two lines, not before.

> RS_Utility.close()
> set RS_Utility = nothing
> end function
> -----------------------------------------------


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.inetser ver.asp.db/msg/72e36562fee7804e

Personally, I prefer using stored procedures:
http://tinyurl.com/jyy0


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.