IIS - Newbie: Problem with dsn odbc

Asked By Shelly

06-Oct-07 07:04 AM

I am new to ASP (and just started ASP.NET).  I successfully rebuilt a site
using asp.net that I had previously done in php.  This is for me to learned
asp (as I did in asp.net).

I am having a problem using ODBC to SQL Server on my laptop.  I successfully
used asp.net, but want to do it here in asp using odbc.

I created a dsn called SQLEXPRESS.  At first it had windows authentication.
Then I created a user called ASPpage with a password.

My original code was:

dim  q, num, i, acct, coName
set Conn = Server.CreateObject("ADODB.Connection")
q = "SELECT * FROM Company"
Conn.Open "DSN=SQLEXPRESS"
set rs = Conn.Execute(q)
set of = Server.CreateObject("ADODB.Field")
num = rs.Count
for i=0 To num - 1
acct = of("accountNumber")
coName = of("companyName")
Response.Write(acct & "   " & coName  & "<br>")
next
conn.Close

When I added the user I changed the Conn.Open line to:

Conn.Open "DSN=SQLEXPRESS;UID=ASPpage;PWD=asptest"

Both times I got the error message of failing at the Conn.Open line:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database
/MyWeb/HelloWorld.asp, line 15

When I use asp.net the connection string is:
Catalog=the_default_db_name;Integrated Security=True"
providerName="System.Data.SqlClient"


Any suggestions?

Shelly

06-Oct-07 10:04 AM

This is not an ASP.net group it is for Classic ASP.

Your question can be posted here:

microsoft.public.dotnet.framework.aspnet

06-Oct-07 10:12 AM

She worded it confusingly I admit,  but the code she posted was definitely
ASP vbscript code, not .Net, so I suspect she is having a classic ASP
problem.


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the

06-Oct-07 10:15 AM

I'm confused, and I suspect you are as well. This is not ASP.Net code: this
is classic ASP code. Are you trying to create a .asp page (classic ASP) or a
.aspx page (ASP.Net)?

Either way, I would not recommend using a DSN to connect to SQLExpress: you
should use the native sql client if .Net, or the sql ole db provider if
classic asp.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the

06-Oct-07 10:27 AM

No, it should not.  I am having a problem with classic ASP.  I have it
working in asp.net.  I quote myself: " I successfully used asp.net, but want
to do it here in asp using odbc."

Shelly

06-Oct-07 10:29 AM

I am male.  My name is Sheldon.  My fault.  I should have appended "Sheldon"
since this is my first time in this group.  Yes, it is ***classic ASP***
that I am having a problem with.

Shelly
(Sheldon)

06-Oct-07 10:37 AM

ok...

error says: "The login failed"

so it sounds like a problem with the uid/password combination

06-Oct-07 11:04 AM

Well, I understand why the one where I am using SQL Server authentication
fails.  If I use that same combo in SQL Server Management Studio Express I
get that the login failed because the user is not associated with a trusted
SQL server connection (Error 18452).  Why that happens, I don't know because
I gave this user just about everything in the database role membership and
in the Owned schemas.

This still doesn't explain why on my own machine when I don't put in a
username or password and am using windows authentication, I get the same
error.

In the asp.net case I connect right away -- but I am not using odbc.

I must admit that I have had much more difficulty administering SQL Server
than in learnin asp.net (and now trying to learn asp).

Shelly
(Sheldon)

06-Oct-07 12:59 PM

This is sounding more like an SQL Server authentication issue

try here:

microsoft.public.sqlserver.programming

06-Oct-07 03:32 PM

Could you give me a pointer to some examples of the sql ole db provider?

06-Oct-07 04:33 PM

http://www.connectionstrings.com/?carrier=sqlserver2005
Scroll down past the ODBC samples to the SQL Native Client OLE DB Provider
examples

I have to tell you that I've never gotten integrated security (trusted
connection) working from asp against sql 2005 for some reason. I have always
had to use a sql login account
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the