Solved

Syntax error (missing operator) in query expression

Posted on 2003-12-05
37
15,210 Views
Last Modified: 2012-08-14
what's wrong with this code...

no = Request.Form("id_no")
      strSQL = "SELECT id_no,nama_jbtn,jenama,no_siri,no_harta1,harga FROM DAFTARHARTASH01 WHERE id_no ="  & no & " "
      set rs = objConn.execute(strSQL)


'id_no' is auto number, when i run..error exist...


Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'id_no ='.
/spick/pembaikan.asp, line 29









0
Comment
Question by:eez81
  • 17
  • 12
  • 8
37 Comments
 
LVL 4

Expert Comment

by:dasari
ID: 9886293
why r u appending an empty character at the end of the query.....

Try this stmt instead, strSQL = "SELECT id_no,nama_jbtn,jenama,no_siri,no_harta1,harga FROM DAFTARHARTASH01 WHERE id_no ="  & Trim(no)

I guess this would work but not sure......

Also what is the data type for the variable "no"

HTH
0
 

Author Comment

by:eez81
ID: 9886410
thanz dasari...it work better now..
0
 
LVL 15

Expert Comment

by:Raisor
ID: 9888922
Hi,

It all depends on how your declaration look like ... are we talking about "int", "char", "varchar", "nvarchar" ... etc. ?

If it's about characters -> and this would make sense regarding the error message

then try this:

strSQL = "SELECT id_no,nama_jbtn,jenama,no_siri,no_harta1,harga FROM DAFTARHARTASH01 WHERE id_no ='"  & no & "'"


Best regards, Raisor
0
 
LVL 4

Expert Comment

by:dasari
ID: 9889163
'id_no' is auto number...............so the above query won't work if we append single quotes(') before and after....

Peace!
0
 
LVL 15

Expert Comment

by:Raisor
ID: 9889194
Hi,

Well, no we got it I think -> you can not set/assign any value to an autonumber field!

Create another field with a numeriv value -> and leave autonumber to be filled "automatically" ...


Best regards, Raisor
0
 
LVL 15

Expert Comment

by:Raisor
ID: 9889202
Hi,

... @dasari -> and why would a "trim" to a numeric value change anything?


Best regards, Raisor
0
 
LVL 4

Expert Comment

by:dasari
ID: 9889210
I agree with "you can not set/assign any value to an autonumber field!"

but the query we are trying to address here is a select statement with a criteria expression on auto number field, as a result we do not need to append single quotes........

Peace!
0
 
LVL 15

Expert Comment

by:Raisor
ID: 9889231
Hi,

... ups ... you're absolutely right! ... I don't know what I've seen on first glance! ...

... anyway ... a "trim" is still not needed as eez81 just had to take off ->  & " " <- at the end of the statement ;-))


Best regards, Raisor
0
 
LVL 4

Expert Comment

by:dasari
ID: 9889240
If you refer to the question posted at the top, it is unclear what type is used for variable "no" in the VB........so it's not a bad idea to trim a variable which is assigned from a field in the form....it's a best practice to trim variables that are assigned with form fields any programming area......

but the thing is, he got the query working so we don't have to break our brains anymore buddy......

Peace!
0
 
LVL 15

Expert Comment

by:Raisor
ID: 9889267
Hi,

... I agree with you! ... but still ... ;-)) ... the Trim statement expects to have a "string" as parameter -> wouldn't it be usefull to write -> Trim(cStr(no)) -> if no is a numeric value? ... just to return to best practice?

Don't take this serious! I'm happy for eez81 that everything works fine!


Best regards, Raisor
0
 

Author Comment

by:eez81
ID: 9891385
**********************
part of html file:carian.html
**********************
<p align=center><h3>Sila nyatakan pencarian anda:</h3>
<form action="\test\papar1.asp" Method=Post>
<center><table border=1>
<input type="submit" value="Sila Proses" >
<input type="reset" value="Sila Bersih" >
</form></p></center></body></html>


********************
part of asp file:papar1.asp
********************
ID = Request.Form("fmatrik")
      strSQL = "SELECT * FROM zee WHERE id_no ="  & ID & " "
      set rs = objConn.execute(strSQL)
%>
<center><table border=1 bgcolor="#00FFFF">
      <tr><td><b>ID:</b></td>
            <td><INPUT type=hidden name="ID" value="<% =rs("id_no") %>"><% =rs("id_no") %></td></tr>
      <tr><td><b>Matrik:</b></td>
            <td><INPUT type=hidden name="matrik" value="<% =rs("matrik") %>"><% =rs("matrik") %></td></tr>
      <tr><td><b>Nama:</b></td>
            <td><INPUT type=hidden name="nama" value="<% =rs("nama") %>"><% =rs("nama") %></td></tr>
      <tr><td><b>Mel:</b></td>
            <td><INPUT type=hidden name="mel" value="<% =rs("mel") %>"><% =rs("mel") %></td></tr>
</table><br>

<center><p><form action="/test/papar2.asp"method="post"></p>
<input type=hidden name=pelajar value="<%=id_no%>">
<center><input type=submit value="Pelajar">


********************
part of asp file:papar2.asp
********************

strSQL = "SELECT * FROM cth WHERE id_no = " & Request("pelajar") & ";"
      set rs = objConn.execute(strSQL)
%>

<center><table border=1 bgcolor="#00FFFF">
<tr><td><b>ID:</b></td>
<td><INPUT type=hidden name="ID" value="<% =rs("id_no") %>"><% =rs("id_no") %></td></tr>
      
<tr><td><b>Nama:</b></td>
<td><INPUT type=hidden name="nama" value="<% =rs("nama") %>"><% =rs("nama") %></td></tr>
<tr><td><b>Mel:</b></td><td><INPUT type=hidden name="mel" value="<% =rs("mel") %>"><% =rs("mel") %></td></tr>


MY PROBLEM IS:
The first frame is carian.html.when i key in the input data,and then i push submit button,papar1.asp appear....in papar1.asp,have 1 submit button"Pelajar".when i push this button,the output file papar2.asp should appear which the data must same with output in papar1.asp but error exist:

*************************
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'id_no ='.
/test/edit2.asp, line 29
****************************
id_no is auto number
cth is query in my mdb file
so anybody can help me....
0
 
LVL 15

Expert Comment

by:Raisor
ID: 9891482
Hi,

Here once again you've set  -> & " " <- behind "ID"

... and what is ->  & ";" <- for behind  -> "SELECT * FROM cth WHERE id_no = " & Request("pelajar") <- ?


Best regards, Raisor
0
 

Author Comment

by:eez81
ID: 9891607
i refer to the book and the example show this  &";".if it wrong can u plz help me..
0
 
LVL 4

Expert Comment

by:dasari
ID: 9892556
Can you post the cth query here.....
--
cth is query in my mdb file
0
 

Author Comment

by:eez81
ID: 9894316
cth query
field:
id_no
matrik
nama
mel
0
 
LVL 4

Expert Comment

by:dasari
ID: 9894327
Well the error might be in the query...U need to post the actual query....
0
 

Author Comment

by:eez81
ID: 9894464
how could i post the actual query???
0
 
LVL 4

Expert Comment

by:dasari
ID: 9894500
Open cth query in design mode and goto Menu "VIEW", choose "SQL View", u'll see the query in a window.......paste the same here
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:eez81
ID: 9894532
u mean this:

SELECT zee.id_no, zee.matrik, zee.nama, zee.mel
FROM zee;
0
 
LVL 4

Expert Comment

by:dasari
ID: 9894553
IF the above posted query is the cth query why don't we replace the stmt in your code something like this.....

strSQL = "SELECT zee.id_no, zee.matrik, zee.nama, zee.mel FROM Zee WHERE Zee.id_no = " & Request("pelajar") & ";"

considering Zee.id_no is still an autonumber field....
0
 

Author Comment

by:eez81
ID: 9894589
i still got the same error...
0
 
LVL 4

Expert Comment

by:dasari
ID: 9894629
Well try this now.....

strSQL = "SELECT zee.id_no, zee.matrik, zee.nama, zee.mel FROM Zee WHERE Zee.id_no =" & Request("pelajar")

are u sure the Zee.id_no is an autonumber field and the value returned by Request("pelajar") is a number...

0
 

Author Comment

by:eez81
ID: 9894707
yes...i'm sure...zee.id_no is an autonumber.
0
 
LVL 4

Expert Comment

by:dasari
ID: 9894715
Can u try this one...

strSQL = "SELECT zee.id_no, zee.matrik, zee.nama, zee.mel FROM Zee WHERE Zee.id_no =1"

or put any other value that already exists in Zee.id_no and also can u make sure Request("pelajar") is returning a valid value.....
0
 

Author Comment

by:eez81
ID: 9894768
dasari...i try ur code..it work ...so how do i apply this to Request("pelajar")
0
 
LVL 4

Accepted Solution

by:
dasari earned 20 total points
ID: 9894845
I guess Request("pelajar") is not holding a valid value.....

so why don't u add the following statement before strSQL = "SELECT * FROM cth WHERE id_no = " & Request("pelajar") & ";"

Response.Write Request("pelajar")
0
 

Author Comment

by:eez81
ID: 9896421
i don't what is going on..i still got an error...when i'm trying WHEREid_no =1" it work...but if i apply it with the code above...i got the same error....so...how????plz help me...
0
 
LVL 4

Expert Comment

by:dasari
ID: 9896772
We will get an error when u try the above stmt but what did Response.Write Request("pelajar") print to the webpage...first u need to find out what's the value at Request("pelajar").....

this is not a best practice but try this.....but I want to test something here.....if an error occurs, please post the error here and also don't forget to let me know the value at Request("pelajar").....

strSQL = "SELECT zee.id_no, zee.matrik, zee.nama, zee.mel FROM Zee WHERE Zee.id_no =" & CInt(Request("pelajar"))
0
 
LVL 4

Expert Comment

by:dasari
ID: 9896775
small change....Well change the query to this......

strSQL = "SELECT zee.id_no, zee.matrik, zee.nama, zee.mel FROM Zee WHERE Zee.id_no =" & CInt(Trim(Request("pelajar")))
0
 

Author Comment

by:eez81
ID: 9900759
Error Type:
Microsoft VBScript runtime (0x800A000D)
Type mismatch: 'CInt'
/test/edit2.asp, line 29


0
 
LVL 4

Expert Comment

by:dasari
ID: 9900823
So it's clear that Request("pelajar") doesn't have a valid value....

dude! U gotta use the stmt Response.Write Request("pelajar") and lemme know wat it prints to the screen.....
0
 
LVL 15

Expert Comment

by:Raisor
ID: 9900877
Hi,

Sorry ... I don't want to interrupt this -> strSQL is as string so instead of using cInt ... cStr might fit ...


Best regards, Raisor
0
 
LVL 4

Expert Comment

by:dasari
ID: 9900976
No Problem Raisor....Let's rock dis joint....

id_no is an autonumber field so I guess it expects a numeric data, that's why I tried to convert it to integer....this may not work if id_no has a bigger number (more than wat an integer can hold) I don't remember on the top of my head but I guess it is 32767....I was just trying to make sure if Request("pelajar") holds a valid value or not......wat do ya think Raisor!
0
 
LVL 15

Expert Comment

by:Raisor
ID: 9901044
Hi,

To be honest -> I think that it should hold a numeric value -> it sure does if it is an autonumber (which is not yet proven to have been checked manually!!!) -> but what's giving me awfull feelings is that there seem to be a lot of conversion errors from the way of "being a number" -> "being passed as string" -> and returning "to be a number" ---> I can't think of anything else ... like I was saying earlier on:

------->       "are we talking about "int", "char", "varchar", "nvarchar" ... etc. ?"       <-------

Anyway ... this is the point where an expert has to look at the database, the tables, the formats and has to perform some error trapping connects and queries ...

... I don't think that there's a chance to solving this without being present at any single byte & bit ...


Best regards, Raisor
0
 
LVL 4

Expert Comment

by:dasari
ID: 9901061
I agree...............
0
 

Author Comment

by:eez81
ID: 9901064
hai dasari....u r right....i make a mistake at value Response(pelajar)....i return wrong value...sory..thats my fault...i change the value....so now everthing is fine...it work better...thanz 4 ur help and raisor...i really appreciate it.....
regards...
0
 
LVL 4

Expert Comment

by:dasari
ID: 9901345
Great! Good Luck eez81......

Catch u later Raisor.....

Peace!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction: I have seen many questions on EE and elsewhere, asking about how to find either gaps in lists of numbers (id field, usually) ranges of values or dates overlapping date ranges combined date ranges I thought it would be a good …
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now