problem with insert statement

[sviolet]

HI there

it seems like i get the error when inserting records into a MS Access database
if the fields are null. :

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft
Access Driver] Syntax error in INSERT INTO statement.

The error occurred in C:\Inetpub\wwwroot\instreamWorks\Instream_Enter3.c fm:
line 30
Called from C:\Inetpub\wwwroot\instreamWorks\Instream_Enter3.c fm: line 1
Called from C:\Inetpub\wwwroot\instreamWorks\Instream_Enter3.c fm: line 30
Called from C:\Inetpub\wwwroot\instreamWorks\Instream_Enter3.c fm: line 1

28 : <cfquery name="AddTrip" datasource="InstreamWorks">
29 : INSERT INTO TBL_PROJECT_GEOREFERENCE (TBL_PROJECT_INFO_ID, EASTINGS,
NORTHINGS)
30 : VALUES (#getid.maxid#, #Form.EASTINGS#, #Form.NORTHINGS#)
31 : </cfquery>
32 :




--------------------------------------------------------------------------------

SQL INSERT INTO TBL_PROJECT_GEOREFERENCE (TBL_PROJECT_INFO_ID, EASTINGS,
NORTHINGS) VALUES (93, , )
DATASOURCE InstreamWorks

****************


if the user fills out all the fields, then the records are inserted without a
problem. this doesn't happen on other insert queries of mine.

my code:

<cfquery name="AddTrip" datasource="InstreamWorks">
INSERT INTO TBL_PROJECT_GEOREFERENCE (TBL_PROJECT_INFO_ID, EASTINGS,
NORTHINGS)
VALUES (#getid.maxid#, #Form.EASTINGS#, #Form.NORTHINGS#)
</cfquery>

any ideas?

thanks

[A3gis]

Hi,
are the fields eastings and northings integers? I dont mean to point out the
obvious but just in case, dont forget the single quotes for them if they're
textual fields.

have you tried using <cfqueryparam> tags for the values as well?

Cheers,
Aegis

[sviolet]

Thanks a3gis,

Single quotes was not the problem but your suggestion to use cfqueryparam
worked:


<CFQUERY DATASOURCE="InstreamWorks" NAME="InsertGEO">
INSERT INTO TBL_GEOREF (
EASTINGS,
NORTHINGS
)
VALUES (
<cfqueryparam
value="#Form.EASTINGS#"
cfsqltype="CF_SQL_DOUBLE"

>,

<cfqueryparam
value="#Form.NORTHINGS#"
cfsqltype="CF_SQL_DOUBLE"

>

)

</cfquery>

[A3gis]

Cool :)

note, anywhere you're receiving form or url variables into a query, it is very
handy to use cfqueryparams, as they prevent users from being able to send
spurious SQL commands through those fields to trigger DROP TABLE commands etc.
Regards,
Aegis