Two SQL queries?

[Airpan]

Is it possible to run two SQL queries on the same ASP page?

~E

 

[Sheco]

Yes. You can even re-use a single recordset if you want.

 

[bslintx]

simple access database example for you...sql server has a different connection string...google it if needed

<%
  Option Explicit

  Dim oConn, oRecordset1, oRecordset2, sConn, sFilePath

   Set oConn = Server.CreateObject("ADODB.Connection")
   sFilePath = Server.MapPath("mydatabase.mdb")
   sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFilePath & ";" 
   oConn.Open sConn 
 
   Set oRecordset1 = oConn.Execute("SELECT col FROM tTable1;")
   Set oRecordset2 = oConn.Execute("SELECT col FROM tTable2;")

   If Not oRecordset1.EOF Then 
    Response.Write "SQL 1 result: "

    Do While Not oRecordset1.EOF
      Response.Write oRecordset1("col") & "<br>"
    oRecordset1.MoveNext
    Loop
   End If

   Response.Write "<hr>"

   If Not oRecordset2.EOF Then 
    Response.Write "SQL 2 result: "

    Do While Not oRecordset2.EOF
      Response.Write oRecordset2("col") & "<br>"
    oRecordset2.MoveNext
    Loop
   End If  
%>

 

[Airpan]

Thanks you both. I will give it a shot and post back if I run into trouble.

~E

 

[Airpan]

I am getting the following error:
Data type mismatch in criteria expression.line 18
Line 18:
Set Rec2 = Con.Execute("SELECT * FROM cust_inventory WHERE [CustID] = '" & UserID & "' ")

I set it up for the first query which is placed in the head tag:

<%Session("sessUserName")=Request.Form("username")%>
<%=Response.Write("Welcome ")%>
<%=Response.Write(Session("sessUserName"))%>
<%=Response.Write("<br>")%>
<%

   Dim Con, sql, Rec1, Rec2
   set Con = Server.CreateObject("ADODB.Connection")
   Con.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("db/logins.mdb")
   UserID = Session("sessUserName")
   'Select the record matching the username.
  Set Rec1 = Con.Execute("SELECT * FROM cust_logins WHERE [USERNAME]= '"& UserID &"' ")
   Set Rec2 = Con.Execute("SELECT * FROM cust_inventory WHERE [CustID] = '" & UserID & "' ")
   'SQL = "SELECT * FROM cust_logins WHERE [USERNAME]= '"& UserID &"' "

   set Rec1=Con.execute(sql)

	Do While Not Rec1.EOF
Response.Write("<b>Customer ID:</b>")
Response.Write(rec("CustID"))
Response.Write("<br>")
Response.Write("<b>Customer Name:</b>")
Response.Write(rec("CustName"))
Response.Write("<br>")
Response.Write("<b>Customer Address:</b>")
Response.Write(rec("Address"))
Response.Write("<br>")
Response.Write("<b>City:</b>")
Response.Write(rec("City"))
Response.Write("<br>")
Response.Write("<b>State:</b>")
Response.Write(rec("State"))
Response.Write("<br>")
Response.Write("<b>Zip:</b>")
Response.Write(rec("Zip"))
Response.Write("<br>")
Response.Write("<b>Email:</b>")
Response.Write(rec("email"))
Response.Write("<br>")
Response.Write("<a href=editprofile.asp?customer=" & UserID & ">")
Response.Write("Edit Profile")
Response.Write("</a>")
Rec1.MoveNext
Loop
%>

The second query I put in the body tag like so:

<%
	Set Rec2=Con.execute(sql)
	Response.Write("<table border=1><tr><th>Pic</th><th>ListingID</th><th>STK</th><th>YR</th><th>Make</th><th>Model</th></tr>")
    Do While Not Rec2.EOF
Response.Write("<tr>")
For x=0 to rec.Fields.Count-1
Response.Write("<td>")
Response.Write(rec(x))
Response.Write("</td>")
Next
Response.Write("</tr>")
rec.Movenext
Loop
Response.Write("</table>")
Response.Write("<br>")
Rec2.Close
Con.Close
%>

I may be wrong (and probably am) but thought that the code provided by bslintx was more or less giving criteria for one or the other to run, not both of them. I know, I know... I should have tested the code. If I need to go ahead and do so I will, but want to be sure that I am getting both queries to run, not just one or the other.

~E

 

[Chopstik]

Is your UserID the same value for both the CustID in your cust_inventory table and the USERNAME in your cust_logins table? That is what you have coded which may be where your problem lies.

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[Airpan]

Chopstik,
No. UserID is a variable I created to represent the session variable, which represents the USERNAME in both tables. The CustID is also in both tables but it is a numerical value. Also for what it is worth I did not tie the tables together by CustID, due to wanting to be able to manipulate the data separately. At any rate, am I understanding you when you say that perhaps my second SQL query should not utilize the UserID and utilize something else? Would that differentiate them enough?

~E

 

[Airpan]

As an afterthought, I tried this:

Set Rec2 = Con.Execute("SELECT * FROM cust_inventory WHERE [USERNAME] = '" & UserID & "' ")

It generates the following error:
Command text was not set for the command object. Line 21
Line 21 is:

set Rec1=Con.execute(sql)

~E

 

[monksnake]

I see this Airpan for your connection string:

Con.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("db/logins.mdb")

That is not a valid SQL connection string.

Take a look here for an SQL connection string.

http://www.codeproject.com/database/connectionstrings.asp

<.

 

[Chopstik]

airpan, you said that UserID represents the USERNAME field in both tables. The problem then lies here:

Set Rec2 = Con.Execute("SELECT * FROM cust_inventory WHERE [CustID] = '" & UserID & "' ")

Your CustID is a numeric value but you're filtering it based on your UserID (which is actually the USERNAME field, not the CustID field). If you want to filter it on the numeric CustID field, then you need to change it to a variable that represents that field. You will also need to change your code slightly to look for a numeric value, not a text value, so remove the quotes from your variable, like so:

Set Rec2 = Con.Execute("SELECT * FROM cust_inventory WHERE [CustID] = " & [COLOR=red]CustID_Variable[/color])

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[Airpan]

Chopstik,
I will give that a shot and post back.

~E

 

[Airpan]

Chopstik,
I understand what you mean by removing the single quotes in the above query, but I need the single quotes b/c I do want to use the session variable (aka username) to filter the queries, so I wrote them like so:

Set Rec1 = sConn.Execute("SELECT * FROM cust_logins WHERE [USERNAME]= '"& UserID &"' ")
Set Rec2 = sConn.Execute("SELECT * FROM cust_inventory WHERE [USERNAME] = '" & UserID & "' ")

I also changed my connection code as you suggested, but I ended up using the connection information provided here because I had a very difficult time getting the one from the web site you suggested to work. It kept giving me errors regarding the characters in the connection string.
Here is what I have for connection purposes:

Dim oConn, Rec1, Rec2, sConn, sFilePath

Set oConn = Server.CreateObject("ADODB.Connection")
sFilePath = Server.MapPath("db/logins.mdb")
sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFilePath & ";"
oConn.Open sConn
UserID = Session("sessUserName")
set Rec1=sConn.execute(sql)
     Do While Not Rec1.EOF

I get the following error:
Object required: 'Provider=Microsoft.J',line 19
Line 19:

Set Rec1 = sConn.Execute("SELECT * FROM cust_logins WHERE [USERNAME]= '"& UserID &"' ")

Do you think it is the quotes? I had a similar issue when attempting to use the one from the site you gave me and everytime I tried to shift the quotes, it made other things not function. I tell ya, this stuff will drive you to drink.
Thanks for your help again. )


~E

 

[Chopstik]

Should this read as oConn, not sConn?

[COLOR=red]s[/color]Conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFilePath & ";"

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[Airpan]

Chopstik,
I tried the change an it still generates the same error.

Monksnake,
I am sorry, but I thought it was Chopstik who posted the recommendation for the connection string. Apologies!

I want to just throw this out there to. I am not using this particular section of the code provided by bslintx:

If Not oRecordset1.EOF Then
    Response.Write "SQL 1 result: "

    Do While Not oRecordset1.EOF
      Response.Write oRecordset1("col") & "<br>"
    oRecordset1.MoveNext
    Loop
   End If

   Response.Write "<hr>"

   If Not oRecordset2.EOF Then
    Response.Write "SQL 2 result: "

    Do While Not oRecordset2.EOF
      Response.Write oRecordset2("col") & "<br>"
    oRecordset2.MoveNext
    Loop
   End If

I was afraid that if I included it, it would only run one query or the other not both. Was I wrong to think that and if so, shall I include it?

~E

 

[Chopstik]

I think I see the issue. I made a mistake in my previous post. See the amended version below. The problem is that you were referencing your connection string, not the connection object itself.

Dim oConn, Rec1, Rec2, sConn, sFilePath

Set oConn = Server.CreateObject("ADODB.Connection")
sFilePath = Server.MapPath("db/logins.mdb")
sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFilePath & ";"
oConn.Open sConn
UserID = Session("sessUserName")
set Rec1=[COLOR=red]o[/color]Conn.execute(sql)
     Do While Not Rec1.EOF

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[Airpan]

chopstik,
will give it another try and post back. Thanks again.

~E

 

[Airpan]

Chopstik,
WHOSE HOUSE? AIRPAN'S HOUSE!!!!!
First of all, I figured out my mistake after you caught the one you caught.
There should be no statements

set Rec1=oConn.execute(sql)

It isn't needed and furthermore, there is no Dim for the varaible sql, so it doesn't work. The sql is executed by calling the Set Rec1, etc etc. DUH. I think I left it in there by mistake thinking that I would need to execute the sql. I am sorry that I made such a stupid mistake! Thanks for your persistence and help. Maybe one day I will be able to help someone else here? Um, yeah - when pigs fly.


~E

 

[bslintx]

glad you got it to work!...btw..mine was tested as is...literally, and worked fine ;-)