Re: Query two databases with asp

Hey, thanks for the time and effort you've put in to help me Bob. This code
will definitely help me.

"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..GetUsersBySchoolorParish (
@School nvarchar(255)
,@Parish nvarchar(255)) AS
SET NOCOUNT ON
IF @School > '' AND @Parish > ''
SELECT UserID, firstname, lastname,UserAddress,UserCity,CompanyName
FROM SchoolsUsers
WHERE Parish = @Parish AND School = @School
ELSE
BEGIN
IF @School > ''
SELECT UserID, firstname, lastname,UserAddress,
UserCity,CompanyName
FROM SchoolsUsers
WHERE School = @School
ELSE
SELECT UserID, firstname, lastname,UserAddress,
UserCity,CompanyName
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("school") ' or Querystring
parish= Request.Form("parish") ' or Querystring
set cn=createobject("adodb.connection")
cn.open "provider=sqloledb;data source=sqlservername;" & _
"user id = xxxxx;password=xxxxx;initial catalog=DB2"
set rs=createobject("adodb.recordset")
cn.GetUsersBySchoolorParish 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(,,"</td><td>","</td></tr><tr><td>")
sHTML = left(sHTML,len(sHTML) - 8) & "</table>"
response.write sHTML
else
response.write "no users returned"
end if
%>


dusty wrote:
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:

dusty wrote:
DB1..School
ID - INT(data type) - 4 (length) - PKey
Parish - nvarchar - 255
School - nvarchar - 255

DB2..Users
ID - numeric(data type) - 9 (length)

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.

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.


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.com/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.

--
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.



.

Relevant Pages

  • Re: Child Abuse
    ... Catholics, here is a contribution I made to a specifically Catholic forum ... parish or diocese. ... should become priests for. ... and him alone to be chairman of the school committee or board of governors? ...
    (soc.culture.irish)
  • Re: Query two databases with asp
    ... accepts a school and/or a parish returns all the user fields whose names ... ,@Parish nvarchar) AS ... LastName - varchar - 50 ... Please reply to the newsgroup. ...
    (microsoft.public.inetserver.asp.db)
  • Re: Can anyone please decifer?
    ... Further to my posts last night and other replies, I cannot help wondering if the school has been correctly identified. ... The rector holds an ecclesiastical court on the Tuesday preceding Holy Thursday. ... The parish church, dedicated to St. Deiniol, is an ancient and a spacious structure, with a square embattled tower. ... The schoolroom was built in 1608, at the west corner of the churchyard, and was rebuilt and enlarged, and a house erected for the master, in 1814, at an expense of £900, by the feoffees, from small savings accumulated at compound interest, and by subscription among the inhabitants: a piece of ground was given to the school by Sir Stephen R. Glynne and Rector Crewe. ...
    (soc.genealogy.britain)
  • Re: Ms Tam disappeared on December 15 while on a pilgrimage with a religious group to a mountain in
    ... 2006 -- The Diocese of Brooklyn announced yesterday that a financially struggling Queens parish elementary school would not reopen next year — becoming the first of what officials expect to be a handful of schools to close. ... With enrollment at 212, Queen of Peace School in Flushing "cannot continue to operate with so few students," said the Rev. James Tighe, the parish pastor. ...
    (soc.culture.china)
  • Re: A necklace I am proud of :)
    ... I found your newsgroup, ... And btw, schooling in Germany was free for me, as ... Goldschmiedeschule in Pforzheim (where I went to school), ... superior in craftsmanship than anything produced by goldsmiths in this ...
    (rec.crafts.jewelry)
Loading