Hi. I'm trying to pull information from two databases on the same server and
use that information on my web form. Not sure how to reference two separate
databases. Any suggestions would be fantastic! Thanks.

            Dim _Ds As New DataSet
'Here's my problem below. I'm trying to pull information from two databases
on the same server. One database is referenced by strConnEmp in the web
config file and one is referenced by strConnNewProd. The "strConnEmp &
strConnNewProd" piece below isn't working.
            Dim _SqlConnection As New SqlConnection(strConnEmp &
strConnNewProd)
            Dim _SqlCommand As New SqlCommand(_SqlAccounts, _SqlConnection)
            Dim _SqlDataAdapter As New SqlDataAdapter(_SqlCommand)
            _SqlConnection.Open()
            _SqlDataAdapter.Fill(_Ds)
            dtgCustomers.DataSource = _Ds
            dtgCustomers.DataBind()

Mike C wrote:
There was no way for you to know it, but this is a classic asp newsgroup.
ASP.Net is a different technology from classic ASP.
While you may be lucky enough to find a dotnet-savvy person  here who can
answer your question, you can eliminate the luck factor by posting your
question to a newsgroup where the dotnet-savvy people hang out. I suggest
microsoft.public.dotnet.framework.aspnet.

You do not need two connection objects. A database object can be referenced
by a three-part name:
dbname.dbowner.objectname
You could (should) make this completely transparent by using stored
procedures. You will get further assistance in the dotnet groups.

Great idea. Thanks, Bob.

Show quoteHide quote

I used a Function :

Function myRecSet(sqlStr, cnStr)
' myRecSet: returns recordset or null if recordset not found
' parms:
' sqlStr: sqlStr
' cnStr : connection string
'
Dim myCn, myRs

Set myCn = CreateObject("ADODB.Connection")
myCn.Open cnStr

If Err.Number <> 0 Then
  Session("err") = "Connection failed - do not put this in production
obviously - " & cnStr
  Server.Transfer(myError.asp)    ' myError.asp will print out
Session("err")
End

Set myRs = CreateObject("ADODB.Recordset")

myRs.CursorLocation = 3   ' ad User Client - you could add a parameter
to this function to pass in to change the cursor
myRs.Open strSQL 1,3,1   ' adOpenKeyset,  adLockOptimistic, adCmdText -
again you could parameterize this into your function


If Err.Number <> 0 Then
  Session("err") = "Recordset failed - do not put this in production
  Server.Transfer(myError.asp)    ' myError.asp will print out
Session("err")
End

Set myRs.ActiveConnection = Nothing
Set myRecSet = myRs
Set myRS = Nothing
myCn.close
Set myCn = Nothing
End Function

So if I want to call this function, I would do something like this
using the PUBS database:

Dim strSQL , rs1, rs2, CnStr

CnStr = Session("myConnectionString")  ' see global.asa or retrieve
from database or write inline ?

strSQL = "Select * From [pubs].[dbo].[Authors]"
set rs1 = myRecSet(strSQL,CnStr)

strSQL = "Select * From [pubs].[dbo].[TitleAuthors]"
set rs2 = myRecSet(strSQL, CnStr)

If Not(rs1.EOF) Then
   Do Until(rs1.EOF)
        ' do some stuff here if you want. . .
        If Not(rs2.EOF) Then
           Do Until(rs2.EOF)
                rs2.MoveNext
                ' get your stuff on here. . .
           Loop
           set rs2 = nothing
        End If

        rs1.MoveNext
   Loop
   set rs1 = nothing
End If

ETM wrote:
For what? What are you talking about? how is this relevant to this thread?
The OP is using ASP.Net. Why are you posting irrelevant vbscript code? What
does your function have to do with the OP's problem of accessing data from
multiple databases?


OK, so maybe you inadvertantly posted your reply to the wrong thread. By
failing to quote anything from the post to which you are replying, you have
left us no way of figuring out which thread you actually intended to reply
to.

And finally ....
Why are you advocatiing using inefficient cursors for data maintenance
instead of sql statements?

Bob Barrows [MVP] wrote:
<snip>
Oh! Ignore this one. I see now that you didn't. Your "do stuff" left that
possibility open, but you didn't come right out and say that the recordset
should be used for data maintenance.

Not working?????
Help with code
Weird error when creating a .CSV file with VBScript on IIS6.
Passing data via XMLHTTPRequest... How do YOU do it?
ASP.NET 2.0 Login Control not working on Apple Mac OS X Version 10