I'm trying to create an asp page that will allow users to generate

search results by querying mssql databases.

I inherited these databases and did not build them. Here's the

scenario.

I have two databases: DB1 and DB2. School is a table of DB1 that

consists of the fields:

ID, Parish, School, Principal, address, city, zip, etc.

Users is a table of DB2 that consists of the following fields:

ID, firstname, lastname, address, city, CompanyName, etc.

Field CompanyName (DB2\Users) is linked to ID (DB1\School). Users

would like to have the capability of finding out which users are in a

particular parish or school.

I don't know how to produce the correct asp code that will pull

information from two different databases.

dusty wrote:

I'm not sure where you are having the problem:

- constructing the sql statement to join the tables?

- executing the sql statement and retrieving the results into a

recordset?

- displaying the results to the users?

Where do you specifically need help?

--

Microsoft MVP -- ASP/ASP.NET

Please reply to the newsgroup. The email account listed in my From

header is my spam trap, so I don't check it very often. You will get a

quicker response by posting to the newsgroup.

I need help with all items you've mentioned.

Thanks so much.

"Bob Barrows [MVP]" wrote:

Are the two databases on different database servers?

dusty wrote:

--

Microsoft MVP -- ASP/ASP.NET

Please reply to the newsgroup. The email account listed in my From

header is my spam trap, so I don't check it very often. You will get a

quicker response by posting to the newsgroup.

I'm trying to create an asp page that will allow users to generate

search results by querying mssql databases.

I inherited these databases and did not build them. Here's the

scenario.

I have two databases: DB1 and DB2. School is a table of DB1 that

consists of the fields:

ID, Parish, School, Principal, address, city, zip, etc.

Users is a table of DB2 that consists of the following fields:

ID, firstname, lastname, address, city, CompanyName, etc.

dusty wrote:

What are the datatypes of these fields, please?

--

Microsoft MVP -- ASP/ASP.NET

Please reply to the newsgroup. The email account listed in my From

header is my spam trap, so I don't check it very often. You will get a

quicker response by posting to the newsgroup.

DB1\school table

int

nvarchar

DB2\Users

numeric

varchar

bit

int

numeric

datetime

"Bob Barrows [MVP]" wrote:

let's make sure I have this straight:

DB1..School

ID numeric (precision and scale please?)

All the rest of the fields are nvarchar? What are the defined lengths?

DB2..Users

ID numeric (precision and scale please?)

firstname varchar (length?)

lastname bit ???

address int ???

city numeric???

CompanyName datetime ???

Huh???

dusty wrote:

--

Microsoft MVP -- ASP/ASP.NET

Please reply to the newsgroup. The email account listed in my From

header is my spam trap, so I don't check it very often. You will get a

quicker response by posting to the newsgroup.

DB1..School

ID - INT(data type) - 4 (length) - PKey

Parish - nvarchar - 255

School - nvarchar - 255

DB2..Users

ID - numeric(data type) - 9 (length)

FirstName - varchar - 50

LastName - varchar - 50

Address - varchar - 50

City - varchar - 25

State - varchar - 10

Zip - varchar - 10

CompanyName - varchar - 50

Username - varchar - 50 - PKey

The CompanyName field in the users table under DB2 is the ID field of

DB1.

dusty wrote:

A numeric column (also known as a "decimal" column) has both a precision

(total number of digits allowed) and a scale (number of digits used for

the decimal portion). I can do without this information in this

particular situation, but you should make a point of providing it when

asked in the future.

That is not possible. An INT column cannot be used to link to a varchar

column ... unless you have a varchar column containing integer data ...

is that the case? I know you said you did not create the databases, but

this seems nonsensical to me. Anyways, if this is the case, it will make

your joins very inefficient due to the necessity of making datatype

conversions on the fly.

What version of SQL Server is this? Can you modify the design of

DB2..Users? Perhaps add a calculated field to it?

It is taking very long to gather the requirements for this task. Given

that I have my own job, I am not going to be able to devote a lot of

time to this. Perhaps you should consider hiring a consultant to do this

for you ...

Here is some information about what information needs to be provided to

enable people (including a consultant if you go that route) to help you

with database-related problems:

http://www.aspfaq.c­om/etiquette.asp?id=­5006

--

Microsoft MVP -- ASP/ASP.NET

Please reply to the newsgroup. The email account listed in my From

header is my spam trap, so I don't check it very often. You will get a

quicker response by posting to the newsgroup.

Thanks for explaining the precision and scale. I was a little unsure

about it, that's why I didn't reply to it.

The CompanyName field actually displays a numeric value that

corresponds to the ID field. I appreciate all of the information you

have provided and I'm sorry to have taken up much of your time. I

think I'll just put this project on hold for now.

Thanks again.

"Bob Barrows [MVP]" wrote:

Well, when you get back to it, here is a view and stored procedure that

accepts a school and/or a parish returns all the user fields whose names

you provided:

CREATE VIEW SchoolsUsers AS

SELECT s.Parish, s.School, s.Principal, s.address SchoolAddress, s.city

SchoolCity, s.zip SchoolZip,

u.ID UserID, firstname, lastname, u.address UserAddress, u.city

UserCity, u.CompanyName

FROM DB1..Schools s inner join DB2..Users u

ON s.ID = CAST(u.CompanyName AS INT)

go

CREATE PROCEDURE DB2..GetUsersByScho­olorParish (

@School nvarchar(255)

,@Parish nvarchar(255)) AS

SET NOCOUNT ON

IF @School > '' AND @Parish > ''

SELECT UserID, firstname, lastname,UserAddres­s,UserCity,CompanyNa­me

FROM SchoolsUsers

WHERE Parish = @Parish AND School = @School

ELSE

BEGIN

IF @School > ''

SELECT UserID, firstname, lastname,UserAddres­s,

UserCity,CompanyNam­e

FROM SchoolsUsers

WHERE School = @School

ELSE

SELECT UserID, firstname, lastname,UserAddres­s,

UserCity,CompanyNam­e

FROM SchoolsUsers

WHERE Parish = @Parish

END

To retrieve results from this in ASP, i would do this:

<%

dim cn, rs, school, parish, sHTML, fld

school = Request.Form("schoo­l") ' or Querystring

parish= Request.Form("paris­h") ' or Querystring

set cn=createobject("ad­odb.connection")

cn.open "provider=sqloledb;­data source=sqlservernam­e;" & _

"user id = xxxxx;password=xxxx­x;initial catalog=DB2"

set rs=createobject("ad­odb.recordset")

cn.GetUsersBySchool­orParish school, parish, rs

if not rs.eof then

sHTML = "<table><tr>"

for each fld in rs.fields

sHTML=sHTML & "<th>" & fld.name & "</th>"

next

sHTML=sHTML & "</tr><tr><td>"

sHTML = sHTML & _

rs.GetString(,,"</t­d><td>","</td></tr><­tr><td>")

sHTML = left(sHTML,len(sHTM­L) - 8) & "</table>"

response.write sHTML

else

response.write "no users returned"

end if

%>

dusty wrote:

--

Microsoft MVP -- ASP/ASP.NET

Please reply to the newsgroup. The email account listed in my From

header is my spam trap, so I don't check it very often. You will get a

quicker response by posting to the newsgroup.