Webmaster Forum / ASP / Database Access / March 2008

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