Solved

Coldfusion insert Error why? INSERT INTO vol (user_id, id_number, firstname, lastname, date

Posted on 2012-03-23
14
676 Views
Last Modified: 2012-03-26
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.datasource#" username="#application.username#" password="#application.password#">
                  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\CONFIRMATION_E.CFM.</p><P><P>
0
Comment
Question by:LelloLello
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 39

Expert Comment

by:gdemaria
ID: 37758976
Not sure if this is the reason, but no reason to choose LONG varchar...

CF_SQL_LONGVARCHAR

should be

CF_SQL_VARCHAR
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37759025
>  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)

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" />
0
 
LVL 15

Assisted Solution

by:Gurpreet Singh Randhawa
Gurpreet Singh Randhawa earned 50 total points
ID: 37760382
hi agx,

i have seen many posts using this

<cfqueryparam value="#NOW()#" cfsqltype="cf_sql_date" />

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
0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 150 total points
ID: 37760664
you can also simply replace the date with the database's now() function

MySQL - now()
SQL - getDate()
Oracle - sysdate
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37760866
> 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.
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 300 total points
ID: 37760895
you can also simply replace the date with the database's now() function

Only if you want to insert a date and time.  (From their code they seem to be inserting a date only.) If you want to insert a date (only) you'll need slightly different sql or a different function. For example, mySQL has the CURDATE() function.
0
 

Author Comment

by:LelloLello
ID: 37766711
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.datasource#" username="#application.username#" password="#application.password#">
                  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\CONFIRMATION_E.CFM.</p><P><P>
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 52

Expert Comment

by:_agx_
ID: 37766825
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')
0
 

Author Comment

by:LelloLello
ID: 37767240
where do u want me to that in sql or in my code
0
 

Author Comment

by:LelloLello
ID: 37767253
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..
0
 

Author Comment

by:LelloLello
ID: 37767257
(1 row(s) affected)
in SQL 2005.
0
 

Author Comment

by:LelloLello
ID: 37767274
so I have added to my coldfusion code:

 <cfquery name="logAnnualDues" datasource="#application.datasource#" username="#application.username#" password="#application.password#">
                     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\MEMBERS\VOLUNTEER\CONFIRMATION_E.CFM.</p><P><P>
0
 

Author Comment

by:LelloLello
ID: 37767331
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.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37767629
> #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 --->
        )
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
popup load of page and setting up of session 8 26
SQL Server - Slabs 9 38
SQL server is using more virtual memory. 5 68
Add '#' to end of file 2 30
I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now