Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2002
    Posts
    8

    Unanswered: Connecting to DB over a network

    I have set up a database server but i do not know how to connect to the database on that servber via asp can some one help with a connction string?

    im new.

  2. #2
    Join Date
    Nov 2002
    Posts
    14

    Re: Connecting to DB over a network

    From ASP you can only use ADO. Here is a sample code:

    option explicit

    dim con
    dim rs

    set con = WScript.CreateObject( "ADODB.Connection" )
    con.open "PROVIDER=SQLOLEDB;SERVER=your_server;DATABASE=you r_db;UID=your_login;PWD=your_password"

    set rs = WScript.CreateObject( "ADODB.Recordset" )
    rs.open "your select statement", con, 0, 1

    do while not rs.EOF
    ' Process your row set
    rs.MoveNext
    loop

    rs.close
    con.close

    set rs = nothing
    set con = nothing
    Zlatko Michailov
    Z-SQL

  3. #3
    Join Date
    Nov 2002
    Posts
    8
    so the only difference between connecting to a local db and one over a network is this...
    SERVER=your_server;DATABASE=your_db;

    thanks a lot.

  4. #4
    Join Date
    Nov 2002
    Posts
    8

    OBJECT REQUIRED error

    the first time i used your code, i was connecting in the asp page. i then tried to connect at session_onstart and had some problems. when i tried to cut and paste back so that i can connect at the asp page again i began getting more problems. i am not sure what i did wrong. heres what i have and i am recieving an OBJECT REQUIRED error


    ---------------------
    dim cnnLogin
    dim rstLogin

    strSQL = "SELECT * FROM CityBase.dbo.Login " _
    & "WHERE Login='" & Replace(Request.Form("login"), "'", "''") & "' " _
    & "AND Pwd='" & Replace(Request.Form("password"), "'", "''") & "';"

    set cnnLogin = WScript.CreateObject( "ADODB.Connection" )
    cnnLogin.open "PROVIDER=SQLOLEDB;SERVER=CITYSERV1;DATABASE=CityB ase;UID=webuser;PWD=webaccess"


    'Set cnnLogin = Server.CreateObject("ADODB.Connection")
    'cnnLogin.Open "PROVIDER=SQLOLEDB;SERVER=CITYSERV1;DATABASE=CityB ase;UID=webuser;PWD=webaccess"

    Set rstLogin = cnnLogin.Execute(strSQL)

  5. #5
    Join Date
    Nov 2002
    Posts
    8

    sorry to bother you

    i seemed to have figured it out. however, i am not sure how. i think becuase i was connecting in each page they over lapped some how between the two pages.

    thanks for your help.

  6. #6
    Join Date
    Nov 2002
    Posts
    4

    Re: sorry to bother you

    Originally posted by CityInet.com
    i seemed to have figured it out. however, i am not sure how. i think becuase i was connecting in each page they over lapped some how between the two pages.

    thanks for your help.
    I think this is the easiest way.

    '---- CursorTypeEnum Values ----
    Const adOpenForwardOnly = 0
    Const adOpenKeyset = 1
    Const adOpenDynamic = 2
    Const adOpenStatic = 3

    '---- LockTypeEnum Values ----
    Const adLockReadOnly = 1
    Const adLockPessimistic = 2
    Const adLockOptimistic = 3
    Const adLockBatchOptimistic = 4

    '---- CursorLocationEnum Values ----
    Const adUseServer = 2
    Const adUseClient = 3

    Dim DataConn
    Dim rs

    Set DataConn = Server.CreateObject("ADODB.Connection")
    Set rs = Server.CreateObject( "ADODB.Recordset" )

    With DataConn
    .ConnectionString = "Provider=SQLOLEDB.1; Data Source=YOUR_SERVER; Persist Security Info=False; User ID=sa; Initial Catalog=YOUR_DATABASE"
    .ConnectionTimeout = 15
    .CommandTimeout = 30
    .CursorLocation = adUseClient
    .Open
    End With

    rs.Open "SELECT * FROM Table", DataConn, adOpenForwardOnly, adLockReadOnly

    hth!

  7. #7
    Join Date
    Nov 2002
    Posts
    8
    i think i like this way best, but what if i wanted to make my connection in session. as of now i am making it at each page. the application is only being made for less than 50 people to access so i think i would like to make my connection in session. how would i go about doing that?

    thanks a lot

  8. #8
    Join Date
    Nov 2002
    Posts
    4
    Hi Matt,

    Follow this:
    ======================
    Put this in your Global.asa:
    Sub Application_OnStart
    Application("DataConn_ConnectionString") = "Provider=SQLOLEDB.1; Data Source=YOUR_SERVER; Persist Security Info=False; User ID=sa; Initial Catalog=YOUR_DATABASE"
    '
    Application("DataConn_ConnectionTimeout") = 15
    Application("DataConn_CommandTimeout") = 30
    End Sub
    ======================
    Create some include file that do the connection, like this:
    <%
    '---- CursorTypeEnum Values ----
    Const adOpenForwardOnly = 0
    Const adOpenKeyset = 1
    Const adOpenDynamic = 2
    Const adOpenStatic = 3

    '---- LockTypeEnum Values ----
    Const adLockReadOnly = 1
    Const adLockPessimistic = 2
    Const adLockOptimistic = 3
    Const adLockBatchOptimistic = 4

    '---- CursorLocationEnum Values ----
    Const adUseServer = 2
    Const adUseClient = 3

    Dim DataConn

    Set DataConn = Server.CreateObject("ADODB.Connection")

    With DataConn
    .ConnectionString = Application("DataConn_ConnectionString")
    .ConnectionTimeout = Application("DataConn_ConnectionTimeout")
    .CommandTimeout = Application("DataConn_CommandTimeout")
    .CursorLocation = adUseClient
    .Open
    End With
    %>
    ======================
    Use it by this way:
    <%@ Language=VBScript %>
    <!--#include file="dataconn_page.inc"-->
    <%
    Dim rs

    Set rs = Server.CreateObject( "ADODB.Recordset" )

    rs.Open "SELECT * FROM Table", DataConn, adOpenForwardOnly, adLockReadOnly
    %>
    ======================

    hth!

    Antonio Carlos
    www.focoservices.com

  9. #9
    Join Date
    Feb 2002
    Posts
    2,232
    To use the constants for ado, just include adovbs.inc.

Posting Permissions

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