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

 

[Crowley16]

lol...

yeah, I've had this one quite a few times now...

replace Date with [Date]...

also you might want to use #'s to wrap the date value as that will convert it to date literals...

i.e.
... #" & mdate & "#...

 

[BarryMVS]

Thanks Crowley,

That solved that problem.
Now I have a new one.

The database fields are the following types:
JobID = text
EngineerIDs = text
Date = text
AM = True/False
PM = True/False
ALLDAY = True/False
ALLNIGHT = True/False
jobtype = text
customerID = text

At present the SQL statement looks like this:

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

(I have left off the #'s around the mdate as I want it as text - is this correct?)

I am now getting the following error:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

The data being sent to the SQL statement is this:

POST Data:
check=2&jobid=Manual+Entry&jobtype=2&customerid=4811&EngList=Brian+Buist%2C+Dave+Woodhouse&mdate=23+Jun+2004&am=&pm=&allday=&allnight=True&submit=Is+this+correct%3F

Any assistance is most welcomed.

Thanks,

Barry

ICT Network Administrator
IT Services Manager

 

[Crowley16]

eh, let me get this straight...

the date you are sending is "23" + "Jun" + "2004"

also, it's not clear what you are actually doing with POST DATA:

can you give us the exact data used to insert? i.e.:
jobid =
EngList =
...

 

[BarryMVS]

Sorry,

OK POST Data broken down.
Check = 2 (Page trigger, ignore)
jobid = Manual+Entry (JobID - Text)
jobtype = 2 (jobtype - Text)
customerid = 4811 (customerID - Text)
EngList = Brian+Buist,+Dave+Woodhouse (EngineerIDs - Text)
mdate = 23+Jun+2004 (Date - Text)
am = (AM - True/False)
pm = (PM - True/False)
allday = (ALLDAY - True/False)
allnight = True (ALLNIGHT - True/False)
submit = Is+this+correct%3F (Value of submit button, ignore)

Hope this helps.

Barry

ICT Network Administrator
IT Services Manager

 

[Crowley16]

check? where does this come in? no references in sql or field list?

with the insert into statement, you have to make sure that every VALUE(field1, ...)
matches with every tblName(field1, ...)

also, remove the "'" from the true/false fields, they take values of 0 and -1 as integers

and if you can, get rid of the + signs, use "," as delimiters or " " not "+

 

[BarryMVS]

The 'Check' valuse is a trigger for the asp page. It is not a field in the database and it's value isn't sent to the database.

The + sign is added by the POST method to replace a " " as you can not have spaces when posting data. When the data is sent by the ASP page to the SQL statement, the + signs are removed and replaced with spaces again.

I will remove the ' ' from around the True/False values.

Hope this helps.

Barry

ICT Network Administrator
IT Services Manager

 

[BarryMVS]

I removed the ' ' from around the true/false and removed the value as being 'True'. This means that the form checkbox now returns the value as 'on'.

I am now getting a Syntax Error in the statement.

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

Just to make it more confussing.

Thanks for your help.

Barry

ICT Network Administrator
IT Services Manager

 

[Crowley16]

what's the error say?

 

[Crowley16]

Oh, ur missing the ; off the end...

 

[BarryMVS]

SQL statement looks like this:

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

But I'm getting the following error:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

I'm lost. I've tried checking this statement against others that I have written, but I can not find an error.

Any more ideas?

Cheers,

Barry

ICT Network Administrator
IT Services Manager

 

[Crowley16]

hmm, try putting [] around am and pm, maybe they are keywords as well...

also check your spelling

otherwise syntax is fine...

 

[BarryMVS]

Tried [ ] around AM & PM.
Made no difference.

Checked my spelling. Thats all ok.

I'm at a loss...

Could it be becuase some of the true/false fields are getting no value returned?

I'm just guessing?

Thanks for your help.

Barry

ICT Network Administrator
IT Services Manager

 

[Crowley16]

ahh...

yes, if you get a null value, you'll hit problems...

use NZ(fieldName, "False") or whatever value u want when u get no results...

eg:

...& ", NZ(" & allday & ", 'FALSE'), " &...

 

[BarryMVS]

Have I made a mistake...?

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

Still getting the Syntax Error Code as before..!

Barry

ICT Network Administrator
IT Services Manager

 

[Crowley16]

you are trying to set a true/false value as 'off' which is wrong

use FALSE or TRUE or 0 or -1 without the '

 

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

Barry

ICT Network Administrator
IT Services Manager

 

[Crowley16]

no no no...

without the ' on the false...

just FALSE

if u do 'FALSE' it tries to set the value as a string, which would give a datatype match error...

 

[BarryMVS]

Opps, sorry.

OK, tried that.

SQL as follows:

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 'Syntax error in INSERT INTO statement.'

Thanks for your help.

Barry

ICT Network Administrator
IT Services Manager

 

[Crowley16]

since you are now passing your true/false values into NZ function, and if there's nothing there, u'd get NZ(,FALSE) which wouldn't make sense...

try putting the '' back on the true/false statements...
NZ('" & am & "', False)