ASP MySql problem

Hi,

My code used to work since it started showing me this:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[MySQL][ODBC 3.51 Driver][mysqld-4.0.14-nt]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
/Auction/bidmenu.asp, line 97

In the bidmeny.asp code I have this:

90 Set my_conn = Server.CreateObject("ADODB.Connection")
91 Set rsItem = Server.CreateObject("ADODB.Recordset")
92 my_conn.Open "DSN=mysql_dns" ' Data source name
93
94 Dim rsItem, strItemName, strDescription, strPerson
95 strSQL = "SELECT * FROM Item WHERE ItemID =" & Request("Item")
96
97 Set rsItem = my_conn.Execute(strSQL)

I can't seem to fix it...does anyone have a clue what this could be?

does the Request("Item") render any result?

On line 96 do a Response.Write strSQL to see if the sql is correct. You can even test that statement in the mySql command prompt, to see if you have errors.

Judging by the error message, there is something wrong with the sql statement.

Another suggestion: complete the sql statement with a semicolon.

Hope this helps,

I added a static value instead of Request("Item") and it worked but then gave me the same error for another line where I also have a .execute(sql) command. Something is wrong with that command, that MySql cannot resolve or something.

Maybe I need some specific version of MySql or something to make it work...Because it always gives me an error on lines where I used ".execute" command. The SQL Query is good, I tested it in MySQL.

Try

hello im ed from kenya.just starting to use asp and mysql. got this syntax error could you please help me. below is the error and the code


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

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[MySQL][ODBC 3.51 Driver][mysqld-4.1.1a-alpha]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 'productid<>178 order by name' at line 1

/shop1/admin/product.asp, line 42


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

this is the vbscript

set rscatlist=db.execute("select name, productid from products where catcode=" & rsprod("catcode") & "and productid<>" & valid_sql(productid) & " order by name")


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

this is the other error

ADODB.Field error '80020009'

Either BOF or EOF is True, or the current record has been deleted; the operation requested by the application requires a current record.

?


<%
'Display a featured product

Set rsprod = Server.CreateObject("ADODB.Recordset")
rsprod.Open "select * from products where featured=true" , StrConn, adOpenStatic, adLockReadOnly
rmax=rsprod.recordcount

if not rsprod.eof then
t=Timer
Randomize t
rnum = Int(RND * rmax)
rsprod.move rnum


'set rsprod=db.execute("select * from products")

if not rsprod.eof then
%>
<%= rsprod("name") %>

<%= rsprod("featimage") %>
<% if rsprod("saleprice")="" or rsprod("saleprice")=0 then %>
<b>Price: <%= cursymbol & formatnumber(rsprod("price")) %>
<% else %>
<strike>Price: <%= cursymbol & formatnumber(rsprod("price")) %>
Sale: <%= cursymbol & formatnumber(rsprod("saleprice")) %>&nbsp;<img src="images/gifs/saletag.gif">
<% end if %>



<%= rsprod("shortdesc") %>
<% end if %>

<a href="addprod.asp?productid=<%= rsprod("productid")%>"><img src="images/gifs/add2cart.gif" border="0"></a>
<%
end if
db.Close
Set db = Nothing
%>


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

your help will be appreciated my email is fyonza@yahoo.com

hello im ed from kenya.just starting to use mysql. got this syntax error could you please help me. below is the error and the code


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

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[MySQL][ODBC 3.51 Driver][mysqld-4.1.1a-alpha]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 'productid<>178 order by name' at line 1

/shop1/admin/product.asp, line 42


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

this is the vbscript

set rscatlist=db.execute("select name, productid from products where catcode=" & rsprod("catcode") & "and productid<>" & valid_sql(productid) & " order by name")


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

thisistheothererror

ADODB.Field error '80020009'

Either BOF or EOF is True, or the current record has been deleted; the operation requested by the application requires a current record.

?


<%
'Display a featured product

Set rsprod = Server.CreateObject("ADODB.Recordset")
rsprod.Open "select * from products where featured=true" , StrConn, adOpenStatic, adLockReadOnly
rmax=rsprod.recordcount

if not rsprod.eof then
t=Timer
Randomize t
rnum = Int(RND * rmax)
rsprod.move rnum


'set rsprod=db.execute("select * from products")

if not rsprod.eof then
%>
<%= rsprod("name") %>

<%= rsprod("featimage") %>
<% if rsprod("saleprice")="" or rsprod("saleprice")=0 then %>
<b>Price: <%= cursymbol & formatnumber(rsprod("price")) %>
<% else %>
<strike>Price: <%= cursymbol & formatnumber(rsprod("price")) %>
Sale: <%= cursymbol & formatnumber(rsprod("saleprice")) %>&nbsp;<img src="images/gifs/saletag.gif">
<% end if %>



<%= rsprod("shortdesc") %>
<% end if %>

<a href="addprod.asp?productid=<%= rsprod("productid")%>"><img src="images/gifs/add2cart.gif" border="0"></a>
<%
end if
db.Close
Set db = Nothing
%>


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

your help will be appreciated my email is fyonza@yahoo.com