?
Solved

ODBC connection/authentication problem using ASP

Posted on 2003-03-27
15
Medium Priority
?
349 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
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

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!

Question has a verified solution.

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

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
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/…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

571 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