Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP 3 Classic ASP Active Server Pages 3.0 > Classic ASP Databases
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old October 1st, 2004, 10:17 AM
Authorized User
 
Join Date: Jan 2004
Location: , , .
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default Connecting to SQL 2000 Database - New at this

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

Reply With Quote
  #2 (permalink)  
Old October 1st, 2004, 10:21 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #3 (permalink)  
Old October 1st, 2004, 10:41 AM
Authorized User
 
Join Date: Jan 2004
Location: , , .
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


Reply With Quote
  #4 (permalink)  
Old October 1st, 2004, 10:51 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #5 (permalink)  
Old October 1st, 2004, 11:03 AM
Authorized User
 
Join Date: Jan 2004
Location: , , .
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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



Reply With Quote
  #6 (permalink)  
Old October 1st, 2004, 11:08 AM
Authorized User
 
Join Date: Jan 2004
Location: , , .
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


Reply With Quote
  #7 (permalink)  
Old October 1st, 2004, 11:08 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #8 (permalink)  
Old October 1st, 2004, 11:12 AM
Authorized User
 
Join Date: Sep 2004
Location: , , United Kingdom.
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #9 (permalink)  
Old October 1st, 2004, 11:26 AM
Authorized User
 
Join Date: Jan 2004
Location: , , .
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #10 (permalink)  
Old October 1st, 2004, 11:28 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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

_________________________
- Vijay G
Strive for Perfection
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Connecting to SQL 2000 Belisarius C# 0 February 17th, 2006 04:07 PM
connecting to sql 2000 using visual c# 2005 ejedspence C# 2005 0 June 17th, 2005 03:01 PM
I need help connecting to SQL Server 2000 aehb C# 6 April 27th, 2005 03:00 PM
Issues Connecting to an Access 2000 Database... Jonny ADO.NET 1 February 11th, 2004 09:54 AM
Connecting to an Access 2000 database Jonny .NET Web Services 0 November 10th, 2003 03:13 PM



All times are GMT -4. The time now is 12:34 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.