Skip to main content
Participating Frequently
October 15, 2006
Question

Error Executing Database Query.

  • October 15, 2006
  • 9 replies
  • 2057 views
[Macromedia][SQLServer JDBC Driver][SQLServer]Line 1: Incorrect syntax near ','.

what is that? i'm really really don't understand why this problem has been occured?
This topic has been closed for replies.

9 replies

clixmaxAuthor
Participating Frequently
October 17, 2006
hmm..so, is there any option to solve this problem?
Participating Frequently
October 18, 2006
There are options, but it gets complicated. You are probably going to need to research this more (I recommend using the Web Application Construction Kit by Ben Forta), but basically, when a cfgrid is submitted, you have special arrays available, such as cfgridname.RowStatus.Action[index] where index is U, I, or D ( Update, Insert, or Delete respectively). Plus, you have gridname.colname[index] and gridname.original.colname[index] as well. You can then use these special arrays to assist you with writing your own insert statement, which in your case would involve performing the insert minus the PK column, since MSSQL is taking care of that for you. I'm not going to go into detail with the code required for doing this, as it is well documented in WACK. (I happen to have the 4th edition, and the examples start on page 740 in chapter 25.)

Good luck with this.

Phil
clixmaxAuthor
Participating Frequently
October 17, 2006
yes..i was declared as INDENTITY because i want the SQL server provide me the id number (autonumber). i don't allow the user to key in their own number.
Participating Frequently
October 17, 2006
quote:

yes..it was declared as INDENTITY

Then look at the insert statement. It appears to me like it is attempting to insert a value into the id_kelas column, which I am assuming is the PK for the tbl_kelas_tmp table. Since your PK is an IDENTITY, I don't believe that you can use cfgridupdate for an insert in this case because it attempts to add values for all columns, including your PK, which is already being generated by SQL Server.

Phil

clixmaxAuthor
Participating Frequently
October 16, 2006
yeah!! it works...but after i restarted the server physically..never mind... but, i still want to know..actually i've another problem about CFGRID/CFGRIDUPDATE. i' have one table in MS SQL Server which have a primary key (INT). it runs ok if i update/edit record (row) but, when i try to add new record, it does'nt work. i don't know why.. i've tried my best in such a way. these all the code and error:

Page 1:

<cfquery name="qkelas" datasource="DatPelajar">
select *
from tbl_kelas_tmp
where id_pel = '#session.id#'
</cfquery>

<cfgrid name="kelas" query="qkelas" selectmode="edit" insert="yes" delete="yes" insertbutton="Tambah Rekod" deletebutton="Hapus Rekod" width="310" align="absmiddle">
<cfgridcolumn name="id_kelas" header="ID" width="20" display="no">
<cfgridcolumn name="id_pel" header="No. Pendaftaran" width="90" display="yes">
<cfgridcolumn name="kelas" header="Kelas" width="50" display="yes">
<cfgridcolumn name="sem" header="Semester "width="100" display="yes">
</cfgrid>

Page 2 (process the record):

<cfgridupdate grid="kelas" datasource="DatPelajar" tablename="tbl_kelas_tmp">

Error (apter add new record):

Error Executing Database Query.
Invalid data for CFSQLTYPE CF_SQL_INTEGER.

The error occurred in E:\PSIS\submit.cfm: line 33

31 : </head>
32 :
33 : <cfgridupdate grid="kelas" datasource="DatPelajar" tablename="tbl_kelas_tmp">
34 :
35 : <body topmargin="0" bottommargin="0" bgcolor="#6699CC">


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

SQL insert into tbl_kelas_tmp (id_kelas, id_pel, kelas, sem) values ( (param 1) , (param 2) , (param 3) , (param 4) )
DATASOURCE DatPelajar

Thanks a lot for ur help.
Participating Frequently
October 16, 2006
Is your PK declared as an IDENTITY? If so, SQL Server isn't going to like for you to try and provide your own PK value when it is already generating one itself for an inserted row.

Phil
clixmaxAuthor
Participating Frequently
October 16, 2006
huh!...still got error.. i don't know why..

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Line 1: Incorrect syntax near &apos;*&apos;.

The error occurred in E:\PSIS\dlt_modul.cfm: line 54

52 : delete *
53 : from tbl_dftr_mod
54 : where id_pel = '#form.idpel#' and sem = '#form.sem#' and id_dftr in (#Form.cb_gugur#)
55 : </cfquery>
56 :


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

SQL delete * from tbl_dftr_mod where id_pel = '16DAT03F001' and sem = 'SEMESTER 1' and id_dftr in (19,20)
DATASOURCE DatModul
VENDORERRORCODE 170
SQLSTATE HY000

October 16, 2006

DELETE * IS INVALID SQL!

Change this to "delete" (no asterix).

clixmaxAuthor
Participating Frequently
October 16, 2006
definitely yes..i've copy from that..but still error:

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Line 1: Incorrect syntax near &apos;*&apos;.

The error occurred in E:\PSIS\dlt_modul.cfm: line 54

52 : delete
53 : from tbl_dftr_mod
54 : where id_pel = '#form.idpel#' and sem = '#form.sem#' and id_dftr in (#Form.cb_gugur#)
55 : </cfquery>
56 :


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

SQL delete * from tbl_dftr_mod where id_pel = '16DAT03F001' and sem = 'SEMESTER 1' and id_dftr in (19)
DATASOURCE DatModul
VENDORERRORCODE 170
SQLSTATE HY000

October 16, 2006
See the error message? It shows delete *.

This is the error in the current block of code.

If you have now made this correction, either the changed file did not get saved or caching is turned on and CF is using a cached version of the file.

Cut, paste, and save the last code I posted and clear the CF cache.
clixmaxAuthor
Participating Frequently
October 16, 2006
Still error:

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Line 1: Incorrect syntax near &apos;*&apos;.

The error occurred in E:\PSIS\dlt_modul.cfm: line 54

52 : delete *
53 : from tbl_dftr_mod
54 : where id_pel = '#form.idpel#' and sem = '#form.sem#' and id_dftr in (#Form.cb_gugur#)
55 : </cfquery>
56 :


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

SQL delete * from tbl_dftr_mod where id_pel = '16DAT03F001' and sem = 'SEMESTER 1' and id_dftr in (19)
DATASOURCE DatModul
VENDORERRORCODE 170
SQLSTATE HY000

October 16, 2006
Did you use the code I specified? (no)
clixmaxAuthor
Participating Frequently
October 16, 2006
id_dftr is numeric in datatype (SQL server). it works in MS Access. Actually, this code will allow me to delete the multiple record regarding to the checkbox. Here the code:

<cfquery name="sen_mod" datasource="DatModul">
select *
from tbl_dftr_mod, tbl_modul
where tbl_dftr_mod.id_pel = '#form.idpel#' and tbl_dftr_mod.sem = '#form.sem#' and tbl_dftr_mod.kod = tbl_modul.kod
order by tbl_dftr_mod.kod
</cfquery>

<cfquery name="del_mod_rec" datasource="DatModul">
delete *
from tbl_dftr_mod
where id_pel = '#form.idpel#' and sem = '#form.sem#' and id_dftr in (#ListQualify(Form.cb_gugur,"'",",","CHAR")#)
</cfquery>

i've try to change the code to id_dftr in (#Form.cb_gugur#) or id_dftr in (#ListQualify (Form.cb_gugur, "'", "," ,"ALL")#), but still doesn't work..
Pls..
October 16, 2006
Use:
<cfquery name="del_mod_rec" datasource="DatModul">
delete
from tbl_dftr_mod
where id_pel = '#form.idpel#' and sem = '#form.sem#' and id_dftr in (#Form.cb_gugur#)
</cfquery>
clixmaxAuthor
Participating Frequently
October 16, 2006
Anyway...i've got another problem in another page. this error appeared on the screen:
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Line 3: Incorrect syntax near &apos;)&apos;.


<cfquery name="chk_mod_rec" datasource="DatModul">
select id_pel, id_dftr
from tbl_dftr_mod
where id_pel = '#form.idpel#'
and
id_dftr in (#ListQualify(Form.cb_gugur,"'",",","CHAR")#)
</cfquery>

What's the problem of my SQL code?
October 16, 2006
Is id_dftr numeric or string?

If it is numeric, use:
id_dftr in (#Form.cb_gugur#)

If it is string, use:
id_dftr in (#ListQualify (Form.cb_gugur, "'", "," ,"ALL")#)
October 15, 2006
The error occured because you tried to update two tables with one update statement. This is invalid SQL even though MS Access allows it.

Use the attached code.