Professional Web Applications Themes

Changing DBs in one connection - ASP Database

Hi All I've done an ASP which connects to an SQL DB. My problem is that I want to distribute this ASP page, but I can't do a straight DSN=MyDSN to point to the DSN, because I need to play with the Master DB first (to get a list of dbs) and then when this db choice is posted I use the actual selected DB. At present, this means I have to specify the whole DSN(less) string (eg, server name, username, password, database) so that I can use master and then the db. Is there a way of specifying a ...

  1. #1

    Default Changing DBs in one connection

    Hi All

    I've done an ASP which connects to an SQL DB.

    My problem is that I want to distribute this ASP page, but I can't do a
    straight DSN=MyDSN to point to the DSN, because I need to play with the
    Master DB first (to get a list of dbs) and then when this db choice is
    posted I use the actual selected DB. At present, this means I have to
    specify the whole DSN(less) string (eg, server name, username, password,
    database) so that I can use master and then the db.

    Is there a way of specifying a straight DSN, which I know that they will
    have set up and then change the DB as I do my SQL-ie bit?

    Many thanks

    Rgds

    Laphan


    Laphan Guest

  2. #2

    Default Re: Changing DBs in one connection

    Why can't you run a query like this:

    set rs = conn.execute("SELECT * FROM database1.dbo.table_name")
    ....
    set rs = conn.execute("SELECT * FROM database2.dbo.table_name")

    Your connection string does not need to be restricted to a specific DB. You
    can use three-part naming to access any database, as long as the user has
    access to the specified database.

    BTW, for a list of DBs, you can call this from any database, without having
    to connect directly to master:

    SELECT CATALOG_NAME FROM INFORMATION_SCHEMA.SCHEMATA

    Also, don't use a DSN!!!! [url]http://www.aspfaq.com/2126[/url]






    "Laphan" <newsFrozenMoles.co.uk> wrote in message
    news:bjiknk$7pm$8hercules.btinternet.com...
    > Hi All
    >
    > I've done an ASP which connects to an SQL DB.
    >
    > My problem is that I want to distribute this ASP page, but I can't do a
    > straight DSN=MyDSN to point to the DSN, because I need to play with the
    > Master DB first (to get a list of dbs) and then when this db choice is
    > posted I use the actual selected DB. At present, this means I have to
    > specify the whole DSN(less) string (eg, server name, username, password,
    > database) so that I can use master and then the db.
    >
    > Is there a way of specifying a straight DSN, which I know that they will
    > have set up and then change the DB as I do my SQL-ie bit?
    >
    > Many thanks
    >
    > Rgds
    >
    > Laphan
    >
    >

    Aaron Bertrand - MVP Guest

  3. #3

    Default Re: Changing DBs in one connection

    You can issue a USE statement within a connection context and then start
    issuing your queries:

    USE myDB1;
    -- This goes against myTable in myDB1
    SELECT * FROM myTable;

    USE myDB2;
    -- This goes against myTable in myDB2
    SELECT * FROM myTable;


    --
    Manohar Kamath
    Editor, .netBooks
    [url]www.dotnetbooks.com[/url]


    "Laphan" <newsFrozenMoles.co.uk> wrote in message
    news:bjiknk$7pm$8hercules.btinternet.com...
    > Hi All
    >
    > I've done an ASP which connects to an SQL DB.
    >
    > My problem is that I want to distribute this ASP page, but I can't do a
    > straight DSN=MyDSN to point to the DSN, because I need to play with the
    > Master DB first (to get a list of dbs) and then when this db choice is
    > posted I use the actual selected DB. At present, this means I have to
    > specify the whole DSN(less) string (eg, server name, username, password,
    > database) so that I can use master and then the db.
    >
    > Is there a way of specifying a straight DSN, which I know that they will
    > have set up and then change the DB as I do my SQL-ie bit?
    >
    > Many thanks
    >
    > Rgds
    >
    > Laphan
    >
    >

    Manohar Kamath [MVP] Guest

Similar Threads

  1. Net connection without Internet connection whoas
    By calmchess333 in forum Macromedia Flash Flashcom
    Replies: 1
    Last Post: June 11th, 12:29 AM
  2. Changing text without changing frames.
    By Jose Olivas in forum Macromedia Flash
    Replies: 1
    Last Post: March 21st, 11:36 AM
  3. Replies: 0
    Last Post: March 5th, 09:31 PM
  4. One NFS connection fine, one NFS connection slow
    By Robert E A Harvey in forum Linux / Unix Administration
    Replies: 5
    Last Post: May 28th, 07:28 AM
  5. Have internet connection but not workgroup connection.
    By Steve Winograd [MVP] in forum Windows Networking
    Replies: 2
    Last Post: July 18th, 10:01 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139