#1
  1. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2003
    Posts
    1,003
    Rep Power
    32

    Connect to SQL Server via ASP


    I've been all over the web and this forum and can't seem to figure out how to do this. I've got many examples and with the many cuts and pastes that I've put together this is what I've got and I still can't get something to work right. First let me start off with a valid connection string for Microsoft Access to connect to my server:
    Code:
    ODBC;DRIVER=SQL SERVER;SERVER=WINSVP123\WINSQL01;WSID=NTCPCA393;DATABASE=DB_DIABC;TRUSTED_CONNECTION=YES
    Now I'm trying to put some code together to test if I can get a connection or not and then after I can successfully do that I want to put code together to query some tables and return info. I figure if I can get connected then I can let Google do the work on how to select and return data. With that said, here's my connection information and no matter what I try I can't seem to get connected. Is it my connection string? Is it my ASP code syntax? Please help! Thanks in advance!
    Code:
    set conn = CreateObject("ADODB.Connection") 
    DSN="Provider=SQLOLEDB;Data Source=WINSVP123\WINSQL01;Initial Catalog=db_diabc;UserId=useridgoeshere;Password=passwordgoeshere;Connect Timeout=10;Trusted_Connection=yes;"
    on error resume next 
    conn.open "remote connection string" 
    if err.number <> 0 then 
        response.write "unable to connect, trying other" 
        conn.open "alternate connection string" 
    end if 
    on error goto 0
  2. #2
  3. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,428
    Rep Power
    4539
    You appear to be connecting using named pipes, which may not be a valid connection from your web server. I'd try using a tcp/ip connection, and also while testing take out the error trapping in your code, it will your debugging by showing any errors. For example, in your current code any errors caused by trying the alternate connection scring will be hidden.
    ======
    Doug G
    ======
    The man who doesn't read good books has no advantage over the man who can't read them.
    --Mark Twain
  4. #3
  5. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2003
    Posts
    1,003
    Rep Power
    32
    Okay so I looked up connectionstrings.com and found a string for ip connections (I hope that's what you're referring to) and I'm assuming it means I use this
    Code:
    DSN="Data Source=167.6.150.113;Network Library=DBMSSOCN;Initial Catalog=db_diabc;User ID=useridgoeshere;Password=passwordgoeshere;"
    instead of this
    Code:
    DSN="Provider=SQLOLEDB;Data Source=WINSVP123\WINSQL01;Initial Catalog=db_diabc;UserId=useridgoeshere;Password=passwordgoeshere;Connect Timeout=10;Trusted_Connection=yes;"
    Also, what are "error trapping"s? How can I rewrite this to make it show when there's a connection?
  6. #4
  7. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,428
    Rep Power
    4539
    Error trapping is "on error resume next". Take a look here http://blogs.msdn.com/b/ericlippert/...-part-one.aspx or locate the vbscript documentation pages in msdn.

    It seems to me the ado connection object has a property that tells you if the connection is open or not. Somewhere in msdn there is complete documentation of all the ado objects, properties, methods and events.
    ======
    Doug G
    ======
    The man who doesn't read good books has no advantage over the man who can't read them.
    --Mark Twain
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    4
    Rep Power
    0
    I don't have server explorer in VS2010 express. I have database explorer. I have followed steps 1 to 3. But in the 4rth step. I don't get any text box. I get a dialog box which contains a Datasource listbox, Data provider combo box. The datasource listbox has

    1. Acess database file, 2. sql server compact 3.5 , 3. sql server database file

    while the combobox changes according to the content of the listbox

IMN logo majestic logo threadwatch logo seochat tools logo