How can u connect to 1 of several dbs based on selection user makes when accessing main page

[hourman]

We have a web site IIS 5 (on Win2000k) with Oracle 9i backend (Sun unix).

There is 1 user ID to oracle(APT_W3) that is used by all users (50).

We have a global.asa file that has one APP Start connection string using OLE
DB:
Application("dbConnString")="Provider=MSDAORA.1;Pa ssword='SDERSD';User
ID=APT_W3;Data Source=CCEWSDD1_new;Locale Identifier=1033;OLE DB services=-1"

The first page that a user sees asks that they select the area they want to
see (western, northern, etc) from a dropdown menu. Once they select it and
hit save they can go to other pages, session varibles indicating what area
they selected are then passed to pages. Currently all areas are in one
database.

What we want to do is have areas in separate dbs, a northern one, a western
one.etc. Each will have separate user ids (APT_W2N, APT_W3W,etc).

We want the users to have the web page presented to them where they select an
area. Based on the area they select, the appropriate conneciton string will
be used (the one in the global asa).

Is there a way to have that in the global asa? Maybe as a session
Session_OnStart? Based on the selection they made on the web page, they will
be using one of several connection strings so we can have connection pooling

[Ray at]

Don't multipost. See asp.db group.

Ray at work

"hourman" <sdfdfwetudfyt@7fdfster.com> wrote in message
news:3f1c28a7$0$96923$45beb828@newscene.com...

> We have a web site IIS 5 (on Win2000k) with Oracle 9i backend (Sun unix).
>
> There is 1 user ID to oracle(APT_W3) that is used by all users (50).
>
> We have a global.asa file that has one APP Start connection string using

OLE

> DB:
> Application("dbConnString")="Provider=MSDAORA.1;Pa ssword='SDERSD';User
> ID=APT_W3;Data Source=CCEWSDD1_new;Locale Identifier=1033;OLE DB

services=-1"

>
> The first page that a user sees asks that they select the area they want

to

> see (western, northern, etc) from a dropdown menu. Once they select it

and

> hit save they can go to other pages, session varibles indicating what area
> they selected are then passed to pages. Currently all areas are in one
> database.
>
> What we want to do is have areas in separate dbs, a northern one, a

western

> one.etc. Each will have separate user ids (APT_W2N, APT_W3W,etc).
>
> We want the users to have the web page presented to them where they select

an

> area. Based on the area they select, the appropriate conneciton string

will

> be used (the one in the global asa).
>
> Is there a way to have that in the global asa? Maybe as a session
> Session_OnStart? Based on the selection they made on the web page, they

will

> be using one of several connection strings so we can have connection

pooling

[hourman]

In article <OdtdAI7TDHA.2520@tk2msftngp13.phx.gbl>, "Ray at <%=sLocation%>" <ask@me.forit> wrote:

>Don't multipost. See asp.db group.
>
>Ray at work
>
>"hourman" <sdfdfwetudfyt@7fdfster.com> wrote in message
>news:3f1c28a7$0$96923$45beb828@newscene.com...

>> We have a web site IIS 5 (on Win2000k) with Oracle 9i backend (Sun unix).
>>
>> There is 1 user ID to oracle(APT_W3) that is used by all users (50).
>>
>> We have a global.asa file that has one APP Start connection string using

>OLE

>> DB:
>> Application("dbConnString")="Provider=MSDAORA.1;Pa ssword='SDERSD';User
>> ID=APT_W3;Data Source=CCEWSDD1_new;Locale Identifier=1033;OLE DB

>services=-1"

>>

sorry its my 1syt post, did not know which group was best suited for question,
since it involved db, but was also asp (global asa) related.

[Tim Williams]

Just store 4 connection strings in the application and then use the
appropriate one for each user based on their region selection.
Application("dbConnString_N")="Provider....
Application("dbConnString_S")="Provider....
Application("dbConnString_E")="Provider....
Application("dbConnString_W")="Provider....


Or store the appropriate connection string in each user's session.

session("dbConnString")=Application("dbConnString_ " &
request.form("regionId")) ' or whatever



tim


"hourman" <sdfdfwetudfyt@7fdfster.com> wrote in message
news:3f1c2823$0$96923$45beb828@newscene.com...

>
> We have a web site IIS 5 (on Win2000k) with Oracle 9i backend (Sun unix).
>
> There is 1 user ID to oracle(APT_W3) that is used by all users (50).
>
> We have a global.asa file that has one APP Start connection string using

OLE

> DB:
> Application("dbConnString")="Provider=MSDAORA.1;Pa ssword='SDERSD';User
> ID=APT_W3;Data Source=CCEWSDD1_new;Locale Identifier=1033;OLE DB

services=-1"

>
> The first page that a user sees asks that they select the area they want

to

> see (western, northern, etc) from a dropdown menu. Once they select it

and

> hit save they can go to other pages, session varibles indicating what area
> they selected are then passed to pages. Currently all areas are in one
> database.
>
> What we want to do is have areas in separate dbs, a northern one, a
> western one.etc. Each will have separate user ids (APT_W2N, APT_W3W,etc).
>
> We want the users to have the web page presented to them where they
> select an area. Based on the area they select, the appropriate conneciton
> string will be used (the one in the global asa).
>
> Is there a way to have that in the global asa? Maybe as a session
> Session_OnStart? Based on the selection they made on the web page, they

will

> be using one of several connection strings so we can have connection

pooling

[hourman]

In article <emYFKm7TDHA.3640@tk2msftngp13.phx.gbl>, "Tim Williams" <saxifrax@pacbellremovethis.net> wrote:

>Just store 4 connection strings in the application and then use the
>appropriate one for each user based on their region selection.
>Application("dbConnString_N")="Provider....
>Application("dbConnString_S")="Provider....
>Application("dbConnString_E")="Provider....
>Application("dbConnString_W")="Provider....
>
>
>Or store the appropriate connection string in each user's session.
>
>session("dbConnString")=Application("dbConnString _" &
>request.form("regionId")) ' or whatever
>
>
>tim

Thanks a followup question: We have
var adoRst = Server.CreateObject("ADODB.Recordset")
adoRst.Open(strSQL, Application("dbConnString")) appearing ~130 times in our
web apps, we ar trying to min. editing the pages. If I understand you

using:

>Application("dbConnString_N")="Provider....
>Application("dbConnString_S")="Provider....

we would have to edit all our asp pages to change Application("dbConnString"))
to the approiapte Application("dbConnString_?")) based on what they selected-
using if statements, may replace adoRst.Open(strSQL,
Application("dbConnString") with an include file that had the if statments,
right?

with

>Or store the appropriate connection string in each user's session.
>
>session("dbConnString")=Application("dbConnString _" &

we would also have to do the same replacing the one we have with an include
file?

Is there any way to pass it back to the global.asa page? so that it resets the
sesssion startup function to use the appropriate one maybe an if statment in
the asa. page

[hourman]

In article <9kjohv84bbqvk8hku8q5832eg7f2a58anc@4ax.com>, See Message body to address if provided... wrote:

>
>There is no need for 4 separate databases ( do not confuse Oracle's
> architecture with SqlServer's ) ..
>One database, one connection, different used_ids , and , for each User_Id grant
> access not to the whole table but to
>just a view of the data that only contains their area.
>
>For instance, when the user_id is APT_W2N, then the view would only contain
> Northern data...
>
>

The data, for a variety of reasons (mostly regulatory per the legal dept.) is
going to be in different databases, its not our decision to make. there are
going to be separate databases with data from each region going into its
separate place. the DBA's are going to give us 4 user ids and if we want to
look at the data from a region we are going to have to use that id.

We have a single web app currently that looked at the data. There was a
single user APT_W3 and a single database. When a user got to the home page
they had to pick a region. we then passed the a session variable containing
the region. This was used in SQL statements in the where clause to have that
query only see the region that was initally selected.

Now we have 4 user id, user still will have to select a region to go to (code
will determine if they get an error message or not based on their lan id) and
then they will get db access. We don't want to create 4 web sites one for
each region, we want to have single site. We want to make the pages determine
with user id to use based on the selection made at start.

My question was if it was possible when user selects region to thave asp
connect with the approrpiate user id.

[Mark Schupp]

1. when the user picks a region, set the connection string for that region
into a session variable. use the connection string from that session
variable for all subsequent database access.

or

2. just put the region in the session variable and have an include file that
contains the code that opens the database. put a select statement in the
include file to pick the appropriate connection string.

or (if you don't like session variables)

3. pass the region along from page to page in hidden form fields or in the
query string. continue as in (2).

--
Mark Schupp
--
Head of Development
Integrity eLearning
Online Learning Solutions Provider
[email]mschupp@ielearning.com[/email]
[url]http://www.ielearning.com[/url]
714.637.9480 x17


"hourman" <sdfdfwetudfyt@7fdfster.com> wrote in message
news:3f1c5bb2$0$96923$45beb828@newscene.com...

> In article <9kjohv84bbqvk8hku8q5832eg7f2a58anc@4ax.com>, See Message body

to address if provided... wrote:

> >
> >There is no need for 4 separate databases ( do not confuse Oracle's
> > architecture with SqlServer's ) ..
> >One database, one connection, different used_ids , and , for each User_Id

grant

> > access not to the whole table but to
> >just a view of the data that only contains their area.
> >
> >For instance, when the user_id is APT_W2N, then the view would only

contain

> > Northern data...
> >
> >

> The data, for a variety of reasons (mostly regulatory per the legal dept.)

is

> going to be in different databases, its not our decision to make. there

are

> going to be separate databases with data from each region going into its
> separate place. the DBA's are going to give us 4 user ids and if we want

to

> look at the data from a region we are going to have to use that id.
>
> We have a single web app currently that looked at the data. There was a
> single user APT_W3 and a single database. When a user got to the home

page

> they had to pick a region. we then passed the a session variable

containing

> the region. This was used in SQL statements in the where clause to have

that

> query only see the region that was initally selected.
>
> Now we have 4 user id, user still will have to select a region to go to

(code

> will determine if they get an error message or not based on their lan id)

and

> then they will get db access. We don't want to create 4 web sites one

for

> each region, we want to have single site. We want to make the pages

determine

> with user id to use based on the selection made at start.
>
> My question was if it was possible when user selects region to thave asp
> connect with the approrpiate user id.

[Phillip Windell]

Shouldn't that be at the Session Level and not Application? The
Application Level effects the whole entire site, not just the one
user. If one user wants Western and another comes later and picks
Southern, then the "Western user" suddenly gets switched to Southern.

--

Phillip Windell [CCNA, MVP, MCP]
[email]pwindell@wandtv.com[/email]
WAND-TV (ABC Affiliate)
[url]www.wandtv.com[/url]


"John Beschler" <giles@geewhiz.com> wrote in message
news:0e2001c35074$e0f13b40$a601280a@phx.gbl...

> If you store multiple connection strings as session
> variables, then after the user selects the region, replace
> the "true" connection string with the connection string
> for that region:
>
> Select Case Region
> Case Region "N"
> Application("dbConnString") = Application
> ("dbConnString_N")
> Case Region "S"
> Application("dbConnString") = Application
> ("dbConnString_S")
> Case Region "E"
> Application("dbConnString") = Application
> ("dbConnString_E")
> Case Region "W"
> Application("dbConnString") = Application
> ("dbConnString_W")
> End Select
>
>
>
>

> >-----Original Message-----
> >In article <emYFKm7TDHA.3640@tk2msftngp13.phx.gbl>, "Tim

> Williams" <saxifrax@pacbellremovethis.net> wrote:

> >>Just store 4 connection strings in the application and

> then use the

> >>appropriate one for each user based on their region

> selection.

> >>Application("dbConnString_N")="Provider....
> >>Application("dbConnString_S")="Provider....
> >>Application("dbConnString_E")="Provider....
> >>Application("dbConnString_W")="Provider....
> >>
> >>
> >>Or store the appropriate connection string in each

> user's session.

> >>
> >>session("dbConnString")=Application("dbConnStrin g_" &
> >>request.form("regionId")) ' or whatever
> >>
> >>
> >>tim

> >
> >Thanks a followup question: We have
> >var adoRst = Server.CreateObject("ADODB.Recordset")
> >adoRst.Open(strSQL, Application("dbConnString"))

> appearing ~130 times in our

> >web apps, we ar trying to min. editing the pages. If I

> understand you

> >
> >using:

> >>Application("dbConnString_N")="Provider....
> >>Application("dbConnString_S")="Provider....

> >
> >we would have to edit all our asp pages to change

> Application("dbConnString"))

> >to the approiapte Application("dbConnString_?")) based on

> what they selected-

> >using if statements, may replace adoRst.Open(strSQL,
> >Application("dbConnString") with an include file that had

> the if statments,

> >right?
> >
> >with

> >>Or store the appropriate connection string in each

> user's session.

> >>
> >>session("dbConnString")=Application("dbConnStrin g_" &

> >
> >we would also have to do the same replacing the one we

> have with an include

> >file?
> >
> >Is there any way to pass it back to the global.asa page?

> so that it resets the

> >sesssion startup function to use the appropriate one

> maybe an if statment in

> >the asa. page
> >.
> >