Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Senior Coder crmpicco's Avatar
    Join Date
    Jan 2005
    Location
    Mauchline, Scotland
    Posts
    1,096
    Thanks
    15
    Thanked 1 Time in 1 Post

    Cool connect to MS SQL DB from Classic ASP script

    I have created a MS SQL DB called 'PiccoCeraci'.
    It is running on my local machine.

    I have this script, called MSSQL.asp:

    Code:
    <html>
    <title>Queries from the MS-SQL database with ASP</title>
    <body bgcolor="FFFFFF">
    <h2>Query from table <b>products</b> with ASP</h2>
    
    <%
    
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.open "PROVIDER=SQLOLEDB;DATABASE=PiccoCeraci"
    
    'This code block will create a recordset
    Set rs = Server.CreateObject("ADODB.Recordset")
    SQL = "select * from products"
    rs.open SQL, conn
    
    'will iterate to display the records got from the database
    While Not rs.EOF
      response.write(rs("id") & " " & rs("price"))
      rs.MoveNext
    Wend
    
    'closes the connection
    rs.close
    conn.close
    Set rs = Nothing
    Set conn = Nothing
    
    %>
    </body>
    </html>
    When i run the script in IE7, i get this error:

    Microsoft OLE DB Provider for SQL Server error '80040e4d'

    Login failed for user 'Craig R Morton'.

    /picco/MSSQL.asp, line 9

    When i test the data source in the Administrative Tools>Data Sources (ODBC) in the Control Panel i get this:

    =====================================================
    Microsoft SQL Server ODBC Driver Version 03.85.1117

    Running connectivity tests...

    Attempting connection
    Connection established
    Verifying option settings
    Disconnecting from server

    TESTS COMPLETED SUCCESSFULLY!
    =====================================================

    However, it asks 'How should SQL Server verify the authenticity of the login ID? I have selected 'With Win NT authentication' as the connectivity tests fail with the other option 'login id and password will be entered by the user'......which i want?

    How can i set the u/name and p/word for this DB? and get it to run in my script?

    Thanks in advance.

    Picco

  • #2
    Senior Coder crmpicco's Avatar
    Join Date
    Jan 2005
    Location
    Mauchline, Scotland
    Posts
    1,096
    Thanks
    15
    Thanked 1 Time in 1 Post
    Code:
    <html>
    <title>Queries from the MS-SQL database with ASP</title>
    <body bgcolor="FFFFFF">
    <h2>Query from table <b>products</b> with ASP</h2>
    
    <%
    
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.open "PROVIDER=SQLOLEDB;UID=sa;PWD=test;DATABASE=PiccoCeraci"
    
    'This code block will create a recordset
    Set rs = Server.CreateObject("ADODB.Recordset")
    SQL = "select * from testTisch"
    rs.open SQL, conn
    
    'will iterate to display the records got from the database
    While Not rs.EOF
      response.write(rs("testColEin") & " " & rs("testColZwei"))
      rs.MoveNext
    Wend
    
    'closes the connection
    rs.close
    conn.close
    Set rs = Nothing
    Set conn = Nothing
    
    %>
    </body>
    </html>
    the working script


  •  

    Posting Permissions

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