Syntax Error in INSERT INTO statement

[BarryMVS]

Hi all,

I have an asp page which contains the following SQL Insert statement.

strSql2 = "INSERT into AllocatedJobs (JobID, EngineerIDs, Date, AM, PM, ALLDAY, ALLNIGHT, jobtype, customerID) VALUES ('" & jobid & "', '" & EngList & "', '" & mdate & "', '" & am & "', '" & pm & "', '" & allday & "', '" & allnight & "', '" & jobtypeID & "', '" & customerid & "')"

When I run the page I get the following error message:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

I can't see what the problem is. It's bound to be something stupid, but I just can't spot it.

If you can see a mistake, please let me know.

Thanks,

Barry

ICT Network Administrator
IT Services Manager

 

[BarryMVS]

strSql2 = "INSERT into AllocatedJobs (JobID, EngineerIDs, [Date], AM, PM, ALLDAY, ALLNIGHT, jobtype, customerID) VALUES ('" & jobid & "', '" & EngList & "', '" & mdate & "', " & ", NZ('" & am & "', False), " & ", " & ", NZ('" & pm & "', False), " & ", " & ", NZ('" & allday & "', False), " & ", '" & ", NZ('" & allnight & "', False), " & "', '" & jobtypeID & "', '" & customerid & "');"

Still getting the Syntax error.


Barry

ICT Network Administrator
IT Services Manager

 

[Crowley16]

do a msgbox strSql2 and see what you get...

 

[BarryMVS]

Sorry, you've lost me. What is a msgbox?

Barry

ICT Network Administrator
IT Services Manager

 

[Crowley16]

msgbox is a message box...

basically, if you put

msgbox sqlStr2

in your code, immediately before the part where you run your sql statement, msgbox will show exactly what the sql statement is at that time...

 

[PHV]

Have you tried to replace this:
& ", NZ('" & am & "', False), " &
by this ?
& ", " & NZ(am, False) & ", " &
and so on for all the NZ calls

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BarryMVS]

I'm trying to do a remote admin on this site and it's informed me that access is denied when I tried to run the 'msgbox'.

I tried PHV's idea, but that returned the error:
Type mismatch: 'NZ'

strSql2 = "INSERT into AllocatedJobs (JobID, EngineerIDs, [Date], AM, PM, ALLDAY, ALLNIGHT, jobtype, customerID) VALUES ('" & jobid & "', '" & EngList & "', '" & mdate & "', " & NZ(am, False) & ", " & NZ( pm , False) & ", " & NZ( allday , False) & ", " & NZ(allnight, False) & ", '" & jobtypeID & "', '" & customerid & "');"

Did I do something wrong??

Cheers for your help,

Barry

ICT Network Administrator
IT Services Manager

 

[PHV]

And what about this ?
& IIf(IsNull(pm), 0, pm) &

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BarryMVS]

PHV,

Should that be IIf or If?

Copied your example (IIf) and got a type mismatch: 'IIf'
Tried it as If and got a syntax error.

strSql2 = "INSERT into AllocatedJobs (JobID, EngineerIDs, [Date], AM, PM, ALLDAY, ALLNIGHT, jobtype, customerID) VALUES ('" & jobid & "', '" & EngList & "', '" & mdate & "', " & If(IsNull(am), 0, am) & ", " & If(IsNull(pm), 0, pm) & ", " & If(IsNull(allday), 0, allday) & ", " & If(IsNull(allnight), 0, allnight) & ", '" & jobtypeID & "', '" & customerid & "');"

Thanks for your help.

Barry

ICT Network Administrator
IT Services Manager

 

[Crowley16]

should be IIF

maybe you should try to simplify the query first...

just do insert for 1 field, and if that works, include another field...

keep going till you've got all your fields or untill you get an error, then u'll know exactly where ur going wrong...

 

[BarryMVS]

OK guys,

I couldn't get either the Nz or the IIF to work so I came up with another work around which is as follows:

am = request.form("am")
pm = request.form("pm")
allday = request.form("allday")
allnight = request.form("allnight")
If am = "" then
am = "False"
end if
If pm = "" then
pm = "False"
end if
If allday = "" then
allday = "False"
end if
If allnight = "" then
allnight = "False"
end if
							
strSql2 = "INSERT into AllocatedJobs (JobID, EngineerIDs, [Date], AM, PM, ALLDAY, ALLNIGHT, jobtype, customerID) VALUES ('" & jobid & "', '" & EngList & "', '" & mdate & "', " & am & ", " & pm & ", " & allday & ", " & allnight & ", '" & jobtypeID & "', '" & customerid & "');"

Thanks for all your ideas and help.

Barry

ICT Network Administrator
IT Services Manager

 

[PHV]

Glad you solved your issue.
So I guess the correct answer was:
& IIf(Len(Nz(pm,""))=0, "False", pm) &

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244