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

Participating Frequently

Question

Error Executing Database Query.

Forum|Forum|19 years ago
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?

Database access

This topic has been closed for replies.

C

clixmaxAuthor

Participating Frequently

hmm..so, is there any option to solve this problem?

P

paross1

Participating Frequently

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

C

clixmaxAuthor

Participating Frequently

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.

P

paross1

Participating Frequently

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

C

clixmaxAuthor

Participating Frequently

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.

P

paross1

Participating Frequently

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

C

clixmaxAuthor

Participating Frequently

huh!...still got error.. i don't know why..

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

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

A

Anonymous

DELETE * IS INVALID SQL!

Change this to "delete" (no asterix).

C

clixmaxAuthor

Participating Frequently

definitely yes..i've copy from that..but still error:

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

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

A

Anonymous

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.

C

clixmaxAuthor

Participating Frequently

Still error:

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

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

A

Anonymous

Did you use the code I specified? (no)

C

clixmaxAuthor

Participating Frequently

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

A

Anonymous

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>

C

clixmaxAuthor

Participating Frequently

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 ')'.

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

A

Anonymous

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")#)

A

Anonymous

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.

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