Error executing insert to data source, please help

Forum|Forum|17 years ago
June 5, 2008
3 replies
1690 views

Hello;
I am making a small security app for a log in feature. I have everything working, but when I try to post to the DB and leave a form field blank, it throws an error. I am placing my query code and then the 2 errors I am getting:

Query:
<cfquery datasource="#APPLICATION.dataSource#">
INSERT INTO Bliplist
(reject, HTTP_USER_AGENT)
VALUES
(<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.rejip#">,
<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#form.browser#">)
</cfquery>
<cflocation url="blist.cfm">

Error:
Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Field 'Bliplist.HTTP_USER_AGENT' cannot be a zero-length string.

The error occurred in C:\Websites\4npp8b\admin\trac\bl-action.cfm: line 28

26 : (<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.rejip#">,
27 : 
28 : <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#form.browser#">)
29 : 
30 : </cfquery>

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

SQLSTATE HY000
SQL INSERT INTO Bliplist (reject, HTTP_USER_AGENT) VALUES ( (param 1) , (param 2) )
VENDORERRORCODE -3702

So I added the code to check if the field was true, and I get this error: (here is the cfif and error:

<cfif HTTP_USER_AGENT is true>
<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#form.browser#">)
</cfif>

Error message:

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:\Websites\4npp8b\admin\trac\bl-action.cfm: line 28

26 : (<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.rejip#">,
27 : <cfif HTTP_USER_AGENT is true>
28 : <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#form.browser#">)
29 : </cfif>
30 : </cfquery>

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

SQLSTATE 42000
SQL INSERT INTO Bliplist (reject, HTTP_USER_AGENT) VALUES ( (param 1) ,
VENDORERRORCODE -3502

What am I doing wrong, and how do I fix it? I also have an update query on this and that throws the same error is there is a blank form field, Kind of lost here. How do I allow 0 length?

Thank you.

CFmonger

Advanced techniques

This topic has been closed for replies.

Correct answer Newsgroup_User

jdeline wrote:
> Under some circumstances, HTTP_USER_AGENT will not exist. What you want to do is <CFIF IsDefined("HTTP_USER_AGENT")>.

This may not work. There is a little known gotcha with CGI variables.
IIRC Do to their flighty nature, ColdFusion will always return true to
a IsDefined() test for any value.

D

Dan_Bracuk

Inspiring

It appears that you are trying to do this.

insert into sometable
(f1, f2)
values
(v1, <cfif something> v2 </cfif>)

Do you see anything obviously wrong with this?

C

CFmongerAuthor

Inspiring

Then how would I insert something into my tables, but allow it to have 0 lenth? So if a form field doesn't have anyhting in it, it won't error out.

Not to be rude, and I appreciate the help, BUT, why talk in riddles if your going to respond to a question? If there is something wrong, then state what it is. Again, not trying to be rude, but if your going to help, then help... not riddle about it.

I changed the way the cfif was written and it didn't like that either. I wasn't looking if the db table was defined, I was looking if the form field was defined to then add it to the DB, if it isn't in the form field, then not to try.

I wrote this in the insert into:

<CFIF IsDefined("#form.browser#")>.
<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.browser#">)
</cfif>

and then in the update record this:

<CFIF IsDefined("#form.browser#")>.
Bliplist.HTTP_USER_AGENT=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.browser#">
</CFIF>

Now it throws this error:

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

The error occurred in C:\Websites\4npp8b\admin\trac\bl-action.cfm: line 39

37 : <CFIF IsDefined("#form.browser#")>.
38 : Bliplist.HTTP_USER_AGENT=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.browser#"></CFIF>
39 : WHERE Bliplist.ID = <cfqueryparam value="#form.ID#" cfsqlType="CF_SQL_INTEGER">
40 : </cfquery>
41 : <cflocation url="blist.cfm">

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

SQLSTATE 42000
SQL UPDATE Bliplist SET Bliplist.reject= (param 1) , WHERE Bliplist.ID = (param 2)
VENDORERRORCODE -3503

So what is the proper way to allow 0 length??

N

Newsgroup_UserCorrect answer

Inspiring

jdeline wrote:
> Under some circumstances, HTTP_USER_AGENT will not exist. What you want to do is <CFIF IsDefined("HTTP_USER_AGENT")>.

This may not work. There is a little known gotcha with CGI variables.
IIRC Do to their flighty nature, ColdFusion will always return true to
a IsDefined() test for any value.

A

Anonymous

Under some circumstances, HTTP_USER_AGENT will not exist. What you want to do is <CFIF IsDefined("HTTP_USER_AGENT")>.

C

CFmongerAuthor

Inspiring

I added the cfif isDefined on both the update record query and the add a new record.

I get this error when adding a new record:
Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '. Pa_RaM001'.

The error occurred in C:\Websites\4npp8b\admin\trac\bl-action.cfm: line 28

26 : (<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.rejip#">,
27 : <CFIF IsDefined("HTTP_USER_AGENT")>.
28 : <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#form.browser#">)
29 : </cfif>
30 : </cfquery>

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

SQLSTATE 42000
SQL INSERT INTO Bliplist (reject, HTTP_USER_AGENT) VALUES ( (param 1) , . (param 2) )
VENDORERRORCODE -3100

And this error when updating an existing record:

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

The error occurred in C:\Websites\4npp8b\admin\trac\bl-action.cfm: line 39

37 : <CFIF IsDefined("HTTP_USER_AGENT")>.
38 : Bliplist.HTTP_USER_AGENT=<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#form.browser#"></CFIF>
39 : WHERE ID = <cfqueryparam value="#form.ID#" cfsqlType="CF_SQL_INTEGER">
40 : </cfquery>
41 : <cflocation url="blist.cfm">

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

SQLSTATE 42000
SQL UPDATE Bliplist SET Bliplist.reject= (param 1) , . Bliplist.HTTP_USER_AGENT= (param 2) WHERE ID = (param 3)
VENDORERRORCODE -3503

Here is the code I added:

<cfif form.id eq 0>
<cfquery datasource="#APPLICATION.dataSource#">
INSERT INTO Bliplist
(reject, HTTP_USER_AGENT)
VALUES
(<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.rejip#">,
<CFIF IsDefined("HTTP_USER_AGENT")>.
<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#form.browser#">)
</cfif>
</cfquery>
<cflocation url="blist.cfm">
<cfelse>
<cfquery datasource="#APPLICATION.dataSource#">
UPDATE Bliplist
SET
Bliplist.reject=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.rejip#">,
<CFIF IsDefined("HTTP_USER_AGENT")>.
Bliplist.HTTP_USER_AGENT=<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#form.browser#"></CFIF>
WHERE ID = <cfqueryparam value="#form.ID#" cfsqlType="CF_SQL_INTEGER">
</cfquery>
<cflocation url="blist.cfm">
</cfif>

I think the update cfif is being used wrong. Is the insert just missing pound signs? ##

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded