Connecting to a database through SQL using a non-DNS connection

[Vegas34]

I am just learning ASP and I'm having tons of trouble connecting to a simple database. I keep getting this error "Microsoft VBScript runtime error '800a01f4'...Variable is undefined: 'mySQL'.../robsasp.asp, line 14" Can anyone help? here is the code.......<br>
<br>
&lt;% Option Explicit %&gt;<br>
<br>
&lt;html&gt;<br>
<br>
&lt;body&gt;<br>
<br>
&lt;h1&gt;The Cafeteria Menu&lt;/h1&gt;<br>
<br>
&lt;% <br>
Dim objConn<br>
Set objConn = Server.CreateObject("ADODB.Connection") <br>
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51; Data Source=e:\web\alsnevada.com\menu.mdb; Initial Catalog=menu; User ID=; Password="<br>
' myDSN="DRIVER={Microsoft Access Driver (*.mdb)};"<br>
mySQL="select * FROM Menu ORDER BY Date"<br>
Set OnMenu = Connect.Execute(mySQL)<br>
do until OnMenu.EOF %&gt;<br>
&lt;font size=5&gt;&lt;%=OnMenu("Date")%&gt;&lt;/font&gt;&lt;br&gt;<br>
Main Course: &lt;%=OnMenu("MainCourse")%&gt;&lt;br&gt;<br>
Fruit: &lt;%=OnMenu("Fruit")%&gt;&lt;br&gt;<br>
Vegetables: &lt;%=OnMenu("Vegetable")%&gt;&lt;br&gt;<br>
Dessert: &lt;%=OnMenu("Dessert")%&gt;&lt;br&gt;<br>
&lt;% OnMenu.MoveNext<br>
loop %&gt;<br>
&lt;/body&gt;<br>
&lt;/html&gt;

 

[Fly]

Use the Active Connection property.<br>
<br>
Example:<br>
<br>
adoCmd.ActiveConnection = "DRIVER=SQLServer;SERVER=XYZ;UID=AAA;DATABASE=SSSS;PASSWORD=xyzzzz;"

 

[jfriestman]

Your error has nothing to do with the DSN-less connection. Rather, like it says, you have undeclared variables.<br>
<br>
You use Option Explicit, good. This forces you to declare all your variables and makes for more reliable code. However, with a quick overview of your code I notice that you are not declaring several of your variables, the mySQL is the first one, OnMenu is the second and myDSN (though commented) is the third.<br>
<br>
Declare these variables and give it another try.<br>
<br>

 

[Vegas34]

I have tried it after DIMing the variables and I still get errors. I'm so lost, and feel like I've tried everything.

 

[jfriestman]

What errors are you getting now? If you properly dimensioned the variables, I know you're not getting a "Variable is undefined" error. Since you didn't provide the error, I can't troubleshoot the exact problem so I wrote you some code instead.<br>
<br>
This sample does a couple of things:<br>
1. demonstrates how to establish a DSN-less connection to the Northwind database (for the purposes of this sample, I put my DB in c:\temp).<br>
2. I've written a sub procedure that will spit the contents of a recordset to HTML, all you have to do is provide a DSN and a SQL statement. Test it with Northwind so you can be satisfied it works, then start tweaking it to work with your particular dataset.<br>
<br>
Good luck and don't despair, ASP is fun!<br>
<br>
&lt;%@ language="VBScript"%&gt;<br>
&lt;% Option Explicit %&gt;<br>
<br>
&lt;%<br>
Sub SQL2HTML (strSQL, strDSN)<br>
Dim oRs, intFieldCount, htm<br>
Set oRs = Server.Createobject("ADODB.Recordset")<br>
oRs.Open strSQL, strDSN<br>
intFieldCount = oRs.Fields.Count - 1<br>
htm = "&lt;table border=1&gt;"<br>
Dim i<br>
For i = 0 to intFieldCount<br>
htm = htm & "&lt;th&gt;" & oRs(i).Name & "&lt;/th&gt;"<br>
Next<br>
Dim fldValue<br>
Do While Not oRs.EOF<br>
htm = htm & "&lt;tr&gt;"<br>
For i = 0 to intFieldCount<br>
fldValue = oRs(i)<br>
If IsNull(fldValue) Then<br>
fldValue = "&nbsp;"<br>
End If<br>
htm = htm & "&lt;td&gt;" & fldValue & "&lt;/td&gt;"<br>
Next<br>
htm = htm & "&lt;/tr&gt;" <br>
oRs.MoveNext<br>
Loop<br>
htm = htm & "&lt;/table&gt;"<br>
oRs.Close<br>
Set oRs = Nothing<br>
Response.Write htm<br>
End Sub<br>
%&gt;<br>
<br>
&lt;html&gt;<br>
&lt;head&gt;<br>
&lt;title&gt;&lt;/title&gt;<br>
&lt;/head&gt;<br>
&lt;body&gt;<br>
&lt;%<br>
Dim dsn, sql<br>
dsn="DRIVER={Microsoft Access Driver (*.mdb)};DBQ=c:\temp\nwind.mdb"<br>
sql = "select * from customers"<br>
Response.Write sql<br>
SQL2HTML sql, dsn<br>
%&gt;<br>
<br>
&lt;/body&gt;<br>
&lt;/html&gt;