Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Connecting to sql server 2008 db

Status
Not open for further replies.
neronikita

neronikita

Technical User
Feb 20, 2002
159
US
Hello. I have used ASP before but I never set up my own database connection in it. Would someone please look at this and tell me why the page will not display... it works for the first section of asp, but when I add in the second part with the connection to the database. I am new to sql server 2008, but my DSN connection works when tested. I've also tried non-dsn connection and couldn't get that to work. Can anyone tell me what I'm doing wrong? I have replaced the pw and ID with generics, but nothing else has changed in this code.

Thanks

Di

CODE

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"<html>
<head>
<title>My First ASP Page</title>
</head>
<body bgcolor="white" text="black">
<%
'Dimension variables
Dim strMessage
'Place the value Hello World into the variable strMessage
strMessage = "Hello World"
'Write the contents of the variable strMessage to the web page
Response.Write (strMessage)
'Write line break into the web page
Response.Write ("<br>")

'Write the server time on the web page using the VBScript Time() function
Response.Write ("The time on the server is: " & Time())

'Close the server script
%>

<%
'declare the variables
Dim Connection
Dim Recordset
Dim SQL

'declare the SQL statement that will query the database
SQL = "SELECT * FROM emptype"

'create an instance of the ADO connection and recordset objects
Set Connection = Server.CreateObject("ADODB.Connection")
Set Recordset = Server.CreateObject("ADODB.Recordset")

'open the connection to the database
Connection.Open "DSN=ESI;UID=id;PWD=password;Database=ESI"

'Open the recordset object executing the SQL statement and return records
Recordset.Open SQL,Connection

'first of all determine whether there are any records
If Recordset.EOF Then
Response.Write("No records returned.")
Else
'if there are records then loop through the fields
Do While NOT Recordset.Eof
Response.write Recordset("ID")
Response.write Recordset("Description")
Response.write "<br>"
Recordset.MoveNext
Loop
End If

'close the connection and recordset objects to free up resources
Recordset.Close
Set Recordset=nothing
Connection.Close
Set Connection=nothing
%>
</body>
</html>
 
Sort by date Sort by votes
you may have to provide the driver in the connection, and you don't need to provide the dbname - the dsn takes care of that for you in the background:

Code: 
set conn = server.createObject("ADODB.Connection")
conn.connectionString = "[COLOR=red]Driver={SQL Server}[/color];DSN=myDSN;UID=myUID;PWD=myPASS;"
conn.open "DSN=myDSN","myUID","myPASS" 
...
conn.close
set conn = nothing

--------
GOOGLE is a great resource to find answers to questions like "how do i..."

If you don't know exaclty what you want to do or what to search on, try Google Suggest: --------
I have recently been semi-converted to ensuring all my code (well most of it) works in both javascript and non-javasc
 
Upvote 0 Downvote
Thanks. I tried putting that in and I'm still getting the HTTP 500 Internal Server error.

Any other ideas? I was worried I did something wrong in setting up my SQL Server (it's been years since I set up a new one, and I'm using 2008 which is totally new to me) but since the DSN test worked, I figured that must be ok.

Thanks,

Di
 
Upvote 0 Downvote
do you get a line number of exactly where the error is occuring? If so, could you provide the code on that line?

--------
GOOGLE is a great resource to find answers to questions like "how do i..."

If you don't know exaclty what you want to do or what to search on, try Google Suggest: --------
I have recently been semi-converted to ensuring all my code (well most of it) works in both javascript and non-javasc
 
Upvote 0 Downvote
That's what is odd. I'm not getting that at all, I'm just getting a generic page:

The website cannot display the page
HTTP 500
Most likely causes:
•The website is under maintenance.
•The website has a programming error.

What you can try:
Refresh the page.

Go back to the previous page.

More information


Thanks,

Di
 
Upvote 0 Downvote
Does it make any difference if everything is running off my computer? I'm still in development for the whole thing, so it's all on my PC.

It almost seems to me like it's just not seeing the db at all. is there any simple way to test what the problem is in ASP? Normally I would output the sql query to the screen to troubleshoot the query, but it's not even giving me that much.

Thanks,

Di
 
Upvote 0 Downvote
Hi,
As a test,just use your 'Hello' part to isolate that it is the SqlServer code that is causing the problem:
Test.asp
Code: 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
 "[URL unfurl="true"]http://www.w3.org/TR/html4/loose.dtd">[/URL]
<html>
<head>
<title>My First ASP Page</title>
</head>
<body bgcolor="white" text="black">
<%
'Dimension variables
Dim strMessage  
 'Place the value Hello World into the variable strMessage
strMessage = "Hello World"
'Write the contents of the variable strMessage to the web page 
Response.Write (strMessage)
'Write line break into the web page
Response.Write ("<br>")

 'Write the server time on the web page using the VBScript Time() function
Response.Write ("The time on the server is: " & Time())

'Close the server script
%> 

[/body]
[/html]

If that works, try adding back just the code to Open the recordset and them close it,to see if it is the connection effort that is failing:

ADD:

Code: 
<% 
'declare the variables 
Dim Connection
Dim Recordset
Dim SQL

'declare the SQL statement that will query the database
SQL = "SELECT * FROM emptype"

'create an instance of the ADO connection and recordset objects
Set Connection = Server.CreateObject("ADODB.Connection")
Set Recordset = Server.CreateObject("ADODB.Recordset")

'open the connection to the database
Connection.Open "DSN=ESI;UID=id;PWD=password;Database=ESI"

'Open the recordset object executing the SQL statement and return records 
Recordset.Open SQL,Connection
'close the connection and recordset objects to free up resources
Recordset.Close
Set Recordset=nothing
Connection.Close
Set Connection=nothing
%>

You may find that using a DSN-less connection is better:


or



[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
Upvote 0 Downvote
That still didn't work. I'm getting the same Page Cannot be Displayed error... I'll look at the two links you sent. I did try dsn-less before the dsn, but that didn't work either. I didn't look at those sites though - maybe the answer is there.

Thanks again,

Di
 
Upvote 0 Downvote
Hi,
When did that error happen, even on the first set ( the hello part) ?


If so, then the DB connection is not the issue.



[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
Upvote 0 Downvote
No, sorry, I wasn't clear about that. hello part works fine. It just happens when i add the second part with the connection.

Thanks,

Di
 
Upvote 0 Downvote
are you sure you're getting the error on the db connection and not on the recordset? I'm just throwing stuff out there, not sure if it would help or not:

try commenting out this line and see what happens:

Code: 
Recordset.Open SQL,Connection

--------
GOOGLE is a great resource to find answers to questions like "how do i..."

If you don't know exaclty what you want to do or what to search on, try Google Suggest: --------
I have recently been semi-converted to ensuring all my code (well most of it) works in both javascript and non-javasc
 
Upvote 0 Downvote
AHA! I figured out that I have to change some settings to get it to show me what my true errors are. It seems that it's not connecting to the db because of permissions, although I'm not sure what else to set them to. I'm using what I always use in the db and I'm getting in fine in sql server.

This is my error now:

Hello World
The time on the server is: 4:02:19 PM Microsoft OLE DB Provider for SQL Server error '80040e4d'

Login failed for user 'e092599'.

/dispatch/test.asp, line 34
 
Upvote 0 Downvote
Could this have anything to do with my sql server being set up for Windows Authentication?

Thanks,

Di
 
Upvote 0 Downvote
Do you think I should start over installing the server and do it another way? I'm not really far into the development, I could still do that. I only have data in a few tables.
 
Upvote 0 Downvote
The patches didn't work. It still will not connect via the ASP page.
Thanks,

Di
 
Upvote 0 Downvote
I tried changing the SQL Server from Windows Authenication to mixed authenication but it's still not connect. I've verified the ID and pw.

Here's a few more details:

IIS 7 and Sql Server 2008 are both on the same machine. I'm using classic ASP and the DSN works when tested. The machine I'm working on is 64-bit and running Vista Ultimate. I can't think of anything else that would affect it but I'm at my wits end. I can't go any farther until this is resolved so I'm at a standstill. Can anyone think of anything else to try?

Thanks,

Di
 
Upvote 0 Downvote
Ahhh, Vista!

Did you check your windows firewall settings to allow web access to SQL (Port 1433 I beleive)

--------
GOOGLE is a great resource to find answers to questions like "how do i..."

If you don't know exaclty what you want to do or what to search on, try Google Suggest: --------
I have recently been semi-converted to ensuring all my code (well most of it) works in both javascript and non-javasc
 
Upvote 0 Downvote
I'm using Norton Internet Security instead of Windows Firewall. I'm not quite sure how to check specific ports in this one. I'm looking now, but if you have any knowledge of it, that would be great.

Thanks,

Di
 
Upvote 0 Downvote
I tried turning the firewall off completely and i'm still getting login failed for my user id. I tried changing the connection information and authentication. I'm at a complete loss.

Thanks,

Di
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

leifoet
  • Locked
  • Question
How to send HTML emails with ASP classic?
Replies
0
Views
253
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
Return to current location on page after asp processes.
Replies
2
Views
204
penguinspeaks
penguinspeaks
bslintx2021
  • Locked
  • Question
How to configure IIS 10 server to allow Classic ASP to connect to MSSQL server database on a domain
Replies
1
Views
504
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
How to check if a datatable is empty ?
Replies
0
Views
194
leifoet
leifoet
leifoet
  • Locked
  • Question
Problem: sql-Select with request.form
2
Replies
22
Views
318
leifoet
leifoet

Part and Inventory Search

Sponsor

Back
Top