...

View Full Version : connect to MS SQL DB from Classic ASP script



crmpicco
05-08-2006, 04:16 PM
I have created a MS SQL DB called 'PiccoCeraci'.
It is running on my local machine.

I have this script, called MSSQL.asp:



<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

crmpicco
05-08-2006, 05:27 PM
<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



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum