Newbie: Problem with dsn odbc

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 & "
")
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
"the_default_db_name" requested by the login. The login failed.
/MyWeb/HelloWorld.asp, line 15

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


Any suggestions?

Shelly

Re: Newbie: Problem with dsn odbc

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

Your question can be posted here:

microsoft.public.dotnet.framework.aspnet



"Shelly" wrote in message news:13gequkk45dr99@corp.supernews.com...
>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 & "
")
> 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 "the_default_db_name" requested by the login. The login
> failed.
> /MyWeb/HelloWorld.asp, line 15
>
> When I use asp.net the connection string is:
> "Data Source=LAPTOP\SQLEXPRESS;Initial Catalog=the_default_db_name;Integrated Security=True" providerName="System.Data.SqlClient"
>
>
> Any suggestions?
>
> Shelly
>

Re: Newbie: Problem with dsn odbc

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.

Jon Paal [MSMD] wrote:
> This is not an ASP.net group it is for Classic ASP.
>
> Your question can be posted here:
>
> microsoft.public.dotnet.framework.aspnet
>
>
>
> "Shelly" wrote in message
> news:13gequkk45dr99@corp.supernews.com...
>> 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 & "
")
>> 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
>> "the_default_db_name" requested by the login. The login failed.
>> /MyWeb/HelloWorld.asp, line 15
>>
>> When I use asp.net the connection string is:
>> "Data Source=LAPTOP\SQLEXPRESS;Initial
>> Catalog=the_default_db_name;Integrated Security=True"
>> providerName="System.Data.SqlClient" Any suggestions?
>>
>> Shelly

--
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
"NO SPAM"

Re: Newbie: Problem with dsn odbc

Shelly wrote:
> 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")

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
"NO SPAM"

Re: Newbie: Problem with dsn odbc

"Jon Paal [MSMD]" wrote in message
news:13gf5eg8g2ui4d7@corp.supernews.com...
> This is not an ASP.net group it is for Classic ASP.
>
> Your question can be posted here:
>
> microsoft.public.dotnet.framework.aspnet

No, it shouldn't. 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


>
>
>
> "Shelly" wrote in message
> news:13gequkk45dr99@corp.supernews.com...
>>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 & "
")
>> 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
>> "the_default_db_name" requested by the login. The login failed.
>> /MyWeb/HelloWorld.asp, line 15
>>
>> When I use asp.net the connection string is:
>> "Data Source=LAPTOP\SQLEXPRESS;Initial
>> Catalog=the_default_db_name;Integrated Security=True"
>> providerName="System.Data.SqlClient"
>>
>>
>> Any suggestions?
>>
>> Shelly
>>
>
>

Re: Newbie: Problem with dsn odbc

"Bob Barrows [MVP]" wrote in message
news:et5jgLCCIHA.4752@TK2MSFTNGP04.phx.gbl...
> 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.

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)

Re: Newbie: Problem with dsn odbc

ok...

error says: "The login failed"

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

Re: Newbie: Problem with dsn odbc

"Jon Paal [MSMD]" wrote in message
news:13gf7cas48hied2@corp.supernews.com...
> ok...
>
> error says: "The login failed"
>
> so it sounds like a problem with the uid/password combination

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)

Re: Newbie: Problem with dsn odbc

This is sounding more like an SQL Server authentication issue

try here:

microsoft.public.sqlserver.programming



"Shelly" wrote in message news:13gf90hafe40c3c@corp.supernews.com...
>
> "Jon Paal [MSMD]" wrote in message news:13gf7cas48hied2@corp.supernews.com...
>> ok...
>>
>> error says: "The login failed"
>>
>> so it sounds like a problem with the uid/password combination
>
> 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)
>

Re: Newbie: Problem with dsn odbc

"Bob Barrows [MVP]" wrote in message
news:OtSsINCCIHA.2268@TK2MSFTNGP02.phx.gbl...
>
> 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.

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

Re: Newbie: Problem with dsn odbc

Shelly wrote:
> "Bob Barrows [MVP]" wrote in message
> news:OtSsINCCIHA.2268@TK2MSFTNGP02.phx.gbl...
>>
>> 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.
>
> Could you give me a pointer to some examples of the sql ole db
> provider?
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
"NO SPAM"