Solved

Connect to Multiple Databases

Posted on 2004-04-06
3
212 Views
Last Modified: 2013-12-24
I have a variety of very large databases. One is a list of birds that can be found across North America. Another has a list of destinations around the continent. A final database is a list of sightings of birds (and other creatures).

Is there a way to be able to connect to more than one database?

Here is several scenarios.

Form One
On one form, I can access the bird species table (drop-down box with a DRW) to allow us to record a BirdID number for a species spotted on a particular outing.

On a DRW page, I need to be able to display the details of that sighting. Here's the problem. The sightings form only saved the BirdID to the sightings table. In order to be able to reconcile the BirdID with the birds common or latin name, I need to connect to the Bird Species database and build a query.

I don't mind doing some custom sql and I understand Union queries to link multiple tables, but how can I connect to queries from more than one database connection?
0
Comment
Question by:wcameron
  • 2
3 Comments
 
LVL 12

Accepted Solution

by:
rcmb earned 250 total points
ID: 10773369
Is BirdID common across all databases? If yes, then I would not use the DBRW but build VBScript DB connection to each database and then capture the data to variables and display in a table. For example:

You start with the drop-down box to select the species and it would pass the BirdID to the resultant ASP page

On the resultant page (start with a blank page and switch to HTML view)
<html>
<% varBirdID = request.form("BirdID") %>

<% ' This section gets the Bird Name from the passed BirdID %>
<%
' Enter your path to the database after MapPath
sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("fpdb/Database1.mdb") & ";Persist Security Info=False"
Set OBJdbConn = Server.CreateObject("ADODB.Connection")
OBJdbConn.Open  sConnStr
' Enter your specific table,column information here
SQLQuery = "SELECT * FROM Species WHERE BirdID = '" & varBirdID & "' ORDER BY BirdName ASC"

Set RsList1 = OBJdbConn.Execute(SQLQuery)  
%>

<% Do While Not RSList1.EOF %>
<%
' This assigns the bird name to a variable
varBirdName = RSList("BirdName")
%>

<%
   RSList1.MoveNext
Loop
RSList1.Close
Set RSList1 = Nothing
OBJdbConn.Close
Set OBJdbConn = Nothing
%>

<% ' This section gets the sighting information on the Bird Name %>
<%
' Enter your path to the database after MapPath
sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("fpdb/Database2.mdb") & ";Persist Security Info=False"
Set OBJdbConn = Server.CreateObject("ADODB.Connection")
OBJdbConn.Open  sConnStr
' Enter your specific table here
' You can enter a different SQL here based on Bird Name if desired
' For example SQLQuery = "SELECT * FROM Sightings WHERE BirdName = '" & varBirdName & "' ORDER BY SightDate ASC"
SQLQuery = "SELECT * FROM Sightings WHERE BirdID = '" & varBirdID & "' ORDER BY SightDate ASC"

Set RsList2 = OBJdbConn.Execute(SQLQuery)  
%>

<% ' Build your table to display the data %>
<table>
  <tr>
    <td>Bird Name</td>
    <td>Sighting</td>
    <td>Date</td>
  </tr>
<% Do While Not RSList2.EOF %>
  <tr>
    <td><%=RSList2("BirdName")%></td>
    <td><%=RSList2("Sighting")%></td>
    <td><%=RSList2("SightDate")%></td>
  </tr>
<%
   RSList2.MoveNext
Loop
RSList2.Close
Set RSList2 = Nothing
%>
</table>
<%
OBJdbConn.Close
Set OBJdbConn = Nothing
%>
</html>

I hope you get the general idea here. I have found frontpage to not work as well in this area as just straight code. Let me know if you need more info.

RCMB
0
 
LVL 3

Author Comment

by:wcameron
ID: 10775049
Thanks I think I get your idea. BirdID would be the common field across each database. This is due to the fact that scientists seem to take great pleasure in changing common and Latin names. Also, I find numerical autonumber IDs to be much more reliable and easy to deal with.

If I understand you correctly, I need to manually create a connection to my bird database to use the BirdID to get the name and then set it as a variable.

Second, create a connection to the second database and retrieve the data I need, replacing the BirdID with the CommonName.

Don't I need to create some kind of join between the two datasets? What if there is more than a single BirdID referenced?

0
 
LVL 12

Expert Comment

by:rcmb
ID: 10776178
If you have more than a single BirdID referenced -

Create a page (newpage1.asp) that list all of your Birds from your main database. Create a link to a resultant page like so:

newpage2.asp?BirdID=<% If Not IsEmpty(fp_rs) And Not (fp_rs Is Nothing) Then Response.Write CStr(fp_rs("BirdID")) %>

(Note this must be done inside the database regions)

The newpage2.asp will display all pertinent information on the Bird (e.g., common name, sighting location, sighting date, etc). Use the idea I gave you above to create the newpage2.asp.

By doing this users can select the bird they desire to get the additional information on and not have to much to search through to find it. You can get even more detailed by allowing them to search the database and have the results displayed.

-------------------------------------------------------------------

Thanks I think I get your idea. BirdID would be the common field across each database. This is due to the fact that scientists seem to take great pleasure in changing common and Latin names. Also, I find numerical autonumber IDs to be much more reliable and easy to deal with. --- I agree with this process and I only use numberical autonumber IDs to allow me to ensure I access the correct record.

If I understand you correctly, I need to manually create a connection to my bird database to use the BirdID to get the name and then set it as a variable.  --- You understand correctly. FrontPage does not allow you to pass a variable to a SQL Query unless it comes from a form or querystring. By using the manual connection you can create the variable on the fly and then have the second connection search on it all in the same page.

Second, create a connection to the second database and retrieve the data I need, replacing the BirdID with the CommonName.  --- Correct. You can even go to a third or fourth but it gets very confusing at that point. You would have to capture the variables for a later display in the page.

RCMB
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

In this short web based tutorial, I wanted to show users how they can still use the powers of FrontPage in conjunction with Expression Web 3.  Even though Microsoft eliminated the use of Web components, we can still use them with FrontPage and edit …
This article provides a case study on how our local youth baseball league deployed a new website, including the platform selection, implementation and benefits to the league.
The purpose of this video is to demonstrate how to exclude a particular blog category from the main blog page. This is can be used when a category already has its own tab, or you simply want certain types of posts not to show up on the main blog. …
The purpose of this video is to demonstrate how to integrate Mailchimp with WordPress, by placing a Mailchimp signup form on a WordPress Page or Post. This will be demonstrated using a Windows 8 PC. Mailchimp will be used. Log into your Mailchi…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now