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
| 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 August 17th, 2006, 10:08 AM
Authorized User
 
Join Date: Aug 2006
Location: , NC, USA.
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default Wacky ASP Connections to SQL Server-PROBLEM SOLVED

I have inherited an ASP/VBscript website with SQL dbs.

I have 2 connection documents in DW MX, and I have to use both of them to make my database connections work - highly unusual and just strange. I can display query results/recordsets/etc. using connection #1 below, and then I have search through the .asp page, and change the name of the connection page #1 (see coding below) and references to connection page #1 to the name of connection page #2. This works but, kind of cumbersome. Suggestions? Also - if I just use one or the other of these 2 pages, then I get all kinds of errors which I have not been able to resolve - "SQL Server does not exist or access denied." OR ConnectOpen (Connect()). I am really new with SQL and asp - and VBScript - so any advice would be great. Thanks in advance.

1.)DatabaseWEB is the connection from my local computer.
It recognizes the db tables (on the server). However, it does not display dynamic data.
<%
' FileName="Connection_odbc_conn_dsn.htm"
' Type="ADO"
' DesigntimeType="ADO"
' HTTP="false"
' Catalog=""
' Schema=""
Dim MM_DATABASEweb_STRING
MM_DATABASEweb_STRING = "dsn=DATABASEWEB100;"
%>

2.) DBwebsite is the custom connection string to the server. It connects and displays but apparently cannot access the db tables (on the server).

<%
' FileName="Connection_ado_conn_string.htm"
' Type="ADO"
' DesigntimeType="ADO"
' HTTP="true"
' Catalog=""
' Schema=""
Dim MM_DBwebsite_STRING
MM_DBwebsite_STRING = "Driver={SQL Server};Server=(LOCAL);Database=DBweb;UID=DBuser;P WD=password"
%>

BuddyZ
Reply With Quote
  #2 (permalink)  
Old August 17th, 2006, 10:30 AM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

You have the server pointing to local, is the SQL server really running on the same machine as IIS?? Also, you don't have a default catalog defined for the user DBWeb. The SQL connection strings I use look something like this:

user ID=[user];password=[password];data source=[server];persist security info=True;initial catalog=[defaultDBase]

You will want to add the Drive field to the begining of the string above (I dont use the driver property because the driver is handled by the .NET classes).

hth

"The one language all programmers understand is profanity."
Reply With Quote
  #3 (permalink)  
Old August 17th, 2006, 10:42 AM
Authorized User
 
Join Date: Aug 2006
Location: , NC, USA.
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hey DAve,

OK - i tried your suggestion and got the same errors: "SQL Server does not exist or access denied." "ConnectOpen (Connect()). "

YES- our network admin configured server so it has both SQL server and IIS running on the same machine. Is that a problem do you think or just different?

Thanks.

BuddyZ

Reply With Quote
  #4 (permalink)  
Old August 17th, 2006, 10:53 AM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

Normally its not a good idea to run your SQL Server on the same machine as your IIS Server.

As far as the error goes, instead of using (local) use the machine name, e.g. SQLSvr01.

Also, the database that you set as the initial catalog, make sure that the user DBWeb has rights to access that database (Go in through Enterprise manager, to verify that).

hth.

"The one language all programmers understand is profanity."
Reply With Quote
  #5 (permalink)  
Old August 17th, 2006, 11:08 AM
Friend of Wrox
Points: 2,101, Level: 18
Points: 2,101, Level: 18 Points: 2,101, Level: 18 Points: 2,101, Level: 18
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: , , .
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

Hi buddyz,

How do you know that in case 1. you can connect to the database? Are you getting data back to the page? I don't understand what you mean by "It recognizes the db tables (on the server). However, it does not display dynamic data. "

This works for me:

"Provider=SQLOLEDB.1;UID=[sql login];Password=[password];Database=[database name];"

Have you tried connecting to the database on your machine with Query Analyzer or Enterprise Manager?
You might try connecting with the sa account just to check your connection and then try using your various users. Also, sql logins must have permission to that database and have rights as 'datareader' for the entire database.

Just some suggestions.

Thanks,
Richard


Reply With Quote
  #6 (permalink)  
Old August 17th, 2006, 12:50 PM
Authorized User
 
Join Date: Aug 2006
Location: , NC, USA.
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Richard,

To answer your question - With my connection 1. I know I am connecting to the database, because I can select that connection in Dreamweaver MX and query the database - I see and can select the tables and data fields I want, and then I create the recordset (query) and insert the data into a dynamic table on the .asp page.

However, when I go to preview the page, I get errors: Internal Server Error. Testing in various browsers, with Firefox I get the "helpful" message: Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
TMPa8ib745l93.asp, line 8

FYI, Line 8 is:
Recordset1.ActiveConnection = MM_dbNAMEweb_STRING

QUESTION: In your suggested connection string - you said it worked for you - where you have "Provider=SQLOLEDB.1;
  -- should I copy that exactly or put in something else? What is the provider? What does that term designate?

Anyway - thanks for the suggestions, appreciate it - still haven't gotten it to work the way it is supposed to - must be missing something. . .

BuddyZ
Reply With Quote
  #7 (permalink)  
Old August 17th, 2006, 01:35 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

In Richards case SQLOLEDB is the SQL OLEDB driver.

BTW, what does your DSN look like.

"The one language all programmers understand is profanity."
Reply With Quote
  #8 (permalink)  
Old August 17th, 2006, 01:56 PM
Authorized User
 
Join Date: Aug 2006
Location: , NC, USA.
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

OK, DSN info:
Microsoft SQL Server ODBC Driver Version 03.85.1117

Data Source Name: DBweb
Data Source Description: DATABASE
Server: SERVERNAME100
Database: DBweb
Language: (Default)
Translate Character Data: Yes
Log Long Running Queries: No
Log Driver Statistics: No
Use Integrated Security: Yes
Use Regional Settings: No
Prepared Statements Option: Drop temporary procedures on disconnect
Use Failover Server: No
Use ANSI Quoted Identifiers: Yes
Use ANSI Null, Paddings and Warnings: Yes
Data Encryption: No

The User Name and password are confusing to me; do I use --
1.) My own Windows login username and password - I checked the server & it says Win authentication and login
2.) SQL Server Public UserName and password
OR
3.) SQL Server Master Database Owner UserName and password - I think they don't want us to use this one because of security reasons.

It is either 1 or 2? I have tried both and neither work. Frustrating.
Thanks.

BuddyZ
Reply With Quote
  #9 (permalink)  
Old August 17th, 2006, 02:01 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

It depends on how your SQL Server is set up; my connection strings always use a user that has been created inside of the SQL Server itself (SQL Server Authentication) in the event that you are using Windows Authentication the user is going to be IWAM\[machinename] (unless you are using impersonation of some sort) and that user will need to have rights to access the 1)The server 2)The necessary database's.



"The one language all programmers understand is profanity."
Reply With Quote
  #10 (permalink)  
Old August 17th, 2006, 04:13 PM
Friend of Wrox
Points: 2,101, Level: 18
Points: 2,101, Level: 18 Points: 2,101, Level: 18 Points: 2,101, Level: 18
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: , , .
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

Don't worry. It will get fixed. I had a terrible amount of trouble when I was just getting started connecting to the database. Just how long it will take....

Thanks for answering those questions I had. I just had never seen a driver specified as { SQL Server } and thought that may be the problem.

QUESTION: In your suggested connection string - you said it worked for you - where you have "Provider=SQLOLEDB.1;
  -- should I copy that exactly or put in something else? What is the provider? What does that term designate?

I think what I would do is make sure I could connect to the SQL server and the database using the SQL logins particularly starting with the sa account using Enterprise Manager and then Query Analyzer. Then I would know for sure that I could connect remotely to the database. After I could do that then I would start working from the app. I keep all of my database connections in the global.asa file. There's a few reasons to do this. One being that when I'm working on my development database, all I have to do to upload to production is just move the pages. I don't have to change any connection strings on any of the pages. I don't know Dream Weaver which it looks like that's what you're using. So please excuse me if some of this doesn't sound quite right.

Here's an example of what's in the global.asa file and an example of how I access that on the individual pages themselves.
In global.asa:
NOTE: I don't specify the server name in the connection string.
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Application_OnStart
Application("conn") = "Provider=SQLOLEDB.1;Persist Security Info=False;DSN=[DSNName];UID=appuser;Password=[password];Database=[Database Name];"
End Sub
</SCRIPT>

On the page:
<%
    Set oConn = Server.CreateObject("ADODB.Connection")
    oConn.Open Application("conn")
%>

Also, make sure you test the DSN when you create it.



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
Connections in a Sql Server project mlw C# 2 September 15th, 2008 09:32 AM
Access-SQL Server Connections Romashka Access 3 May 25th, 2007 06:26 AM
Server to Server XML Problem (ASP.NET to ASP) Enwood XML 7 April 26th, 2006 03:22 AM
problem 2 connecting SQL Server 200 using ASP.NET nagen111 ADO.NET 5 February 16th, 2005 01:26 AM



All times are GMT -4. The time now is 09:47 PM.


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