Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." 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 Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old August 7th, 2003, 09:04 AM
Authorized User
 
Join Date: Aug 2003
Location: , , .
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Default Switch from Access DB to SQL

I'm trying to switch from an Access database to an SQL version, and I can't quite figure out the connection for it. I have this code:

-Dim conn
-Set conn = Server.CreateObject("ADODB.Connection")
-conn.Open "Provider= SQLOLEDB, Datasource=local, Initial Catalog=Clearance"

But when I try and run it I get this message:

-ADODB.Connection error '800a0e7a'

-Provider cannot be found. It may not be properly installed.

-/connect.asp, line 4

Does this mean I have to configure the SQL Server for the SQLOLEDB still, or shouldn't it recognize it?

  #2 (permalink)  
Old August 7th, 2003, 09:23 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 141
Thanks: 0
Thanked 0 Times in 0 Posts
Default

This is what I use:

Code:
Provider=SQLOLEDB.1;User ID=<Login name here>;Password=<Password here>;Initial Catalog=<Database name here>;Data Source=<SQL Server name here>
Chris
  #3 (permalink)  
Old August 7th, 2003, 10:27 AM
Authorized User
 
Join Date: Aug 2003
Location: , , .
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I tried that, and it seems to work a little better, but now it says that the server can't be found or I don't have the permissions to access it. I have the user set to admin, so I have full access. Do I need to give it full direction to the server directory?

  #4 (permalink)  
Old August 7th, 2003, 11:14 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 141
Thanks: 0
Thanked 0 Times in 0 Posts
Default

If the user does not have permissions to the SQL database, then make sure the user (The one you have in the provider string) has permissions to the resources he is using (IE; table, view, and/or stored proc). SQL Permissions really have nothing to do with the folder structure. If you are just viewing data, then the user only needs to have permissions to select. If it is a stored proc, they need execute permissions on the proc.

Remember that deny permissions override allow permissions. There is a user called "public". Do not set deny or allow for this user if you only want your web user to access the table. Since all users are part of public, if you deny public, the user wont be allowed access even if you set him to allow.

If it is simply the web page that the user does not have permissions for, then give the user permissions to the folder the file is in.

Chris
  #5 (permalink)  
Old August 7th, 2003, 03:18 PM
Authorized User
 
Join Date: Aug 2003
Location: , , .
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Still not working, I'm almost positive the problem is in the connection code, is there any way I could just edit my code from Access to work with the SQL server?:
Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString="DRIVER={Microsoft Access Driver (*.mdb)};" & _
             "DBQ=C:\PROD\W0061115\VirtualDirectory\V1\pages\k3 etlab.mdb"
objConn.Open

Could I change the driver to an SQL compliant one, and the drive to the full path of the SQL database?

  #6 (permalink)  
Old August 7th, 2003, 03:20 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 141
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You cannot access a SQL database directly. You need to go through the SQL engine (hence the connect string I posted). SQL has the ability to split a database in to multiple files and this alone could cause you grief in the future.

Chris
  #7 (permalink)  
Old August 8th, 2003, 07:46 AM
Authorized User
 
Join Date: Aug 2003
Location: , , .
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I worked around with the connection string for a while, and now it seems to recognize the database, but it gives this error:

Microsoft OLE DB Provider for SQL Server error '80004005'

Login failed for user 'temp'. Reason: Not associated with a trusted SQL Server connection.

/connect.asp, line 4

The code for connect.asp is now:

<%
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB.1;User ID=temp;Password=temp;Initial Catalog=k3etlabSQL;Data Source=WPWBJ1V9"
%>

I added temp, and they have pretty much full control in the database.

  #8 (permalink)  
Old August 8th, 2003, 10:43 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 141
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I remember this problem and I can't remember what I did to fix it. I do remember having to doto enterprise manager, right click the SQL server name and goto properties. I remember having to switch (on the security tab) from "Windows only" to "Windows and SQL" authentication. Can't remember if that was the solution to this particular problem, but it may be worth a shot for ya.

Chris


Similar Threads
Thread Thread Starter Forum Replies Last Post
Access DB to SQL lryckman Access 5 October 24th, 2007 07:03 AM
can't access db in sql server 2005 brillox BOOK: Beginning ASP.NET 2.0 BOOK VB ISBN: 978-0-7645-8850-1; C# ISBN: 978-0-470-04258-8 3 May 3rd, 2007 05:15 PM
How to switch from ACCESS to MSDE kinnari BOOK: ASP.NET Website Programming Problem-Design-Solution 1 January 18th, 2005 08:22 AM
access db to sql server db mikersantiago Classic ASP Basics 4 November 16th, 2004 03:33 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.