Solved

Connect to Multiple Databases

Posted on 2004-04-06
3
220 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Turn A Profile Picture Into A Cartoon Using Photoshop And Illustrator This tutorial will teach you how to make a cartoon style image out of a regular picture. I have tried to keep the tutorial as simple as possible. I used Adobe CS4 for this tuto…
When setting up new project requests for our site, one of the most powerful tools our team has available to use is Axure (http://www.axure.com/). It’s a tool for creating software and web prototypes that can function and interact as if it were the a…
The purpose of this video is to demonstrate how to Test the speed of a WordPress Website. Site Speed is an important metric of a site’s health. Slow site speed can result in viewers leaving your site quickly and not seeing your content. This…
The purpose of this video is to demonstrate how to update a WordPress Site’s version. WordPress releases new versions of its software frequently and it is important to update frequently in order to keep your site secure, and to get new WordPress…

808 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