Connect ASP page to SQL Table

**giasone777** · July 16th, 2012, 04:30 PM

I am completely new to ASP and I am trying to make an SQL connection from a html page to a SQL 2008 database

database name is Exchange

table name is Report

Here is what I have so far but I don't get anyting when I click on the the query.html file

<!DOCTYPE html>
<html>
<body>

<h1>MS Exchange Report</h1>

<p>Testing Phase</p>

<%

Dim cn
Dim rs
Dim sql

Set rs=Conn.Execute(sql)

Set cn = Server.CreateObject("ADODB.Recordset")
"PROVIDER=SQLOLEDB;DATA SOURCE=192.168.10.10;DATABASE=Exchange;USER ID=administrator;PASSWORD=passwprd;"

[Code]
Set rs = Server.CreateObject("ADODB.Recordset")
sql = "SELECT * FROM Report"

rs.Open sql, cn

While Not rs.EOF
Response.Write(rs.Fields("user")
Response.Write(rs.Fields("quantity")

rs.MoveNext
Loop
%>

</body>
</html>
[Code]

Any help would be appreciated.

Thank-you!

**gk53** · July 17th, 2012, 07:38 AM

first at all it must be asp not html file and it should be something like that...

Code:

<html>
<body>

<h1>MS Exchange Report</h1>

<p>Testing Phase</p>

<%

Dim cn
Dim rs
Dim sql


Set cn = Server.CreateObject("ADODB.Recordset")
cn.open "PROVIDER=SQLOLEDB;DATA SOURCE=192.168.10.10;DATABASE=Exchange;USER ID=administrator;PASSWORD=passwprd;"


Set rs = Server.CreateObject("ADODB.Recordset")
sql = "SELECT * FROM Report"

rs.Open sql, cn

While Not rs.EOF
Response.Write rs("user")
Response.Write rs("quantity")

rs.MoveNext
Loop

rs.close
cn.close
set rs=nothing
set cn=nothing
%>

</body>
</html>

**giasone777** · July 17th, 2012, 08:23 AM

Do I save it as a .asp file and if that is the case how do I call it from the html.

Thanks.

**gk53** · July 17th, 2012, 09:16 AM

Why you need to call from html? Save as test.asp and call
http://yourDomainName/test.asp

**giasone777** · July 17th, 2012, 10:15 AM

When I save it as you suggested but I called it query2.asp

I have IIS pointing to the folder f:\web and the file iside that folder i enter into the web browser on the same machine

http://localhost/query2.asp

I get page not found - If I rename it to query2.html

It does display the html section of the file.

Cleary ISS is finding it but the asp code is not working for some reason.

Any ideas?

Thanks again.

**gk53** · July 17th, 2012, 10:39 AM

check you iis settings by default is it not processes asp pages only html is enabled by default

**giasone777** · July 17th, 2012, 04:58 PM

You were right it was disabled but it still doesn't work after enabling it.

http://localhost/query.asp

- cannot display web page.

When I change the extension to query.html

It at least shows the html section/title of the page.

Any more ideas?

Thanks.

**giasone777** · July 17th, 2012, 05:01 PM

Here is the exact error

This error (HTTP 500 Internal Server Error)

**gk53** · July 18th, 2012, 07:04 AM

you should uncheck "show friendly HTTP error messages" checkbox in IE click tools/options advanced tab uncheck and apply. After that you should see actual error and line number and not generic message

**giasone777** · July 18th, 2012, 09:53 AM

Originally Posted by gk53

you should uncheck "show friendly HTTP error messages" checkbox in IE click tools/options advanced tab uncheck and apply. After that you should see actual error and line number and not generic message

Ok now wer're getting somewhere - I get

ADODB.Recordset error '800a0e7d'

The connection cannot be used to perform this operation. It is either closed or invalid in this context.

/query.asp, line 10

**gk53** · July 18th, 2012, 10:00 AM

which line is #10?
Check you connection string...
I guess, you can not open connection
and publish code you using

**giasone777** · July 18th, 2012, 11:33 AM

Here is line 10

cn.open "PROVIDER=SQLOLEDB;DATA SOURCE=192.168.224.79;DATABASE=Exchange;USER ID=user;PASSWORD=password;"

Thanks again.

**gk53** · July 18th, 2012, 12:25 PM

try that connection string

Provider=SQLOLEDB;Data Source=192.168.224.79;Initial Catalog=Exchange;User ID=user;Password=password;

It should be initial catalog not database

**giasone777** · July 18th, 2012, 12:40 PM

Thanks I will try it.

**giasone777** · July 18th, 2012, 12:50 PM

Now it says

Microsoft VBScript compilation error '800a0401'

Expected end of statement

/query.asp, line 10

Provider=SQLOLEDB;Data Source=192.168.6.79;Initial Catalog=Exchange;User ID=password;Password=password;
-----------------^

Threads:	525,378
Posts:	976,962
Members:	219,992

Developer Shed Network

ASP Free Login

Menu

Thread: Connect ASP page to SQL Table