query different databases from the same page

[fran7]

Hi, I wonder if anyone can tell me the trick to query two different databases with two dropdowns.

I have two dropdowns. One is included as an include. At the top of the page I have one database connection:

<%@ Language=VBScript %>
<!--#Include File="miniart/dbconnect.asp"-->
<%
 
Dim oRs, conn, connect, strSQL
 
set conn=server.CreateObject ("adodb.connection")
connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("/mini.mdb") & ";Persist Security Info=False"
conn.Open connect

I need the second one to query a different database...anyone any ideas?

Thanks for any help.
Richard

<select  onChange="window.location='http://www..com/mane.asp?CardDescription=' + this.options[this.selectedIndex].value">
<OPTION VALUE = art>&nbsp;art Option>
<OPTION>-----------------------------------------------------</Option>
<%        
    Set oRs=Server.CreateObject("adodb.recordset")
    strSQL = "SELECT DISTINCT CardDescription FROM tblCards Where CategoryID <> " & 60 & " and CategoryID<>" & 61 & " ORDER BY Description"
'strSQL = "SELECT DISTINCT CardDescription FROM tblCards Where CategoryID<>" & 61 & "  ORDER BY CardDescription"
 
    oRs.Open strSQL, conn        
 
    Do while not oRs.EOF
        if Request.Form("GreetingPostCards") = oRs("CardDescription") then 'if this is the selected one then display as selected
            Response.Write "<OPTION VALUE = '" & oRS ("CardDescription") & "' SELECTED>"
            Response.Write oRs("CardDescription") & "</Option>"
            oRs.MoveNext 
        else
            Response.Write "<OPTION VALUE = '" & oRs ("CardDescription") & "'>&nbsp;"
            Response.Write oRs("CardDescription") & ",</Option>"
            oRs.MoveNext 
        end if
    loop        
    %>
</SELECT>
</div>
  </form>

[fran7]

Just thought I would say I worked this one out.

Putting this at the beginning of each drop down and directing them to the different databases did the trick

        set conn=server.CreateObject ("adodb.connection")
connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &_
      Server.MapPath("/the two databases.mdb") & ";Persist Security Info=False"
conn.Open connect

Thanks
Richard

[danp129]

Hi, I wonder if anyone can tell me the trick to query two different databases with two dropdowns.

I have two dropdowns. One is included as an include. At the top of the page I have one database connection:

<%@ Language=VBScript %>
<!--#Include File="miniart/dbconnect.asp"-->
<%
 
Dim oRs, conn, connect, strSQL
 
set conn=server.CreateObject ("adodb.connection")
connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("/mini.mdb") & ";Persist Security Info=False"
conn.Open connect

I need the second one to query a different database...anyone any ideas?

Thanks for any help.
Richard

<select  onChange="window.location='http://www..com/mane.asp?CardDescription=' + this.options[this.selectedIndex].value">
<OPTION VALUE = art>&nbsp;art Option>
<OPTION>-----------------------------------------------------</Option>
<%        
    Set oRs=Server.CreateObject("adodb.recordset")
    strSQL = "SELECT DISTINCT CardDescription FROM tblCards Where CategoryID <> " & 60 & " and CategoryID<>" & 61 & " ORDER BY Description"
'strSQL = "SELECT DISTINCT CardDescription FROM tblCards Where CategoryID<>" & 61 & "  ORDER BY CardDescription"
 
    oRs.Open strSQL, conn        
 
    Do while not oRs.EOF
        if Request.Form("GreetingPostCards") = oRs("CardDescription") then 'if this is the selected one then display as selected
            Response.Write "<OPTION VALUE = '" & oRS ("CardDescription") & "' SELECTED>"
            Response.Write oRs("CardDescription") & "</Option>"
            oRs.MoveNext 
        else
            Response.Write "<OPTION VALUE = '" & oRs ("CardDescription") & "'>&nbsp;"
            Response.Write oRs("CardDescription") & ",</Option>"
            oRs.MoveNext 
        end if
    loop        
    %>
</SELECT>
</div>
  </form>

It's not clear when you are wanting to query the other database. You can create a connection to a seperate database like this

Dim oRs, conn, connect, strSQL, conn2, connect2
 
'first connection
set conn=server.CreateObject ("adodb.connection")
connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("/mini.mdb") & ";Persist Security Info=False"
conn.Open connect
 
'second connection
set conn2=server.CreateObject ("adodb.connection")
connect2 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("/otherdb.mdb") & ";Persist Security Info=False"
conn2.Open connect2
 
 
'query connection 2 using another recordset (if first recordset still open, otherwise just use oRS
Dim oRs2
Set oRs2=Server.CreateObject("adodb.recordset")
 
strSQL="blah blah blah"
oRs2.Open strSQL, conn2
 
 

[danp129]

Just thought I would say I worked this one out.

Putting this at the beginning of each drop down and directing them to the different databases did the trick

        set conn=server.CreateObject ("adodb.connection")
connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &_
      Server.MapPath("/the two databases.mdb") & ";Persist Security Info=False"
conn.Open connect

Thanks
Richard

Yes you can (and should) do it that way if you know you don't need the first connection anymore sometimes you will.