Query a Query/Multiple Conn

[danieljoo]

I've created an asp page that allows the user to search for a company based on company name, number or city (using like). I then built a string using the criteria:

''''''''''''''''''''''''''''''''''''''''''''''''''''''
BuildString = "SELECT * FROM Customer WHERE " & buildstring & ";"
sqlstr = BuildString
<%
Set Conn = Server.CreateObject(&quot;ADODB.Connection&quot
Conn.Open &quot;test&quot;, &quot;DSN=test;UID=;PWD=&quot;
Set RS = Conn.Execute (sqlstr)%>
''''''''''''''''''''''''''''''''''''''''''''''''''''''

I then display the results, and use the company number as a querystring to find out employees belonging to that company:

''''''''''''''''''''''''''''''''''''''''''''''''''''''
<a href=&quot;attendees.asp?customernumber=<%=RS(&quot;customernumber&quot%>&quot;><%=RS(&quot;CustomerName&quot%></a> 
''''''''''''''''''''''''''''''''''''''''''''''''''''''

On the next page I try to open another connection, but keep getting this error:

[Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view its data.

Here is my code when I try and use the customernumber to display the employees (from another table):

''''''''''''''''''''''''''''''''''''''''''''''''''''''
<%
myVar2=Request.Querystring(&quot;customernumber&quot
BuildString2 = &quot;SELECT * FROM attendee WHERE attendee.customernumber =&quot; & &quot;'&quot; & myVar2 & &quot;'&quot; & &quot;;&quot;
sqlstr2 = BuildString2
%>
<%
Set Conn = Server.CreateObject(&quot;ADODB.Connection&quot
Conn.Open &quot;test&quot;, &quot;DSN=test;UID=;PWD=&quot;
Set RS = Conn.Execute (sqlstr2)%>
''''''''''''''''''''''''''''''''''''''''''''''''''''''

What am I doing wrong?

 

[Tarwn]

Make sure your are closing both the recordset and the connection in the first piece of code, then set both objects = Nothing


-Tarwn --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
For my next trick I will pull a hat out of a rabbit (if you think thats bad you should see how the pigeon feels...)