SQL ERROR

[alexfusion]

Hi everyone:
I hope this question will be clear:
I have a search form with an action page that is a new fom.This last form will insert the content of the form fields into a Table.That is fine.
The possibles values entered by the visitor in the search form can be alphanumeric or numeric.
The problem is that in the query I compare the value entered for the visitor with the Id (autonumeric) field of the table,so if the visitor enter an alphanumeric value ,I get an error.Here I put some code to be more specific:
<cfquery name=&quot;check_values&quot; datasource=&quot;datasourcename&quot; dbtype=&quot;ODBC&quot;>
SELECT * FROM Table 1
WHERE customer_name= '#FORM.value#' OR phone='#FORM.value#' OR Id=#FORM.value#
</cfquery>
As you see if the visitor enter a value in the form that is not numeric,let's say 1A the criteria Id=#FORM.value# get this error:

ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'customer_name='1A' OR phone='1A' OR Id=1A'.
How can I solve this error.I need to use the Id field as autonumeric value.
I hope I was clear.

Any help is welcome!!

alexfusion

mixale@hotmail.com

 

[DeZiner]

It looks as if you are missing syntax. Adding the single quotes around the id='#form.value#' should sove your error.

Hope it helps!

 

[GunJack]

Hey Alex,

If the field &quot;id&quot; is an autonumber field, there is no way you can compare it to alpha numeric data. The only option I see is to use a conditional sql statement like this:

SELECT * FROM Table 1
WHERE customer_name= '#FORM.value#' OR phone='#FORM.value#'
<cfif isnumeric(form.value)>
OR Id=#FORM.value#
</cfif>

I'm not sure I understand why you're letting users enter alpha-numeric criteria against a numeric field but if you can't simply exclude the critieria with a <cfif> statement, I'll need to understand your app better before I can suggest a better approach.

Hope this helps,
GJ

 

[alexfusion]

Hi Guys
Thank you for the solution you gave me about the question.
Hey GunJack,I'm wondering the same question that you were wondering.Why am I letting the visitor enter alphanumeric data against a numeric field.
I'will describe better the application:
I am developing for a customer an intranet application.
The customer wants to have a search form that will retrieve results from a database.He wants the aplication determine if his clients exists into the database searching the name of the client or the telephone.That is pretty fine.
The other part is when a new client wants to put a new order for a his company.He wants the application generate automatically the order number and insert it into the table.
I thougth about the Id as the way to generate automatically the order number,but this fiels is autonumeric.Hence my question.
If this is clear for you maybe you can suggest me a better way to generate an automatic order number.

Once again I must say thank you for your help.

alexfusion

mixale@hotmail.com

 

[GunJack]

Hey Alex,

The autonumber field is the best way to generate a unqiue id IMO. On the search page, you could use Javascript to make sure that only numbers are entered into the search field and then use the isNumeric() function on the action page to screen for valid entries in case they bypass the JS check.

Something like this would work on your action page:

<cfif not isnumeric(form.id)>
<cflocation searchError.cfm?name=#urlencodedformat(name)#&phone=#urlencodedformat(phone)&ID=#urlencodedformat(id)#&quot;>
</cffif>

..... Rest of search coding here in the case of no error ......

On searchError.cfm, just copy your initial search page and put an error message above the &quot;ID&quot; field saying that this field must be a number. Then assign the value attribute on each text input to be the value you passed in with the url variables. This gives a nice clean error message and keeps your code from generating an error.

Let me know if you have any more questions,
GJ