ConnectionString

R

RN1

I use SQL Server 2005 Management Studio Express as the database. For
ASP files to connect to SQL Server 2005, I am using the following
ConnectionString:

<%
Dim objConn
Set objConn=Server.CreateObject("ADODB.CONNECTION")
objConn.Open "Data Source=RON\SQLEXPRESS;Initial
Catalog=RON;Integrated Security=True"
%>

But ASP generates the following error pointing to the objConn.Open
line:

-------------------------------------------------------------------------------
Multiple-step OLE DB operation generated errors. Check each OLE DB
status value, if available. No work was done.
-------------------------------------------------------------------------------

But when I use the same ConnectionString is ASPX files, no error gets
generated & I can access records residing in SQL Server 2005
successfully.

What could be causing this problem?
 
B

Bob Barrows [MVP]

RN1 said:
I use SQL Server 2005 Management Studio Express as the database. For
ASP files to connect to SQL Server 2005, I am using the following
ConnectionString:

<%
Dim objConn
Set objConn=Server.CreateObject("ADODB.CONNECTION")
objConn.Open "Data Source=RON\SQLEXPRESS;Initial
Catalog=RON;Integrated Security=True"
%>

But ASP generates the following error pointing to the objConn.Open
line:

---------------------------------------------------------------------- ---------
Multiple-step OLE DB operation generated errors. Check each OLE DB
status value, if available. No work was done.
---------------------------------------------------------------------- ---------

But when I use the same ConnectionString is ASPX files, no error gets
generated & I can access records residing in SQL Server 2005
successfully.

What could be causing this problem?

..Net provider vs OLEDB provider means different connection string.
You need to specify the provider. Go to www.connectionstrings.com to see
examples of connetion strings to use.
 
R

RN1

.Net provider vs OLEDB provider means different connection string.
You need to specify the provider. Go towww.connectionstrings.comto see
examples of connetion strings to use.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.- Hide quoted text -

- Show quoted text -

Thanks Bob for the prompt response. I did have a look at
connectionstrings.com before posting my question but couldn't make it
work.

I even tried by creating a System DSN by navigating to Start-->Control
Panel-->Administrative Tools-->Data Sources (ODBC) & then using that
DSN but that didn't work as well. I am using the Windows
Authentication mode for logging into SQL Server 2005 & used the same
while creating the DSN.

I just can't seem to find where am I going wrong. Any other
suggestions, sir?

Regards,

Ron
 
B

Bob Barrows [MVP]

RN1 said:
Thanks Bob for the prompt response. I did have a look at
connectionstrings.com before posting my question but couldn't make it
work.

Show me what you tried and the error message you received. The string
you tried above is not correct. It needs a "Provider=" attribute which
you can find at connectionstrings. I think it's SQLNCLI but you could
also use SQLOLEDB. I have not use SQL Express so I'm not sure if you
need a different provider. The thing is: you HAVE to specify the
provider in the connection string. Look at the OLEDB examples at
connectionstrings
I even tried by creating a System DSN
No no no no no - stay away from ODBC!
 
T

TOUDIdel

Uzytkownik "RN1 said:
I use SQL Server 2005 Management Studio Express as the database. For
ASP files to connect to SQL Server 2005, I am using the following
ConnectionString:
<%
Dim objConn
Set objConn=Server.CreateObject("ADODB.CONNECTION")
objConn.Open "Data Source=RON\SQLEXPRESS;Initial
Catalog=RON;Integrated Security=True"
%>
But ASP generates the following error pointing to the objConn.Open
line:

try
Driver={SQL
Server};Server=localhost\sqlexpress;Database=Northwind;Trusted_Connection=yes
 
B

Bob Barrows [MVP]

TOUDIdel said:
Uzytkownik "RN1" <[email protected]> napisal w wiadomosci
news:254ef523-e81b-48f9-9182-b32861a293b0@v67g2000hse.googlegroups.com...

try
Driver={SQL
Server};Server=localhost\sqlexpress;Database=Northwind;Trusted_Connectio
n=yes

Why? There's a perfectly usable native OLEDB provider. Why suggest the
obsolete ODBC driver and syntax?

To RN1 look at the section titled "SQL Native Client OLE DB Provider" on
this page: http://www.connectionstrings.com/?carrier=sqlserver2005
 
R

RN1

Server};Server=localhost\sqlexpress;Database=Northwind;Trusted_Connectio
n=yes

Why? There's a perfectly usable native OLEDB provider. Why suggest the
obsolete ODBC driver and syntax?

To RN1 look at the section titled "SQL Native Client OLE DB Provider" on
this page:http://www.connectionstrings.com/?carrier=sqlserver2005

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

This is the ConnectionString I tried:

Provider=SQLNCLI;Server=RON
\SQLEXPRESS;Database=DB1;Trusted_Connection=Yes;

The above ConnectionString generates the following error:

Cannot open database "DB1" requested by the login. The login failed.

My entire work has come to a standstill just because I have not been
able to frame the correct ConnectionString.
 
R

RN1

This is the ConnectionString I tried:

Provider=SQLNCLI;Server=RON
\SQLEXPRESS;Database=DB1;Trusted_Connection=Yes;

The above ConnectionString generates the following error:

Cannot open database "DB1" requested by the login. The login failed.

My entire work has come to a standstill just because I have not been
able to frame the correct ConnectionString.- Hide quoted text -

- Show quoted text -

If I use either of the following 2 ConnectionStrings:

Provider=SQLOLEDB;Data Source=RON\SQLEXPRESS;Trusted_Connection=Yes;

or

Provider=SQLNCLI;Data Source=RON\SQLEXPRESS;Trusted_Connection=Yes;

then ASP generates the following error

Invalid object name 'MyTable'

pointing to the following line which contains the SQL query:

strSQL="SELECT * FROM MyTable"

I guess this means that ASP is now able to connect to SQL Server 2005
but the problem comes up when I add the database name to the
ConnectionString

Provider=SQLOLEDB;Data Source=RON
\SQLEXPRESS;Database=DB1;Trusted_Connection=Yes;

or

Provider=SQLNCLI;Data Source=RON
\SQLEXPRESS;Database=DB1;Trusted_Connection=Yes;

then ASP generates the error

Cannot open database "DB1" requested by the login. The login failed

What could be causing this error now?
 
J

Jeff Dillon

Trusted Connection would only work if the ASP server/page is on the same
server as SQL. Othewise, you'll need to pass a SQL username and password

Jeff

This is the ConnectionString I tried:

Provider=SQLNCLI;Server=RON
\SQLEXPRESS;Database=DB1;Trusted_Connection=Yes;

The above ConnectionString generates the following error:

Cannot open database "DB1" requested by the login. The login failed.

My entire work has come to a standstill just because I have not been
able to frame the correct ConnectionString.- Hide quoted text -

- Show quoted text -

If I use either of the following 2 ConnectionStrings:

Provider=SQLOLEDB;Data Source=RON\SQLEXPRESS;Trusted_Connection=Yes;

or

Provider=SQLNCLI;Data Source=RON\SQLEXPRESS;Trusted_Connection=Yes;

then ASP generates the following error

Invalid object name 'MyTable'

pointing to the following line which contains the SQL query:

strSQL="SELECT * FROM MyTable"

I guess this means that ASP is now able to connect to SQL Server 2005
but the problem comes up when I add the database name to the
ConnectionString

Provider=SQLOLEDB;Data Source=RON
\SQLEXPRESS;Database=DB1;Trusted_Connection=Yes;

or

Provider=SQLNCLI;Data Source=RON
\SQLEXPRESS;Database=DB1;Trusted_Connection=Yes;

then ASP generates the error

Cannot open database "DB1" requested by the login. The login failed

What could be causing this error now?
 
B

Bob Barrows [MVP]

RN1 said:
If I use either of the following 2 ConnectionStrings:

Provider=SQLOLEDB;Data Source=RON\SQLEXPRESS;Trusted_Connection=Yes;

or

Provider=SQLNCLI;Data Source=RON\SQLEXPRESS;Trusted_Connection=Yes;

then ASP generates the following error

Invalid object name 'MyTable'

pointing to the following line which contains the SQL query:

strSQL="SELECT * FROM MyTable"

I guess this means that ASP is now able to connect to SQL Server 2005
but the problem comes up when I add the database name to the
ConnectionString

Provider=SQLOLEDB;Data Source=RON
\SQLEXPRESS;Database=DB1;Trusted_Connection=Yes;

You can use
SELECT * FROM DB1.dbo.MyTable

However, try "Initial Catalog", instead of "Database" and "Integrated
Security=SSPI" instead of "Trusted_Connection=Yes"

or

Provider=SQLNCLI;Data Source=RON
\SQLEXPRESS;Database=DB1;Trusted_Connection=Yes;

then ASP generates the error

Cannot open database "DB1" requested by the login. The login failed

I have to say that I have not had success connecting to SQL 2005 from ASP
via integrated security. I have had to create a sql login and use that in my
connection strings. This means switching the sql server to Mixed security.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top