rst.Open syntax error

sromine · Jan 18, 2008

Hello,

I have been tasked with moving an internal application running asp and connecting to a mysql database from one server to another....I did not program the original application, and I am not a programmer by trade, although I can debug most code with a little work and some help from user communities

I receive the following error:
[MySQL][ODBC 3.51 Driver][mysqld-5.0.45-community-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 'priority' at line 1

/admin_form.asp, line 43

The line in question is the following:
rst.Open "type","dsn=myodbc"

It is probably glaring to someone with knowledge of ASP, can someone point me in the right direction for a solution? Thanks!

sromine · Jan 18, 2008

Let me add the line above it which might help as well...

Set rst = Server.CreateObject("ADODB.recordset")
rst.Open "type","dsn=myodbc"

ethorn10 · Jan 18, 2008

It is most likely an error in the actual sql statement (probably set a few lines above line 43).

Can you post that line as well?

sromine · Jan 18, 2008

Here is all the code. This seems to work in other areas, but the difference is that instead of rts.Open "priority" in other scripts it is doing a select query of some type.....the purpose of this portion of the code is to enter data into a form.....thank you for your help on this...

<%@ Language=VBScript %>
<%
usersid=request.QueryString("usersid")
account=request.QueryString("account")
usergroupid=request.QueryString("usergroupid")
ptitle="admin_form"
%>
<html>
<head>
<title>Tech Log</title>
<style type="text/css" media="screen">@import url(sample1.css);</style>


</head>
<body>
<div id="content">
<%

'This creates a unique number based on date and time without formatting
'to allow a query to get the unique call id without establishing a session
'first.
dim v1
dim v2
dim v3
v1=now()
v2=Replace(v1,"/","")
v3=Replace(v2,":","")
add_unique = v3

%>
PLease fill in all fields as completely as you can!<br /><br />
<form name="add" action="admin_add.asp?usersid=<% =usersid %>&account=<% =account %>&usergroupid=<% =usergroupid %>" method="post">

<label for="summary">Enter a short summary of your issue<input type="text" name="summary" class="input-box" /> <br />
<label for="callback">A phone number to reach you<input type="text" name="callback" class="input-box" /> <br />

<%
'Instantiate a Recordset object and open a recordset using
'the Open method
Set rst = Server.CreateObject("ADODB.recordset")
rst.Open "priority", "DSN=myodbc"

'response.write "<label for='priority'>Priority<br /><select name='priority' class='select'>"
response.write "<a href='UpdateTable.asp?Table=priority'>priority</a><br/><select name='priority' class='select'>"
response.write "<option></option>"
while not rst.eof
response.write "<option>"& rst(1).value &"</option>"
rst.movenext
wend

rst.close
set rst = nothing
response.write "</select>"
response.write "<br />"
%>
<br />
<%
'Instantiate a Recordset object and open a recordset using
'the Open method
Set rst = Server.CreateObject("ADODB.recordset")
rst.Open "type", "DSN=myodbc"

'response.write "<label for='priority'>Priority<br /><select name='priority' class='select'>"
response.write "<a href='UpdateTable.asp?Table=type'>Type of issue</a><br/><select name='calltype' class='select'>"
response.write "<option></option>"
while not rst.eof
response.write "<option>"& rst(1).value &"</option>"
rst.movenext
wend

rst.close
set rst = nothing
response.write "</select>"
response.write "<br />"
%>

<label for="description">Description (i.e. Which PC, who was using it, date/time of occurrence, description of the issue and quote the error )<textarea rows="3" cols="10" name="description" class="input-box"> </textarea> <br />
<input type="submit" value="Submit" class="submit-button" />
<input type="hidden" name="uniquelog" value=" <% =add_unique %> ">
<input type="hidden" name="add_trigger" value="true">
</form>
</div>


</body>
</html>

Sheco · Jan 18, 2008

Maybe it is trying to open a table named type on the database specified in the DSN named myodbc

So maybe there is a problem in the DSN pointing to the correct database or maybe the database doesn't have a table named type or maybe the word "type" is a reseved word in your database or.... hmmm, or maybe there is a property of the recordset object that needs to be set if you are going to just open a table directly.

sromine · Jan 18, 2008

There is a table called type and a table called priority....I know the connection works on some level because before this particular script is executed, others are that do show me data in the mysql table.....

it is hanging on the rst.Open "priority", "DSN=myodbc".....it doesnt seem to like this specific format, whereas in another area it doesnt seem to mind the following:
Set rst = Server.CreateObject("ADODB.recordset")
rst.Open strquery, "DSN=myodbc"

so, something about the rst.Open "priority"...it looks to me what this is doing is opening data in the mysql priority table that has data such as "critical", "routine", etc....

this code does work on another server, so something is different...the error noted on the first post says this is some kind of syntax error, which leads me to believe it is out of date code....anymore ideas, and thanks to everyone taking the time to check it out....

MoLaker · Jan 18, 2008

Try

strquery = "select * from priority"
Set rst = Server.CreateObject("ADODB.recordset")
rst.Open strquery, "DSN=myodbc"

Just to see if there is a problem with the priority table or the DSN.

"Retired Programmer". So, please be patient.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

rst.Open syntax error

sromine

Technical User

sromine

Technical User

ethorn10

Programmer

sromine

Technical User

Sheco

Programmer

sromine

Technical User

MoLaker

Programmer

Similar threads

Part and Inventory Search

Sponsor