Syntax Error in SQL Update Statement

[Maximus]

Can some one help me with finding the error is this statement?

strSQL_Update = "UPDATE tblWJS_SuperInp ut SET JobNumber= '"&
strJobNumber &"', " _
& "Weekending Date = '"& strWeekEndingDa te &"', "_
& "CurrentHou rs = '"& strCurrentHours &"', "_
& "CurrentHoursTa ping = '"& strCurrentHours Taping &"', "_
& "ReportRequired _01 = '"& strReportRequir ed_01 &"', " _
& "ReportRequired _02 = '"& strReportRequir ed_02 &"', " _
& "ReportRequired _03 = '"& strReportRequir ed_03 &"', " _
& "ReportRequired _04 = '"& strReportRequir ed_04 &"', " _
& "ReportRequired _05 = '"& strReportRequir ed_05 &"', " _
& "ReportRequired _06 = '"& strReportRequir ed_06 &"', " _
& "ReportRequired _07 = '"& strReportRequir ed_07 &"', " _

& "WHERE JobNumber ="&strJobNumber &" AND WeekEndingDate ="&
strWeekEndingDa te &""

strConnect = "Driver={Micros oft Access Driver (*.mdb)}; DBQ=\
\CALSJ1\xxxx.md b"
Set conn = Server.CreateOb ject("ADODB.Con nection")
conn.Open strConnect
Set objRecordSet = conn.Execute(st rSQL_Update)

[Bob Barrows [MVP]]

Re: Syntax Error in SQL Update Statement

Maximus wrote:

Can some one help me with finding the error is this statement?
>
strSQL_Update = "UPDATE tblWJS_SuperInp ut SET JobNumber= '"&
strJobNumber &"', " _
& "Weekending Date = '"& strWeekEndingDa te &"', "_
& "CurrentHou rs = '"& strCurrentHours &"', "_
& "CurrentHoursTa ping = '"& strCurrentHours Taping &"', "_
& "ReportRequired _01 = '"& strReportRequir ed_01 &"', " _
& "ReportRequired _02 = '"& strReportRequir ed_02 &"', " _
& "ReportRequired _03 = '"& strReportRequir ed_03 &"', " _
& "ReportRequired _04 = '"& strReportRequir ed_04 &"', " _
& "ReportRequired _05 = '"& strReportRequir ed_05 &"', " _
& "ReportRequired _06 = '"& strReportRequir ed_06 &"', " _
& "ReportRequired _07 = '"& strReportRequir ed_07 &"', " _
>

This is less than optimal database design. For one thing, what if you
need an eighth report? You will have to revise the table as well as all
queries that reference it: a maintenance nightmare. A better design
would involve a separate table with three columns: JobNumber,
ReportRequiredO rdinal, and ReportRequired

& "WHERE JobNumber ="&strJobNumber &" AND WeekEndingDate ="&
strWeekEndingDa te &""
>
strConnect = "Driver={Micros oft Access Driver (*.mdb)}; DBQ=\
\CALSJ1\xxxx.md b"

Nothing to do with your problem but:

http://www.aspfaq.com/show.asp?id=2126

Set conn = Server.CreateOb ject("ADODB.Con nection")
conn.Open strConnect
Set objRecordSet = conn.Execute(st rSQL_Update)

Is WeekendingDate a date/time field? If so, date literals need to be
delimited with octathorps (#), not quotes ('). Quotes are used for
strings, not dates in JetSQL.

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, 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. 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.

[Bob Barrows [MVP]]

Re: Syntax Error in SQL Update Statement

Maximus wrote:

Can some one help me with finding the error is this statement?
>

Please do not multipost Maximus. This is definitely a database-related
question
so .asp.db was the perfect group in which to post it. Posting it here as
well did not increase your chances of getting an answer (most of us
subscribe to both groups). On the contrary, if somebody had taken his
time
to answer it here, only to find that it was already resolved in the
other
group, that person may have been annoyed enough to ignore any future
posts
from you, thereby decreasing your chances of getting help in the future.

There are times when you will not be sure which group is most
appropriate,
and you will want to post a question to both groups. In that situation,
you
should use the cross-posting technique, rather than posting the same
message
multiple times. To crosspost, put a semicolon-delimited* list of the
newsgroups to which you wish to post in the To: header of your post and
post
it once. It, and any replies to it, will appear in all the newsgroups in
your list. So, if I reply in .asp.db, my reply will also appear here in
..asp.general.

* ... or whatever delimiter is recognized by your news client

--
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.

[Bob Barrows [MVP]]

Re: Syntax Error in SQL Update Statement

Bob Barrows [MVP] wrote:

Maximus wrote:

>Can some one help me with finding the error is this statement?
>>

Please do not multipost Maximus.

Oops - disregard this. my mistake.
--
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.