Connecting to SQL 2000 Database

Tee88 · October 1st, 2004, 10:17 AM

Can someone please tell me how to connect to a SQL database on the network? SQl and IIS are not on the same server. Here is what I am using to open the connection below, but it is not working Please help.

<%
    Set Conn = Server.CreateObject("ADODB.Connection")
    Set rs = Server.CreateObject("ADODB.Recordset")
    Conn.Open "DSN=ProdMarket;UID=sa;pwd=;"
        SQL = "SELECT Account, PhoneNumber FROM Test01"

    rs.LockType = 3
    rs.CursorLocation = 3
    rs.CursorType = 3

rs.Open SQL,Conn
%>

Thanks,
Tee

happygv · October 1st, 2004, 10:21 AM

Hey Tee,

Check this Connection strings, to see if you are using the right connection string that fits your need.

Also it would be better if you could explain what you mean by "it is not working". May be you can post if any errors that you face.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

Tee88 · October 1st, 2004, 10:41 AM

thanks for the post.

I am trying to insert information into a SQl 2000 database. When I open the file in IE it gives me the HTTP 500 - Internal server error message that does not help at all. Below are the two files I have. I created this simple form to test the database connection.

Testconn.asp code is below

<html>
<head>
<title>Insert info into Dbase</title>
</head>
<body>

<table>

<form method="get" action="insertASP.asp">
    <tr><td>Account:</td><td><input type="text" name="acct"></td></tr><br>
    <tr><td>Phone Number:</td><td><input type="text" name="phone"></td></tr><br>
    <br>
<tr><td><input type=Submit value="Insert Info"></td>
<td><input type=reset value="Reset"></td></tr>

</table>
</form>
</body>
</html>

InsertASP.asp (opens database connection, inserts data, and closes database connection)

<html>
<head>
<title>See the database</title>
</head>
<body>
<table align=center width=600 border="2">

<%
    Set Conn = Server.CreateObject("ADODB.Connection")
    Set rs = Server.CreateObject("ADODB.Recordset")
    Conn.Open "DSN=HRdbase;UID=;pwd=;"
        SQL = "SELECT Account, PhoneNumber FROM Test01"
rs.Open SQL,Conn
%>

<%
rs.AddNew
rs("Account")=Request("acct")
rs("PhoneNumber") = Request("phone")

rs.MoveFirst
DO WHILE NOT rs.EOF
%>

<tr>
<td width=300><%=rs("Account")%></td>
<td><%=rs("PhoneNumber")%></td>
</tr>

<%
rs.MoveNext
Loop%>

<%
rs.Close
Conn.Close
%>

</table>
</body>
</html>

Thanks for looking at this Tee

happygv · October 1st, 2004, 10:51 AM

Try this

Quote:

quote:In Internet Explorer, open the Advanced tab of the Options dialog, and uncheck "Show friendly HTTP error messages". For your Web site in IIS, open the Properties dialog, switch to the Home Directory tab, and click the Configuration button. On the Debugging tab, make sure "Send detailed ASP error messages to the client" is checked.

Then run the page, and see if you get any error information.

And post the errors if any you see then.

Cheers!

_________________________
- Vijay G
Strive for Perfection

Tee88 · October 1st, 2004, 11:03 AM

Here is the error message. thanks you are GREAT!!!

Microsoft OLE DB Provider for SQL Server error '80040e09'
SELECT permission denied on object 'Test01', database 'DropShip_HRD', owner 'dbo'.

/chrd/db/include/opendb.asp, line 12

Tee88 · October 1st, 2004, 11:08 AM

Here is the other error message. I am trying to connect to two different databases.

Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

/chrd/db/include/opendb.asp, line 7

happygv · October 1st, 2004, 11:08 AM

Now use your sql query analyser, connect using sa account and run the following.

Code:

Use DropShip_HRD
go

GRANT Select,Insert,Update,Delete on dbo.Test01 to public
go

If that doesn't help, try this.

Code:

GRANT Select,Insert,Update,Delete on dbo.Test01 to sa
go

Hope that solves your problem.

I would also suggest you to create a new user on sql server and use that for such things, rather than using SA account there. Also set a passowrd for SA, and never use that set with blank password.
Cheers!

_________________________
- Vijay G
Strive for Perfection

dalewright22 · October 1st, 2004, 11:12 AM

you need to set the permissions for person who you are giving access to the file to. (I_USER account.
Go into Enterprise Manager. Find the database you are using, then users. Check to see if the user is listed as a user of the db. If not Right click the user, select Props, Permissions button and give the user the permissions you need.

Also i assume that you have created a system DSN ??

Cheers

DJWright

Tee88 · October 1st, 2004, 11:26 AM

Yep, I tried that and I have access. I will try to rename the DSN and see if that works.

happygv · October 1st, 2004, 11:28 AM

what is on line number /chrd/db/include/opendb.asp, line 7 ?

_________________________
- Vijay G
Strive for Perfection