DNS-less Connections

Discussion in 'ASP General' started by slc@cfl.rr.com, Feb 19, 2005.

  1. Guest

    Help,

    I'm trying to make the following asp code work using a DNS-Less
    connection on a windows 2000 server running IIS and ODBC 4.0 driver.
    The Access database (odbc_exmp.mdb) was made using Access 2002. The
    code works if I set up a DNS on the server under the ODBC drivers.
    What I'm I doing wrong and what do I change the code to, to make it
    work using DNS-Less connection?

    Thanks for any help
    Kevin.


    <% Response.buffer = true %>
    <html>
    <head>
    <title>Testing of odbc_exmp asp</title>
    </head>
    <body>
    <%
    'Next 4 lines do not work
    Dim rs, dbPath
    dbPath = "c:\safety\tr\odbc_exmp.mdb"
    Set rs = Server.CreateObject("ADODB.Connection")
    rs.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbPath

    ' The next 3 lines work with DNS set up on the windows 2000
    server-IIS-ODBC 4.0 drivers
    'Dim rs
    'Set rs = Server.CreateObject ("ADODB.Recordset")
    'rs.Open "names", "DSN=odbc_exmp",,, &H0200

    While Not rs.EOF
    'Response.Write "ID : " & rs("id") & "<br>"
    Response.Write "First Name : " & rs("first_name") & "<br>"
    Response.Write "Last Name : " & rs("last_name") & "<br>"
    Response.Write "<br>"
    Response.Write "<br>"
    rs.MoveNext
    Wend

    rs.Close
    Set rs = Nothing %>
    </body>
    </html>
     
    , Feb 19, 2005
    #1
    1. Advertising

  2. Veign Guest

    > Dim rs, dbPath
    > dbPath = "c:\safety\tr\odbc_exmp.mdb"
    > Set rs = Server.CreateObject("ADODB.Connection")


    You have the variable named RS but have instantiated a Connection object.
    Try changing it to an ADODB.Recordset

    > rs.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbPath


    You need some sort of SQL statement of something to let it know what to pull
    from the database.

    '---------------------------------------Code Sample:
    'Create the Connection string
    Dim strConnStr, dbPath
    dbPath = "c:\safety\tr\odbc_exmp.mdb"
    strConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath

    'Create the SQL Statement
    Dim strSQL
    strSQL = "SELECT id, first_name, last_name FROM MyTable

    'Open the Recordset
    Dim RS
    Set RS = Server.CreateObject("ADODB.Recordset")
    RS.Open strSQL, strConnStr

    'Loop through the records
    With RS
    If Not .EOF Then
    Do Until .EOF
    Response.Write "ID : " & RS("id").Value & "<br />"
    .MoveNext
    Loop
    End If
    End With

    'Cleanup
    RS.Close
    Set RS = Nothing
    '-------------------------End Code sample


    **- Make sure you change MyTable to the actual table name in your DB
    ** - On the line 'RS.Open strSQL, strConnStr' you should include the
    constants for a Forward Only, Read only recordset (firehose recordset) as
    this is the most efficient kind in this type of use

    --
    Chris Hanscom - Microsoft MVP (VB)
    Veign's Resource Center
    http://www.veign.com/vrc_main.asp
    --

    <> wrote in message
    news:...
    > Help,
    >
    > I'm trying to make the following asp code work using a DNS-Less
    > connection on a windows 2000 server running IIS and ODBC 4.0 driver.
    > The Access database (odbc_exmp.mdb) was made using Access 2002. The
    > code works if I set up a DNS on the server under the ODBC drivers.
    > What I'm I doing wrong and what do I change the code to, to make it
    > work using DNS-Less connection?
    >
    > Thanks for any help
    > Kevin.
    >
    >
    > <% Response.buffer = true %>
    > <html>
    > <head>
    > <title>Testing of odbc_exmp asp</title>
    > </head>
    > <body>
    > <%
    > 'Next 4 lines do not work
    > Dim rs, dbPath
    > dbPath = "c:\safety\tr\odbc_exmp.mdb"
    > Set rs = Server.CreateObject("ADODB.Connection")
    > rs.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbPath
    >
    > ' The next 3 lines work with DNS set up on the windows 2000
    > server-IIS-ODBC 4.0 drivers
    > 'Dim rs
    > 'Set rs = Server.CreateObject ("ADODB.Recordset")
    > 'rs.Open "names", "DSN=odbc_exmp",,, &H0200
    >
    > While Not rs.EOF
    > 'Response.Write "ID : " & rs("id") & "<br>"
    > Response.Write "First Name : " & rs("first_name") & "<br>"
    > Response.Write "Last Name : " & rs("last_name") & "<br>"
    > Response.Write "<br>"
    > Response.Write "<br>"
    > rs.MoveNext
    > Wend
    >
    > rs.Close
    > Set rs = Nothing %>
    > </body>
    > </html>
    >
     
    Veign, Feb 19, 2005
    #2
    1. Advertising

  3. wrote:
    > Help,
    >
    > I'm trying to make the following asp code work using a DNS-Less
    > connection on a windows 2000 server running IIS and ODBC 4.0 driver.
    > The Access database (odbc_exmp.mdb) was made using Access 2002. The
    > code works if I set up a DNS on the server under the ODBC drivers.
    > What I'm I doing wrong and what do I change the code to, to make it
    > work using DNS-Less connection?
    >
    > Thanks for any help
    > Kevin.
    >
    >
    > <% Response.buffer = true %>
    > <html>
    > <head>
    > <title>Testing of odbc_exmp asp</title>
    > </head>
    > <body>
    > <%
    > 'Next 4 lines do not work


    What does "doesn't work" mean? Error messages? Incorrect behavior? Please
    try to describe your symptoms without using the words "doesn't work", or "no
    luck", or some other phrase that assumes your readers are able to read your
    mind. :)

    > Dim rs, dbPath
    > dbPath = "c:\safety\tr\odbc_exmp.mdb"
    > Set rs = Server.CreateObject("ADODB.Connection")
    > rs.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbPath


    "rs" for a connection object??? Do you want it make it impossible for
    someone to debug your code?? Join the rest of the world and use "cn" or
    "conn" for the name of your connection object variable.
    >
    > ' The next 3 lines work with DNS set up on the windows 2000
    > server-IIS-ODBC 4.0 drivers
    > 'Dim rs
    > 'Set rs = Server.CreateObject ("ADODB.Recordset")
    > 'rs.Open "names", "DSN=odbc_exmp",,, &H0200


    You've just redefined the rs variable. It's now a recordset object. Was that
    intentional? Anyways, it's a bad idea to use a connection string in your
    recordset's Open statement. You may be disabling connection pooling by doing
    so. Always use an explicit connection object.

    It's also not a good idea to open your entire names table when your intent
    is to display two of the fields in it. Do this instead:

    Dim rs, dbPath, cn, sSQL
    dbPath = "c:\safety\tr\odbc_exmp.mdb"
    sSQL= "Select [id],first_name,last_name From [names]"
    Set cn = Server.CreateObject("ADODB.Connection")
    cn.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbPath
    Set rs = cn.Execute(sSQL,,1)


    For a potentially major performance improvement, use a GetRows array:

    dim arData
    If Not rs.EOF then arData = rs.GetRows
    rs.close: set rs=nothing
    cn.close: set cn=nothing
    if isArray(arData) then
    dim i
    for i = 0 to ubound(arData,2)
    Response.Write "ID : " & arData(0,i) & "<br>"
    Response.Write "First Name : " & arData(1,i) & "<br>"
    Response.Write "Last Name : " & arData(2,i) & "<br>"
    Response.Write "<br>"
    Response.Write "<br>"
    next
    else
    response.write "No Records were returned"
    end if


    HTH,
    Bob Barrows
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Feb 19, 2005
    #3
  4. Guest

    Chris,

    Thank You for the sample code.
    I fix the line that says strSQL = "SELECT id, first_name, last_name
    FROM MyTable
    to read
    strSQL = "SELECT id, first_name, last_name FROM [MyTable]"
    and it works grate.

    (Boy, was my code mested up)

    Again Thanks for the Help.

    Kevin
     
    , Feb 20, 2005
    #4
  5. Guest

    Bob,

    Thanks for the Help, your code works grate.
    I will try to do beater need time on describing my symptoms.
    Again Thanks for the help and setting me strate.

    Kevin
     
    , Feb 20, 2005
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. hilz
    Replies:
    0
    Views:
    668
  2. jiajia wu
    Replies:
    0
    Views:
    357
    jiajia wu
    Oct 1, 2009
  3. Big Dave

    DNS Less Connection String with ASP

    Big Dave, Feb 6, 2005, in forum: ASP General
    Replies:
    1
    Views:
    167
    Bob Barrows [MVP]
    Feb 6, 2005
  4. Sam Roberts
    Replies:
    0
    Views:
    212
    Sam Roberts
    Mar 20, 2005
  5. gavino
    Replies:
    2
    Views:
    168
    Ted Zlatanov
    Jul 27, 2006
Loading...

Share This Page