Syntax error... but why? Need an extra pair of eyes.

<i><b>Originally posted by : Stefan (rpantern@hotmail.com)</b></i><br />I have made a little application, and it works fine with IIS3/Access95 database. When converting this to IIS4/SQL-srv7 I get a problem with syntax error, and I cannot understand why. I can't see why. It appears only when using UPDATE in combination with WHERE. SELECT/WHERE works fine with then same syntax for "where". Would greatly appreciate a solution to this.<br /><br />strconn = "Driver={SQL Server} ; Description=uppdrag ; SERVER=EXC ; UID=sa ; PWD=pwd ; DATABASE=support"<br /><br />Set Conn = Server.CreateObject("ADODB.Connection")<br /><br />Conn.Open strconn<br /><br />Conn.Execute ("UPDATE dsuppdrag SET kommentar = '" & request.form("kommentar") & "' WHERE counter = " & request.querystring("hid") & ";")<br /><br />Conn.Close<br /><br /><br />The error message says:<br /><br />Microsoft OLE DB Provider for ODBC Drivers error '80040e14' <br />[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ';'. <br /><br />

<i><b>Originally posted by : Bob</b></i><br />Get rid of the trailing ;<br /><br /><br />------------<br />Stefan at 5/10/2000 1:52:55 AM<br /><br />I have made a little application, and it works fine with IIS3/Access95 database. When converting this to IIS4/SQL-srv7 I get a problem with syntax error, and I cannot understand why. I can't see why. It appears only when using UPDATE in combination with WHERE. SELECT/WHERE works fine with then same syntax for "where". Would greatly appreciate a solution to this.<br /><br />strconn = "Driver={SQL Server} ; Description=uppdrag ; SERVER=EXC ; UID=sa ; PWD=pwd ; DATABASE=support"<br /><br />Set Conn = Server.CreateObject("ADODB.Connection")<br /><br />Conn.Open strconn<br /><br />Conn.Execute ("UPDATE dsuppdrag SET kommentar = '" & request.form("kommentar") & "' WHERE counter = " & request.querystring("hid") & ";")<br /><br />Conn.Close<br /><br /><br />The error message says:<br /><br />Microsoft OLE DB Provider for ODBC Drivers error '80040e14' <br />[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ';'. <br /><br />

<i><b>Originally posted by : </b></i><br />Already tried that, it results in another syntax error near "=" instead. *sigh*<br /><br /><br />------------<br />Bob at 5/10/2000 9:32:17 AM<br /><br />Get rid of the trailing ;<br /><br /><br />------------<br />Stefan at 5/10/2000 1:52:55 AM<br /><br />I have made a little application, and it works fine with IIS3/Access95 database. When converting this to IIS4/SQL-srv7 I get a problem with syntax error, and I cannot understand why. I can't see why. It appears only when using UPDATE in combination with WHERE. SELECT/WHERE works fine with then same syntax for "where". Would greatly appreciate a solution to this.<br /><br />strconn = "Driver={SQL Server} ; Description=uppdrag ; SERVER=EXC ; UID=sa ; PWD=pwd ; DATABASE=support"<br /><br />Set Conn = Server.CreateObject("ADODB.Connection")<br /><br />Conn.Open strconn<br /><br />Conn.Execute ("UPDATE dsuppdrag SET kommentar = '" & request.form("kommentar") & "' WHERE counter = " & request.querystring("hid") & ";")<br /><br />Conn.Close<br /><br /><br />The error message says:<br /><br />Microsoft OLE DB Provider for ODBC Drivers error '80040e14' <br />[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ';'. <br /><br />

<i><b>Originally posted by : </b></i><br />Response.Write your query string to make sure you've got what you think you've got. Then take the output of the query and paste it in the SQL Query tool. That's a good place to debug the query.<br /><br /><br />------------<br /> at 5/10/2000 1:28:35 PM<br /><br />Already tried that, it results in another syntax error near "=" instead. *sigh*<br /><br /><br />------------<br />Bob at 5/10/2000 9:32:17 AM<br /><br />Get rid of the trailing ;<br /><br /><br />------------<br />Stefan at 5/10/2000 1:52:55 AM<br /><br />I have made a little application, and it works fine with IIS3/Access95 database. When converting this to IIS4/SQL-srv7 I get a problem with syntax error, and I cannot understand why. I can't see why. It appears only when using UPDATE in combination with WHERE. SELECT/WHERE works fine with then same syntax for "where". Would greatly appreciate a solution to this.<br /><br />strconn = "Driver={SQL Server} ; Description=uppdrag ; SERVER=EXC ; UID=sa ; PWD=pwd ; DATABASE=support"<br /><br />Set Conn = Server.CreateObject("ADODB.Connection")<br /><br />Conn.Open strconn<br /><br />Conn.Execute ("UPDATE dsuppdrag SET kommentar = '" & request.form("kommentar") & "' WHERE counter = " & request.querystring("hid") & ";")<br /><br />Conn.Close<br /><br /><br />The error message says:<br /><br />Microsoft OLE DB Provider for ODBC Drivers error '80040e14' <br />[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ';'. <br /><br />

<i><b>Originally posted by : anon</b></i><br />assuming from the lack of an apostrophe near the "request.querystring("hid")" poriton of your sql code...you are trying to insert a numeric value. Try converting the querystring variable to a number (pref. integer) and sql shouldn't bounce it then. (also leave the trailing ";"'s for access only...sql doesn't need them.<br /><br /><br />------------<br /> at 5/11/2000 10:07:17 AM<br /><br />Response.Write your query string to make sure you've got what you think you've got. Then take the output of the query and paste it in the SQL Query tool. That's a good place to debug the query.<br /><br /><br />------------<br /> at 5/10/2000 1:28:35 PM<br /><br />Already tried that, it results in another syntax error near "=" instead. *sigh*<br /><br /><br />------------<br />Bob at 5/10/2000 9:32:17 AM<br /><br />Get rid of the trailing ;<br /><br /><br />------------<br />Stefan at 5/10/2000 1:52:55 AM<br /><br />I have made a little application, and it works fine with IIS3/Access95 database. When converting this to IIS4/SQL-srv7 I get a problem with syntax error, and I cannot understand why. I can't see why. It appears only when using UPDATE in combination with WHERE. SELECT/WHERE works fine with then same syntax for "where". Would greatly appreciate a solution to this.<br /><br />strconn = "Driver={SQL Server} ; Description=uppdrag ; SERVER=EXC ; UID=sa ; PWD=pwd ; DATABASE=support"<br /><br />Set Conn = Server.CreateObject("ADODB.Connection")<br /><br />Conn.Open strconn<br /><br />Conn.Execute ("UPDATE dsuppdrag SET kommentar = '" & request.form("kommentar") & "' WHERE counter = " & request.querystring("hid") & ";")<br /><br />Conn.Close<br /><br /><br />The error message says:<br /><br />Microsoft OLE DB Provider for ODBC Drivers error '80040e14' <br />[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ';'. <br /><br />