update errors

[kabbi~thkek]

Hello,

I have something set up like this:

In form1 some data is inserted into the MySQL database, then the user is
redirected to form 2 in where he can update the data just inserted and insert
more data for this particular device. (so all the fields of the table will be
filled). I made page 2 an update form.

I believed this was possible but I keep getting these errors:

The code (original):

<cfset CurrentPage=GetFileFromPath(GetTemplatePath())><cf param
name="URL.dev_id" default="1">
<cfif IsDefined("FORM.MM_UpdateRecord") AND FORM.MM_UpdateRecord EQ "form1">

<cfquery datasource="testdb">
UPDATE device SET volgnr=
<cfif IsDefined("FORM.volgnr") AND #FORM.volgnr# NEQ "">
#FORM.volgnr#
<cfelse>
NULL
</cfif>
, owner=
<cfif IsDefined("FORM.owner") AND #FORM.owner# NEQ "">
'#FORM.owner#'
<cfelse>
NULL
</cfif>
, serienr=
<cfif IsDefined("FORM.serienr") AND #FORM.serienr# NEQ "">
'#FORM.serienr#'
<cfelse>
NULL
</cfif>
, datum_in=
<cfif IsDefined("FORM.datum_in") AND #FORM.datum_in# NEQ "">
'#FORM.datum_in#'
<cfelse>
NULL
</cfif>
, datum_tr=
<cfif IsDefined("FORM.datum_tr") AND #FORM.datum_tr# NEQ "">
'#FORM.datum_tr#'
<cfelse>
NULL
</cfif>
, gek_dev=
<cfif IsDefined("FORM.gek_dev") AND #FORM.gek_dev# NEQ "">
'#FORM.gek_dev#'
<cfelse>
NULL
</cfif>
,"user"=
<cfif IsDefined("FORM.user") AND #FORM.user# NEQ "">
'#FORM.user#'
<cfelse>
NULL
</cfif>
, locatie=
<cfif IsDefined("FORM.locatie") AND #FORM.locatie# NEQ "">
'#FORM.locatie#'
<cfelse>
NULL
</cfif>
, mac=
<cfif IsDefined("FORM.mac") AND #FORM.mac# NEQ "">
'#FORM.mac#'
<cfelse>
NULL
</cfif>
, IP=
<cfif IsDefined("FORM.IP") AND #FORM.IP# NEQ "">
'#FORM.IP#'
<cfelse>
NULL
</cfif>
, processor=
<cfif IsDefined("FORM.processor") AND #FORM.processor# NEQ "">
'#FORM.processor#'
<cfelse>
NULL
</cfif>
, type=
<cfif IsDefined("FORM.type") AND #FORM.type# NEQ "">
'#FORM.type#'
<cfelse>
NULL
</cfif>
, nummer=
<cfif IsDefined("FORM.nummer") AND #FORM.nummer# NEQ "">
#FORM.nummer#
<cfelse>
NULL
</cfif>
WHERE dev_id=#FORM.dev_id#
</cfquery>
</cfif>
<cfquery name="type" datasource="testdb">
SELECT *
FROM type
</cfquery>
<cfquery name="device" datasource="testdb">
SELECT *
FROM device
WHERE dev_id = #URL.dev_id#</cfquery>
<cfquery name="eig" datasource="testdb">
SELECT * FROM owner
</cfquery>

<cfset typenr = #device.type#>
<cfquery name="nr" datasource="testdb">
SELECT MAX(nummer) AS hoogste_nummer FROM device WHERE type = #typenr#
</cfquery>

<cfset dbNum = (nr.hoogste_nummer + 1)>
<cfset nieuw_nummer = Repeatstring('0', (3-Len(dbNum))) & dbNum>

........

Using this code I get the following error:

Error Executing Database Query.
Syntax error or access violation: You have an error in your SQL syntax; check
the manual that corresponds to your MySQL server version for the right syntax
to use near '"user"= 'kabbi' , locatie= 'magazijn' , ' at line 25

Now I changed in the code above:

</cfif>
,"user"=
<cfif IsDefined("FORM.user") AND #FORM.user# NEQ "">
'#FORM.user#'
<cfelse>
NULL
</cfif>

in:

</cfif>
,user=
<cfif IsDefined("FORM.user") AND #FORM.user# NEQ "">
'#FORM.user#'
<cfelse>
NULL
</cfif>

It then puts the data in the database (except the type instead of the
typenumber is now inserted in the type field). But it still gives an error:

Error Executing Database Query.
Syntax error or access violation: You have an error in your SQL syntax; check
the manual that corresponds to your MySQL server version for the right syntax
to use near '' at line 1

Does anybody know what could be wrong? I cannot figure it out.

Kabbi

[SQLMenace]

Change this
,"user"=
to
,user=

[kabbi~thkek]

Hi,

I've done that, and the data is now actually inserted into the database table
but it still gives an error in my browser:

Error Executing Database Query.
Syntax error or access violation: You have an error in your SQL syntax; check
the manual that corresponds to your MySQL server version for the right syntax
to use near '' at line 1

Doe anyone know what could be wrong?

[mxstu]

kabbi~thkek,

I don't know if you saw jdeline's message above. Posting the actual sql
statement sent to the database (with the values)., makes it easier for others
to help you diagnose the problem. Turn on full debugging in the CF
administrator, and the sql statement will be included in the error message.

Btw, "TYPE" seems to be a reserved word in mySQL so you may need to escape
that column or change the name.
[url]http://dev.mysql.com/doc/mysql/en/reserved-words.html[/url]

[kabbi~thkek]

Thank you!
I'll have a look at it.

gr,

Kabbi

[paross1]

Use brackets around user

</cfif>
,[user]=
<cfif IsDefined("FORM.user") AND #FORM.user# NEQ "">
'#FORM.user#'
<cfelse>

Phil

[kabbi~thkek]

Hi,

I tried it, unfortunately it did not work. Now I get the following error:

Error Executing Database Query.
Syntax error or access violation: You have an error in your SQL syntax; check
the manual that corresponds to your MySQL server version for the right syntax
to use near '[user]= 'jelmer' , locatie= 'magazijn' ,' at line 25

Gr,

Kabbi

[mxstu]

1) As I said above, both "user" AND "Type" are reserved words. You need to escape or rename both of them.
2) Can you please post the entire sql statement from your error message?

[kabbi~thkek]

Now I have turned on full debugging:

Syntax error or access violation: You have an error in your SQL syntax; check
the manual that corresponds to your MySQL server version for the right syntax
to use near '[user]= 'yukyu' , locatie= 'magazijn' , ' at line 25

The error occurred in C:\xampp\htdocs\coldf\new0003.cfm: line 83

81 : NULL
82 : </cfif>
83 : WHERE dev_id=#FORM.dev_id#
84 : </cfquery>
85 : </cfif>




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

SQL UPDATE device SET volgnr= 0 , owner= 'SOW' , serienr= '12321' ,
datum_in= '2005-10-11' , datum_tr= '2005-10-12' , gek_dev= 'yuk' , [user]=
'yukyu' , locatie= 'magazijn' , mac= 'yuky' , IP= 'kyukkuy' , processor= 'kku'
, type= '20' , nummer= 016 WHERE dev_id=155
DATASOURCE testdb
VENDORERRORCODE 1064
SQLSTATE 42000

[mxstu]

Thank you :-)

In mySQL you use back ticks "`" to escape column names. Make sure to use them
around both the "user" and "type" column names:

`user` = 'yukyu' ,
`type` = '20' ,

.... rest of sql statement ....


Note - a back tick is not the same as a single quote

[kabbi~thkek]

Hi,

I tried this, and now it actually inserts the data into the table but I keep
getting an error:

The error occurred in C:\xampp\htdocs\coldf\new0003.cfm: line 100

98 : <cfset typenr = #device.type#>
99 : <cfquery name="nr" datasource="testdb">
100 : SELECT MAX(nummer) AS hoogste_nummer FROM device WHERE type = #typenr#
101 : </cfquery>
102 :




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

SQL SELECT MAX(nummer) AS hoogste_nummer FROM device WHERE type =
DATASOURCE testdb
VENDORERRORCODE 1064
SQLSTATE 42000

Resources:

I can't figure it out..... should I rename the table columns maybe? Do you
think this will help, I guess it's not the problem...

Gr,

Kabbi

[mxstu]

SELECT MAX(nummer) AS hoogste_nummer FROM device WHERE type = #typenr#
.....
SQL SELECT MAX(nummer) AS hoogste_nummer FROM device WHERE type =
DATASOURCE testdb
VENDORERRORCODE 1064
SQLSTATE 42000

It looks like there are two (possibly three) problems here.
1) It looks like #typenr# is an empty string. That will cause an error in
your query because the database expects a value to follow the equal sign:
WHERE type = (must be a value here)

2) It does not look like you escaped the "type" column with back ticks. Again
this will cause an error because "type" is a reserved word.

3) In your previous post you used single quotes around the "type" value. If
"type" is a varchar column, you must use single quotes around the variable
values or the database will probably throw an error. If "type" is a numeric
column, like int, mediumint, etc, you do not need single quotes around the
value. For example

WHERE someNumericColumn = #someNumberValue# ....or....
WHERE someVarcharColumn = '#someTextValue#'


Note - MM recommends using cfqueryparam when passing parameters to a database
query.

Considering the trouble you are having with these columns, you should probably
just rename the columns and save yourself some headaches.

[kabbi~thkek]

Hi,

3.....

First I made a variable named typenr:

<cfset typenr = #device.type#>

Then I used the value in this variable like this:

<cfquery name="nr" datasource="testdb">
SELECT MAX(nummer) AS hoogste_nummer FROM device WHERE type = #typenr#
</cfquery>

<cfset dbNum = (nr.hoogste_nummer + 1)>
<cfset nieuw_nummer = Repeatstring('0', (3-Len(dbNum))) & dbNum>


I tried to to use the quotes in:
<cfset typenr = '#device.type'#>

and in:

SELECT MAX(nummer) AS hoogste_nummer FROM device WHERE type = '#typenr#'

But it still gives errors.

1....
Type is not an empty string because in the end the data is inserted into the
database and the nummer column is automatically raised with one value for that
specific type.

2.....
When I use "type" instead of type it gives an error:

Error Executing Database Query.
Syntax error or access violation: You have an error in your SQL syntax; check
the manual that corresponds to your MySQL server version for the right syntax
to use near '"type"= '20' , nummer= 020 WHERE dev_id=' at line 45

The error occurred in C:\xampp\htdocs\coldf\new0003.cfm: line 83

81 : NULL
82 : </cfif>
83 : WHERE dev_id=#FORM.dev_id#
84 : </cfquery>
85 : </cfif>




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

SQL UPDATE device SET volgnr= 0 , owner= 'E' , serienr= 'r23e4r2' ,
datum_in= '2005-10-11' , datum_tr= '2005-10-12' , gek_dev= 'ertge' , `user`=
'gregreg' , locatie= 'magazijn' , mac= 'regre' , IP= 'gregre' , processor=
'regre' , "type"= '20' , nummer= 020 WHERE dev_id=162
DATASOURCE testdb
VENDORERRORCODE 1064
SQLSTATE 42000

Resources:

(

or 'type' :

Error Occurred While Processing Request
Error Executing Database Query.
Syntax error or access violation: You have an error in your SQL syntax; check
the manual that corresponds to your MySQL server version for the right syntax
to use near '' at line 1

The error occurred in C:\xampp\htdocs\coldf\new0003.cfm: line 100

98 : <cfset typenr = #device.type#>
99 : <cfquery name="nr" datasource="testdb">
100 : SELECT MAX(nummer) AS hoogste_nummer FROM device WHERE type = #typenr#
101 : </cfquery>
102 :




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

SQL SELECT MAX(nummer) AS hoogste_nummer FROM device WHERE type =
DATASOURCE testdb
VENDORERRORCODE 1064
SQLSTATE 42000

What do you think? Should I redesign my database and the codes?

Gr,

Kabbi

(PS: When I use 'user' or user (and just type instead of "type" or 'type') it
inserts all the data into the database, but still gives an error in my browser,
but it seems everything works because the data in inserted into the database
table..... isn't this strange? )

[mxstu]

Take a step back ..... and read my post again :-)

RE: When I use "type" instead of type it gives an error:
1)
It does not say use double quotes. It says to use back ticks to escape the
column names. You are not using back ticks in the necessary places, which is
one of the reasons you are getting errors. Since you are having so much trouble
with this, I would strongly suggest that you rename the "user" and "type"
columns. Then you will not have to worry about that particular problem.

RE: Type is not an empty string because in the ...
<cfset typenr = #device.type#> ... etc...

2) Did you output the value? Have you verified that it is not empty? Looking
at the sql statement in the error message. I see no value there. Output the
#typenr# variable and see whether or not it has a value.

[mxstu]

Take a step back ..... and read my post again :-)

RE: When I use "type" instead of type it gives an error:
1)
It does not say use double quotes. It says to use back ticks to escape the
column names. You are not using back ticks in the necessary places, which is
one of the reasons you are getting errors. Since you are having so much trouble
with this, I would strongly suggest that you rename the "user" and "type"
columns. Then you will not have to worry about that particular problem.

RE: Type is not an empty string because in the ...
<cfset typenr = #device.type#> ... etc...

2) Did you output the value? Have you verified that it is not empty? Looking
at the sql statement in the error message. I see no value there. Output the
#typenr# variable and see whether or not it has a value.

[kabbi~thkek]

Hi,

I've tested it and it gets a value.... However I think I'm going to redesign my tables...... Hopefully it'll work then.

Thank you very much for all your help!

Gr,

Kabbi