ConnectionString

Discussion in 'ASP General' started by RN1, Feb 13, 2008.

  1. RN1

    RN1 Guest

    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?
    RN1, Feb 13, 2008
    #1
    1. Advertising

  2. RN1 wrote:
    > 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.

    --
    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.
    Bob Barrows [MVP], Feb 13, 2008
    #2
    1. Advertising

  3. RN1

    RN1 Guest

    On Feb 14, 12:41 am, "Bob Barrows [MVP]" <>
    wrote:
    > RN1 wrote:
    > > 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 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
    RN1, Feb 13, 2008
    #3
  4. RN1 wrote:
    > On Feb 14, 12:41 am, "Bob Barrows [MVP]" <>
    > wrote:
    >> RN1 wrote:
    >>> 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"
    >>> %>

    >>



    > 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!
    --
    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.
    Bob Barrows [MVP], Feb 13, 2008
    #4
  5. RN1

    TOUDIdel Guest

    Uzytkownik "RN1" <> napisal w wiadomosci
    news:...
    >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
    --
    td
    TOUDIdel, Feb 13, 2008
    #5
  6. TOUDIdel wrote:
    > Uzytkownik "RN1" <> napisal w wiadomosci
    >

    news:...
    >> 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_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.
    Bob Barrows [MVP], Feb 13, 2008
    #6
  7. RN1

    RN1 Guest

    On Feb 14, 2:26 am, "Bob Barrows [MVP]" <>
    wrote:
    > TOUDIdel wrote:
    > > Uzytkownik "RN1" <> napisal w wiadomosci

    >
    > news:...>> 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_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.
    RN1, Feb 14, 2008
    #7
  8. RN1

    RN1 Guest

    On Feb 14, 6:00 am, RN1 <> wrote:
    > On Feb 14, 2:26 am, "Bob Barrows [MVP]" <>
    > wrote:
    >
    >
    >
    >
    >
    > > TOUDIdel wrote:
    > > > Uzytkownik "RN1" <> napisal w wiadomosci

    >
    > >news:...>> 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_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.- 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?
    RN1, Feb 14, 2008
    #8
  9. RN1

    Jeff Dillon Guest

    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

    "RN1" <> wrote in message
    news:...
    On Feb 14, 6:00 am, RN1 <> wrote:
    > On Feb 14, 2:26 am, "Bob Barrows [MVP]" <>
    > wrote:
    >
    >
    >
    >
    >
    > > TOUDIdel wrote:
    > > > Uzytkownik "RN1" <> napisal w wiadomosci

    >
    > >news:...>>
    > >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_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.- 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?
    Jeff Dillon, Feb 14, 2008
    #9
  10. RN1 wrote:
    > On Feb 14, 6:00 am, RN1 <> wrote:
    >> On Feb 14, 2:26 am, "Bob Barrows [MVP]" <>
    >> wrote:
    >>
    >>
    >>
    >>
    >>
    >>> TOUDIdel wrote:
    >>>> Uzytkownik "RN1" <> napisal w wiadomosci

    >>
    >>> news:...>>
    >>> 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_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

    >>


    >> 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;


    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.


    --
    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"
    Bob Barrows [MVP], Feb 14, 2008
    #10
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Carl Prothman [MVP]

    Re: [ASP.NET] MS SQL Connectionstring

    Carl Prothman [MVP], Jul 11, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    6,486
    Carl Prothman [MVP]
    Jul 11, 2003
  2. Frank
    Replies:
    1
    Views:
    1,609
    Robbe Morris [C# MVP]
    Mar 6, 2005
  3. josephrthomas

    ConnectionString Initialization

    josephrthomas, Dec 23, 2003, in forum: ASP .Net
    Replies:
    5
    Views:
    4,146
    Simon Aspden
    Dec 23, 2003
  4. Lars Netzel
    Replies:
    1
    Views:
    1,022
    Lars Netzel
    May 14, 2004
  5. G. Dean Blake

    ConnectionString - best practices ques

    G. Dean Blake, Jun 3, 2004, in forum: ASP .Net
    Replies:
    2
    Views:
    3,586
    Saravana [MVP]
    Jun 4, 2004
Loading...

Share This Page