LelloLello
asked on
Coldfusion insert Error why? INSERT INTO vol (user_id, id_number, firstname, lastname, date
I have create the following SQL TABLE
user_id int
id_number int
firstname varchar50
lastename varchar40
date datetime
This is my insert
<cfif xaction eq "true">
<cfquery name="logs" datasource="#application.d atasource# " username="#application.use rname#" password="#application.pas sword#">
INSERT INTO vol (user_id, id_number, firstname, lastname, date)
VALUES (
<cfqueryparam value = "#form.user_id#" CFSQLType = "CF_SQL_INTEGER" />,
<cfqueryparam value = "#form.id_number#" cfsqltype="CF_SQL_INTEGER" />,
<cfqueryparam value = "#form.firstname#" CFSQLType = "CF_SQL_LONGVARCHAR" /> ,
<cfqueryparam value = "#form.lastname#" CFSQLType = "CF_SQL_LONGVARCHAR" /> ,
<cfqueryparam value="#DateFormat(NOW(), "dd/MM/YYYY")#" cfsqltype="cf_sql_date" />
)
</cfquery>
</cfif>
This is the errors i'm getting
-------------------------- ---------- ------
ODBC Error Code = S1C00 (Driver not capable)<P><P> [Microsoft][ODBC SQL Server Driver]Optional feature not implemented<P><P>SQL = "INSERT INTO vol (user_id, id_number, firstname, lastname, date)
VALUES (
?,
?,
? ,
? ,
?
)"<P>
Query Parameter Value(s) - <P>Parameter #1 = 16687
<P>Parameter #2 = 972432
<P>Parameter #3 = Lello
<P>Parameter #4 = Ceer
<P>Parameter #5 = {d '2012-03-23'}
<P>Data Source = "ACT"<P><p>The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (28:5) to (28:140) in the template file D:\USERS\ACA\HTML\MEMBERS\ VOL\CONFIR MATION_E.C FM.</p><P> <P>
user_id int
id_number int
firstname varchar50
lastename varchar40
date datetime
This is my insert
<cfif xaction eq "true">
<cfquery name="logs" datasource="#application.d
INSERT INTO vol (user_id, id_number, firstname, lastname, date)
VALUES (
<cfqueryparam value = "#form.user_id#" CFSQLType = "CF_SQL_INTEGER" />,
<cfqueryparam value = "#form.id_number#" cfsqltype="CF_SQL_INTEGER"
<cfqueryparam value = "#form.firstname#" CFSQLType = "CF_SQL_LONGVARCHAR" /> ,
<cfqueryparam value = "#form.lastname#" CFSQLType = "CF_SQL_LONGVARCHAR" /> ,
<cfqueryparam value="#DateFormat(NOW(), "dd/MM/YYYY")#" cfsqltype="cf_sql_date" />
)
</cfquery>
</cfif>
This is the errors i'm getting
--------------------------
ODBC Error Code = S1C00 (Driver not capable)<P><P> [Microsoft][ODBC SQL Server Driver]Optional feature not implemented<P><P>SQL = "INSERT INTO vol (user_id, id_number, firstname, lastname, date)
VALUES (
?,
?,
? ,
? ,
?
)"<P>
Query Parameter Value(s) - <P>Parameter #1 = 16687
<P>Parameter #2 = 972432
<P>Parameter #3 = Lello
<P>Parameter #4 = Ceer
<P>Parameter #5 = {d '2012-03-23'}
<P>Data Source = "ACT"<P><p>The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (28:5) to (28:140) in the template file D:\USERS\ACA\HTML\MEMBERS\
> INSERT INTO vol (user_id, id_number, firstname, lastname, date)
Could be that "date" is a reserved word in many db's. Try escaping it OR changing the column name altogether (better)
<cfqueryparam value="#DateFormat(NOW(), "dd/MM/YYYY")#" cfsqltype="cf_sql_date" />
Side note, since you're using the date type, you don't need DateFormat() to truncate the time. That particular mask is probably going to insert the wrong date (sometimes) anyway. Better to use:
Could be that "date" is a reserved word in many db's. Try escaping it OR changing the column name altogether (better)
INSERT INTO vol (user_id, id_number, firstname, lastname, [date] )
<cfqueryparam value="#DateFormat(NOW(), "dd/MM/YYYY")#" cfsqltype="cf_sql_date" />
Side note, since you're using the date type, you don't need DateFormat() to truncate the time. That particular mask is probably going to insert the wrong date (sometimes) anyway. Better to use:
<cfqueryparam value="#NOW()#" cfsqltype="cf_sql_date" />
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
> why can't we use this
> <cfqueryparam value="#createodbcdate(NOW ())#" cfsqltype="cf_sql_date" />
> that automatically handles the date according to the format database handles
So does now(). Since now() returns a date object, and the sql type already truncates the time, there's no need for the extra createodbcdate() call. Nothing technically wrong with the syntax, it's just redundant.
Edit: Usually people use createodbcdate(NOW()) to truncate the time. But you really only need it when using cfsqltype=cf_sql_timestamp . cf_sql_date only sends a date, so it has the same affect as createODBCDate.
> <cfqueryparam value="#createodbcdate(NOW
> that automatically handles the date according to the format database handles
So does now(). Since now() returns a date object, and the sql type already truncates the time, there's no need for the extra createodbcdate() call. Nothing technically wrong with the syntax, it's just redundant.
Edit: Usually people use createodbcdate(NOW()) to truncate the time. But you really only need it when using cfsqltype=cf_sql_timestamp
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you guys. I have remove the datefield and my data gointo the vol table. .. So the error was within my date: so i have change the columns name to datereg type datetime and i have adjust my code below.
So I would like to insert the DATE when the form has been submitted... either SQL, or ODBC date or should i create a field date... i'm not sure what is important for me that the datereg field type datetime has a date. and now it didn't work with that code any clue what i should fix in my code below...
<cfquery name="logs" datasource="#application.d atasource# " username="#application.use rname#" password="#application.pas sword#">
INSERT INTO vol (user_id, id_number, firstname, lastname, datereg)
VALUES (
<cfqueryparam value = "#form.user_id#" CFSQLType = "CF_SQL_INTEGER" />,
<cfqueryparam value = "#form.id_number#" cfsqltype="CF_SQL_INTEGER" />,
<cfqueryparam value = "#form.firstname#" CFSQLType = "CF_SQL_VARCHAR" /> ,
<cfqueryparam value = "#form.lastname#" CFSQLType = "CF_SQL_VARCHAR" /> ,
<!---<cfqueryparam value="#DateFormat(NOW(), "dd/MM/YYYY")#" cfsqltype="cf_sql_date" />--->
<cfqueryparam value="#createodbcdate(NOW ())#" cfsqltype="cf_sql_date" />
here is the error.
ODBC Error Code = S1C00 (Driver not capable)<P><P> [Microsoft][ODBC SQL Server Driver]Optional feature not implemented<P><P>SQL = "INSERT INTO volunteers (user_id, id_number, firstname, lastname, datereg)
VALUES (
?,
?,
? ,
? ,
?
)"<P>
Query Parameter Value(s) - <P>Parameter #1 = 16687
<P>Parameter #2 = 93232
<P>Parameter #3 = Joe
<P>Parameter #4 = Blow
<P>Parameter #5 = {d '2012-03-26'}
<P>Data Source = "ACTUARIES"<P><p>The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (28:5) to (28:140) in the template file D:\USERS\ACA\HTML\MEMBERS\ VOL\CONFIR MATION_E.C FM.</p><P> <P>
So I would like to insert the DATE when the form has been submitted... either SQL, or ODBC date or should i create a field date... i'm not sure what is important for me that the datereg field type datetime has a date. and now it didn't work with that code any clue what i should fix in my code below...
<cfquery name="logs" datasource="#application.d
INSERT INTO vol (user_id, id_number, firstname, lastname, datereg)
VALUES (
<cfqueryparam value = "#form.user_id#" CFSQLType = "CF_SQL_INTEGER" />,
<cfqueryparam value = "#form.id_number#" cfsqltype="CF_SQL_INTEGER"
<cfqueryparam value = "#form.firstname#" CFSQLType = "CF_SQL_VARCHAR" /> ,
<cfqueryparam value = "#form.lastname#" CFSQLType = "CF_SQL_VARCHAR" /> ,
<!---<cfqueryparam value="#DateFormat(NOW(), "dd/MM/YYYY")#" cfsqltype="cf_sql_date" />--->
<cfqueryparam value="#createodbcdate(NOW
here is the error.
ODBC Error Code = S1C00 (Driver not capable)<P><P> [Microsoft][ODBC SQL Server Driver]Optional feature not implemented<P><P>SQL = "INSERT INTO volunteers (user_id, id_number, firstname, lastname, datereg)
VALUES (
?,
?,
? ,
? ,
?
)"<P>
Query Parameter Value(s) - <P>Parameter #1 = 16687
<P>Parameter #2 = 93232
<P>Parameter #3 = Joe
<P>Parameter #4 = Blow
<P>Parameter #5 = {d '2012-03-26'}
<P>Data Source = "ACTUARIES"<P><p>The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (28:5) to (28:140) in the template file D:\USERS\ACA\HTML\MEMBERS\
So the error was within my date:
..
ODBC Error Code = S1C00 (Driver not capable)
Isn't that the same error as before? I suggested "date" might be causing the problem. But if you're still getting the same error it suggests a different cause.
Try hard coding the values. Any change?
INSERT INTO volunteers (user_id, id_number, firstname, lastname, datereg)
VAUES (16687, 93232, 'Joe', 'Blow', '2012-03-26')
..
ODBC Error Code = S1C00 (Driver not capable)
Isn't that the same error as before? I suggested "date" might be causing the problem. But if you're still getting the same error it suggests a different cause.
Try hard coding the values. Any change?
INSERT INTO volunteers (user_id, id_number, firstname, lastname, datereg)
VAUES (16687, 93232, 'Joe', 'Blow', '2012-03-26')
ASKER
where do u want me to that in sql or in my code
ASKER
Well the SQL Server is SQL Server 2000 at the hosting and i'm using Microsoft SQL Server Studio management 2005 to create the table volunteers..
ASKER
(1 row(s) affected)
in SQL 2005.
in SQL 2005.
ASKER
so I have added to my coldfusion code:
<cfquery name="logAnnualDues" datasource="#application.d atasource# " username="#application.use rname#" password="#application.pas sword#">
INSERT INTO volunteers (user_id, id_number, firstname, lastname, datereg)
VAUES (16687, 93232, 'lello', 'Blow', '2012-03-26')
</cfquery>
and here is the errors:
ODBC Error Code = 37000 (Syntax error or access violation)<P><P> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near 'VAUES'.<P><P>SQL = "INSERT INTO volunteers (user_id, id_number, firstname, lastname, datereg)
VAUES (16687, 93232, 'lello', 'Blow', '2012-03-26')"<P>Data Source = "ACTUARIES"<P><p>The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (28:5) to (28:140) in the template file D:\USERS\ACTCA\HTML\MEMBER S\VOLUNTEE R\CONFIRMA TION_E.CFM .</p><P><P >
<cfquery name="logAnnualDues" datasource="#application.d
INSERT INTO volunteers (user_id, id_number, firstname, lastname, datereg)
VAUES (16687, 93232, 'lello', 'Blow', '2012-03-26')
</cfquery>
and here is the errors:
ODBC Error Code = 37000 (Syntax error or access violation)<P><P> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near 'VAUES'.<P><P>SQL = "INSERT INTO volunteers (user_id, id_number, firstname, lastname, datereg)
VAUES (16687, 93232, 'lello', 'Blow', '2012-03-26')"<P>Data Source = "ACTUARIES"<P><p>The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (28:5) to (28:140) in the template file D:\USERS\ACTCA\HTML\MEMBER
ASKER
I fix the problem.
VALUES (
<cfqueryparam value = "#form.user_id#" CFSQLType = "CF_SQL_INTEGER" />,
<cfqueryparam value = "#form.id_number#" cfsqltype="CF_SQL_INTEGER" />,
<cfqueryparam value = "#form.firstname#" CFSQLType = "CF_SQL_VARCHAR" /> ,
<cfqueryparam value = "#form.lastname#" CFSQLType = "CF_SQL_VARCHAR" /> ,
'#DateFormat(NOW())#'
)
Thank all for your help.
VALUES (
<cfqueryparam value = "#form.user_id#" CFSQLType = "CF_SQL_INTEGER" />,
<cfqueryparam value = "#form.id_number#" cfsqltype="CF_SQL_INTEGER"
<cfqueryparam value = "#form.firstname#" CFSQLType = "CF_SQL_VARCHAR" /> ,
<cfqueryparam value = "#form.lastname#" CFSQLType = "CF_SQL_VARCHAR" /> ,
'#DateFormat(NOW())#'
)
Thank all for your help.
> #DateFormat(NOW())#
A tip, don't use strings for dates. I don't know why cfqueryparam isn't working for you. But I never use ODBC anymore. Might have something to do with it. (Though I'm curious why are you using ODBC instead of JDBC?)
If you can't use cfqueryparam, use what myselfrandhawa suggested, but without the cfqueryparam
ie VALUES ( .... other values
, #CreateODBCDate(NOW())# <!--- notice NO quotes --->
)
A tip, don't use strings for dates. I don't know why cfqueryparam isn't working for you. But I never use ODBC anymore. Might have something to do with it. (Though I'm curious why are you using ODBC instead of JDBC?)
If you can't use cfqueryparam, use what myselfrandhawa suggested, but without the cfqueryparam
ie VALUES ( .... other values
, #CreateODBCDate(NOW())# <!--- notice NO quotes --->
)
CF_SQL_LONGVARCHAR
should be
CF_SQL_VARCHAR