Syntax error (missing operator) in query expression

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









eez81Asked:
Who is Participating?
 
dasariConnect With a Mentor Commented:
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
 
dasariCommented:
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
 
eez81Author Commented:
thanz dasari...it work better now..
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Ralf KlattConcerned World CitizenCommented:
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
 
dasariCommented:
'id_no' is auto number...............so the above query won't work if we append single quotes(') before and after....

Peace!
0
 
Ralf KlattConcerned World CitizenCommented:
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
 
Ralf KlattConcerned World CitizenCommented:
Hi,

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


Best regards, Raisor
0
 
dasariCommented:
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
 
Ralf KlattConcerned World CitizenCommented:
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
 
dasariCommented:
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
 
Ralf KlattConcerned World CitizenCommented:
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
 
eez81Author Commented:
**********************
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
 
Ralf KlattConcerned World CitizenCommented:
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
 
eez81Author Commented:
i refer to the book and the example show this  &";".if it wrong can u plz help me..
0
 
dasariCommented:
Can you post the cth query here.....
--
cth is query in my mdb file
0
 
eez81Author Commented:
cth query
field:
id_no
matrik
nama
mel
0
 
dasariCommented:
Well the error might be in the query...U need to post the actual query....
0
 
eez81Author Commented:
how could i post the actual query???
0
 
dasariCommented:
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
 
eez81Author Commented:
u mean this:

SELECT zee.id_no, zee.matrik, zee.nama, zee.mel
FROM zee;
0
 
dasariCommented:
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
 
eez81Author Commented:
i still got the same error...
0
 
dasariCommented:
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
 
eez81Author Commented:
yes...i'm sure...zee.id_no is an autonumber.
0
 
dasariCommented:
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
 
eez81Author Commented:
dasari...i try ur code..it work ...so how do i apply this to Request("pelajar")
0
 
eez81Author Commented:
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
 
dasariCommented:
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
 
dasariCommented:
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
 
eez81Author Commented:
Error Type:
Microsoft VBScript runtime (0x800A000D)
Type mismatch: 'CInt'
/test/edit2.asp, line 29


0
 
dasariCommented:
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
 
Ralf KlattConcerned World CitizenCommented:
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
 
dasariCommented:
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
 
Ralf KlattConcerned World CitizenCommented:
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
 
dasariCommented:
I agree...............
0
 
eez81Author Commented:
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
 
dasariCommented:
Great! Good Luck eez81......

Catch u later Raisor.....

Peace!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.