?
Solved

ODBC connection/authentication problem using ASP

Posted on 2003-03-27
15
Medium Priority
?
345 Views
Last Modified: 2012-05-04
I have created a database in SQL Server and am trying to connect to it via ODBC DSN. I have created a DSN and have set it to use Windows Authentication rather then SQL authentication.

I set the default database to one i created and test the connection. The Test proves successful.

I have setup IIS to also use Windows Authentication, and requests to ASPs in my application without database access work fine.

But when ever a page uses the connection string to access the database it reports:
"Invalid object Name 'accessRights'", where accessRights is the name of one of the tables.

I am using the following connection string to access the DSN connection:

strConn = "DSN=myDSN"

Set objConn = Server.CreateObject("ADOBD.Connection")
objConn.Open strConn


I then use a simple Select statement to retrieve a recordset, but i get the error message.
"SELECT * FROM accessRights;"

Can anyone help?
0
Comment
Question by:andichilds
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 3
  • +1
15 Comments
 
LVL 4

Expert Comment

by:Wakie
ID: 8217047
Replace:
Set objConn = Server.CreateObject("ADOBD.Connection")
objConn.Open strConn

With:
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open strConn
0
 

Author Comment

by:andichilds
ID: 8217089
Sorry, that was a typo. It is ADODB.Connection in the connection string.
0
 
LVL 6

Expert Comment

by:MannSoft
ID: 8217669
Does your DSN set a default database?  If not, you'll have to select one programatically before you can work with a table.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Expert Comment

by:MannSoft
ID: 8217677
You would do that with objConn.DefaultDatabase = "mydatabase" btw
0
 

Author Comment

by:andichilds
ID: 8217810
Sorry, that was a typo. It is ADODB.Connection in the connection string.
0
 

Author Comment

by:andichilds
ID: 8217882
I have been looking into the problem today and found that if you put 'dbo.' before the table name in the select statements it works. Is there a quick way of applying this to all the select statements?
0
 
LVL 6

Expert Comment

by:MannSoft
ID: 8217920
For simple selects you can just search and replace "FROM " with "FROM dbo.", but for complex selects from multiple tables, that will only work on the first table of course.  Not to mention the fact that if you have more than a handful of files you'll be opening and closing files all day :)
0
 

Author Comment

by:andichilds
ID: 8217929
I have been looking into the problem today and found that if you put 'dbo.' before the table name in the select statements it works. Is there a quick way of applying this to all the select statements?
0
 

Author Comment

by:andichilds
ID: 8218890
I have been looking into the problem today and found that if you put 'dbo.' before the table name in the select statements it works. Is there a quick way of applying this to all the select statements?
0
 

Author Comment

by:andichilds
ID: 8223499
I have been looking into the problem today and found that if you put 'dbo.' before the table name in the select statements it works. Is there a quick way of applying this to all the select statements?
0
 

Accepted Solution

by:
andichilds earned 0 total points
ID: 8225394
I have solved the problem.

There were two factors.

The first was that the network users had not been added to SQL Server roles datareader and datawriter.

The second, and i refer to, http://support.microsoft.com/support/kb/articles/Q279/5/26.ASP, reports a known problem problem with the SQL ODBC Driver.
0
 
LVL 4

Expert Comment

by:Wakie
ID: 8228724
How do you intend to close this question?
0
 
LVL 6

Expert Comment

by:MannSoft
ID: 8228809
In the future you may not want to hit refresh to see if there are new messages.  You were resubmitting the form data which is why you have the same post up there multiple times.

And since you solved it yourself, it seems like a good candidate for deletion.  You can ask them to do that in the Community Support forum.
0
 
LVL 58

Expert Comment

by:Gary
ID: 9187812
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

PAQ'd and pts refunded

Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
GaryC123
EE Cleanup Volunteer
0

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question