Error executing insert to data source, please help

[CFmonger]

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 : <!--- <cfif HTTP_USER_AGENT is true> --->
28 : <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#form.browser#">)
29 : <!--- </cfif> --->
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

[Guest]

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

[CFmonger]

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? ##

[Newsgroup_User · Accepted solution]

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.

[Dan_Bracuk]

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?

[CFmonger]

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??

[Newsgroup_User]

CFmonger wrote:
>
> So what is the proper way to allow 0 length??
>


Insert a zero length string ''. You are not allowing for a zero length,
you are truncating your SQL code in such a manner that it is illegal
under the conditions for which you are testing.

The fix that would require the least change to your code would to add
and else clause to your if statement so to allow for the the desired
value to be placed into the SQL code.

A better fix would to use features of CFML and/or your database that
allow for this. I.E. the NULL parameter of a <cfqueryparam...> tag or
default values in your database design.

Or you can modify your logic so that both the field name and field value
are truncated from your SQL code so that is is still legal under this case.

[CFmonger]

I thought I had this fixed, but it is back to errors again. Let me get this right.. if I set my db table, for allow zero length is should fix my problem? then I wouldn't need a cfif to tell if the form field wasn't filled, it wold still be ok to post to the db? Correct?

[Newsgroup_User]

CFmonger wrote:
> I thought I had this fixed, but it is back to errors again. Let me get this
> right.. if I set my db table, for allow zero length is should fix my problem?
> then I wouldn't need a cfif to tell if the form field wasn't filled, it wold
> still be ok to post to the db? Correct?
>

Not completely. If you set your database to use a default, apparently
an empty string in this case, you would not need to provide this in your
code.

You would still need to structure you CFML so that the SQL it builds is
legal for all cases.

I.E.
INSERT INTO aTable
(aField, bField)
VALUES
(aValue, <cfif ...>bValue></cfif>)

This will produce the following two SQL statements depending on the
evaluation of the if clause.

IF TRUE:
INSERT INTO aTable
(aField, bField)
VALUES
(aValue, bValue)

This is a normal and complete SQL statement and will properly execute.

IF FALSE:
INSERT INTO aTable
(aField, bField)
VALUES
(aValue, )

This is not proper. It says there will be two field requiring two
values, but there is only one value and a trailing comma with nothing
following. Databases are never going to accept this improper SQL. This
is what you need to work on. I gave you two ways to handle this in my
previous message.




The teacher in me now leaves you to give it your best shot to put this
all together.