ASP and SQL Server 2005 Express

Discussion in 'ASP General' started by Anil Gupte, Nov 17, 2007.

  1. Anil Gupte

    Anil Gupte Guest

    I am using the following to try to connect to the database, but it does not
    seem to be working.

    Dim sConnString
    sConnString = "Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security
    Info=False;Initial Catalog=MediaDB;Data Source=MEDIAMACHINE\SQLEXPRESS"
    Connection.Open sConnString
    Dim Recordset
    Set Recordset=Server.CreateObject("ADODB.Recordset")

    Can someone tell me the correct syntax? The original syntax (which worked)
    for an Access DB was as follows:
    sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
    Server.MapPath("/Music/DB/MediaDB.mdb")

    TIA
    --
    Anil Gupte
    www.keeninc.net
    www.icinema.com
     
    Anil Gupte, Nov 17, 2007
    #1
    1. Advertising

  2. Anil Gupte wrote:
    > I am using the following to try to connect to the database, but it
    > does not seem to be working.
    >
    > Dim sConnString
    > sConnString = "Provider=SQLNCLI.1;Integrated Security=SSPI;Persist
    > Security Info=False;Initial Catalog=MediaDB;Data
    > Source=MEDIAMACHINE\SQLEXPRESS" Connection.Open sConnString
    > Dim Recordset
    > Set Recordset=Server.CreateObject("ADODB.Recordset")
    >
    > Can someone tell me the correct syntax? The original syntax (which
    > worked) for an Access DB was as follows:
    > sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
    > Server.MapPath("/Music/DB/MediaDB.mdb")
    >

    You really need to show us the error message, but i will say that I have had
    little success connecting to SQL2005 with integrated security from ASP. I
    would suggest creating a sql login on your server and using its user id and
    password in your connection string instead of Integrated Security=SSPI

    --
    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], Nov 17, 2007
    #2
    1. Advertising

  3. Anil Gupte

    Anil Gupte Guest

    0xE7D:The connection cannot be used to perform this operation. It is either
    closed or invalid in this context.

    --
    Anil Gupte
    www.keeninc.net
    www.icinema.com

    "Bob Barrows [MVP]" <> wrote in message
    news:OG%...
    > Anil Gupte wrote:
    >> I am using the following to try to connect to the database, but it
    >> does not seem to be working.
    >>
    >> Dim sConnString
    >> sConnString = "Provider=SQLNCLI.1;Integrated Security=SSPI;Persist
    >> Security Info=False;Initial Catalog=MediaDB;Data
    >> Source=MEDIAMACHINE\SQLEXPRESS" Connection.Open sConnString
    >> Dim Recordset
    >> Set Recordset=Server.CreateObject("ADODB.Recordset")
    >>
    >> Can someone tell me the correct syntax? The original syntax (which
    >> worked) for an Access DB was as follows:
    >> sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
    >> Server.MapPath("/Music/DB/MediaDB.mdb")
    >>

    > You really need to show us the error message, but i will say that I have
    > had little success connecting to SQL2005 with integrated security from
    > ASP. I would suggest creating a sql login on your server and using its
    > user id and password in your connection string instead of Integrated
    > Security=SSPI
    >
    > --
    > 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"
    >
     
    Anil Gupte, Nov 17, 2007
    #3
  4. So the "Connection.Open sConnString" statement didn't raise an error? I
    don't see any code in which you tried to use the connection object. Neither
    do I see a

    Set Connection = Createobject("adodb.connection")

    statement. Does one exist?

    Anil Gupte wrote:
    > 0xE7D:The connection cannot be used to perform this operation. It is
    > either closed or invalid in this context.
    >
    >
    > "Bob Barrows [MVP]" <> wrote in message
    > news:OG%...
    >> Anil Gupte wrote:
    >>> I am using the following to try to connect to the database, but it
    >>> does not seem to be working.
    >>>
    >>> Dim sConnString
    >>> sConnString = "Provider=SQLNCLI.1;Integrated Security=SSPI;Persist
    >>> Security Info=False;Initial Catalog=MediaDB;Data
    >>> Source=MEDIAMACHINE\SQLEXPRESS" Connection.Open sConnString
    >>> Dim Recordset
    >>> Set Recordset=Server.CreateObject("ADODB.Recordset")
    >>>
    >>> Can someone tell me the correct syntax? The original syntax (which
    >>> worked) for an Access DB was as follows:
    >>> sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
    >>> Server.MapPath("/Music/DB/MediaDB.mdb")
    >>>

    >> You really need to show us the error message, but i will say that I
    >> have had little success connecting to SQL2005 with integrated
    >> security from ASP. I would suggest creating a sql login on your
    >> server and using its user id and password in your connection string
    >> instead of Integrated Security=SSPI
    >>
    >> --
    >> 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"


    --
    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], Nov 17, 2007
    #4
  5. Anil Gupte

    Anil Gupte Guest

    Yes, I have that earlier in the file
    Dim Connection
    Set Connection=Server.CreateObject("ADODB.Connection")

    sConnString = "Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security
    Info=False;Initial Catalog=iMedia;Data Source=IMEDIA/SQLEXPRESS"
    ' Tried both the above and the below - no luck
    sConnString = "Provider=SQLOLEDB;Integrated Security=True;Initial
    Catalog=IMEDIA;Data Source=.\SQLEXPRESS"
    Connection.Open sConnString
    Dim Recordset 'Here we declare our variable that will hold our new object
    Set Recordset=Server.CreateObject("ADODB.Recordset")

    Thanx for your help
    --
    Anil Gupte
    www.keeninc.net
    www.icinema.com

    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > So the "Connection.Open sConnString" statement didn't raise an error? I
    > don't see any code in which you tried to use the connection object.
    > Neither do I see a
    >
    > Set Connection = Createobject("adodb.connection")
    >
    > statement. Does one exist?
    >
    > Anil Gupte wrote:
    >> 0xE7D:The connection cannot be used to perform this operation. It is
    >> either closed or invalid in this context.
    >>
    >>
    >> "Bob Barrows [MVP]" <> wrote in message
    >> news:OG%...
    >>> Anil Gupte wrote:
    >>>> I am using the following to try to connect to the database, but it
    >>>> does not seem to be working.
    >>>>
    >>>> Dim sConnString
    >>>> sConnString = "Provider=SQLNCLI.1;Integrated Security=SSPI;Persist
    >>>> Security Info=False;Initial Catalog=MediaDB;Data
    >>>> Source=MEDIAMACHINE\SQLEXPRESS" Connection.Open sConnString
    >>>> Dim Recordset
    >>>> Set Recordset=Server.CreateObject("ADODB.Recordset")
    >>>>
    >>>> Can someone tell me the correct syntax? The original syntax (which
    >>>> worked) for an Access DB was as follows:
    >>>> sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
    >>>> Server.MapPath("/Music/DB/MediaDB.mdb")
    >>>>
    >>> You really need to show us the error message, but i will say that I
    >>> have had little success connecting to SQL2005 with integrated
    >>> security from ASP. I would suggest creating a sql login on your
    >>> server and using its user id and password in your connection string
    >>> instead of Integrated Security=SSPI
    >>>
    >>> --
    >>> 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"

    >
    > --
    > 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"
    >
     
    Anil Gupte, Nov 18, 2007
    #5
  6. Anil Gupte

    Anil Gupte Guest

    I also have now tried creating a DSN called Media and using:
    sConnString = "DSN=Media"

    Doesn't work.

    --
    Anil Gupte
    www.keeninc.net
    www.icinema.com

    "Anil Gupte" <> wrote in message
    news:On$...
    >I am using the following to try to connect to the database, but it does not
    >seem to be working.
    >
    > Dim sConnString
    > sConnString = "Provider=SQLNCLI.1;Integrated Security=SSPI;Persist
    > Security Info=False;Initial Catalog=MediaDB;Data
    > Source=MEDIAMACHINE\SQLEXPRESS"
    > Connection.Open sConnString
    > Dim Recordset
    > Set Recordset=Server.CreateObject("ADODB.Recordset")
    >
    > Can someone tell me the correct syntax? The original syntax (which
    > worked) for an Access DB was as follows:
    > sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
    > Server.MapPath("/Music/DB/MediaDB.mdb")
    >
    > TIA
    > --
    > Anil Gupte
    > www.keeninc.net
    > www.icinema.com
    >
    >
     
    Anil Gupte, Nov 18, 2007
    #6
  7. You still have yet to show us the line that produces that error. If the
    Connection.Open statement does not throw an error, then there is nothing
    wrong with your connection string. I do not blieve the Connection.Open
    statement can throw that particular error ... wait ... "Connection" may be a
    reserved keyword. Try calling your connection object "cn" or "conn" or
    something like that.

    The only other possibility is that the Connection.Open statement actually IS
    throwing an error, but you have an "on error resume next" statement earlier
    in your code that is masking te error. Make sure you comment out that
    statement so you can see if the Open statement produces an error. If it
    does, show us THAT error message.

    Anil Gupte wrote:
    > Yes, I have that earlier in the file
    > Dim Connection
    > Set Connection=Server.CreateObject("ADODB.Connection")
    >
    > sConnString = "Provider=SQLNCLI.1;Integrated Security=SSPI;Persist
    > Security Info=False;Initial Catalog=iMedia;Data
    > Source=IMEDIA/SQLEXPRESS" ' Tried both the above and the below - no
    > luck sConnString = "Provider=SQLOLEDB;Integrated Security=True;Initial
    > Catalog=IMEDIA;Data Source=.\SQLEXPRESS"
    > Connection.Open sConnString
    > Dim Recordset 'Here we declare our variable that will hold our new
    > object Set Recordset=Server.CreateObject("ADODB.Recordset")
    >
    > Thanx for your help
    >
    > "Bob Barrows [MVP]" <> wrote in message
    > news:...
    >> So the "Connection.Open sConnString" statement didn't raise an
    >> error? I don't see any code in which you tried to use the connection
    >> object. Neither do I see a
    >>
    >> Set Connection = Createobject("adodb.connection")
    >>
    >> statement. Does one exist?
    >>
    >> Anil Gupte wrote:
    >>> 0xE7D:The connection cannot be used to perform this operation. It is
    >>> either closed or invalid in this context.
    >>>
    >>>
    >>> "Bob Barrows [MVP]" <> wrote in message
    >>> news:OG%...
    >>>> Anil Gupte wrote:
    >>>>> I am using the following to try to connect to the database, but it
    >>>>> does not seem to be working.
    >>>>>
    >>>>> Dim sConnString
    >>>>> sConnString = "Provider=SQLNCLI.1;Integrated Security=SSPI;Persist
    >>>>> Security Info=False;Initial Catalog=MediaDB;Data
    >>>>> Source=MEDIAMACHINE\SQLEXPRESS" Connection.Open sConnString
    >>>>> Dim Recordset
    >>>>> Set Recordset=Server.CreateObject("ADODB.Recordset")
    >>>>>
    >>>>> Can someone tell me the correct syntax? The original syntax
    >>>>> (which worked) for an Access DB was as follows:
    >>>>> sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
    >>>>> Server.MapPath("/Music/DB/MediaDB.mdb")
    >>>>>
    >>>> You really need to show us the error message, but i will say that I
    >>>> have had little success connecting to SQL2005 with integrated
    >>>> security from ASP. I would suggest creating a sql login on your
    >>>> server and using its user id and password in your connection string
    >>>> instead of Integrated Security=SSPI
    >>>>
    >>>> --
    >>>> 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"

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


    --
    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], Nov 18, 2007
    #7
  8. Jon Paal [MSMD] wrote:
    > This is not actually an ASP question.


    I'm not sure what makes you say that? I mean, he hasn't exactly shown us
    enough to make that determination yet, at least IMHO.
    --
    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], Nov 18, 2007
    #8
  9. Anil Gupte

    Anil Gupte Guest

    Bob:

    Sorry, you are right, I had the "On Error Resume Next", which I have now
    commented out. Here are the various error messages - I am printing the
    connection string at the top of each page.
    *****************************
    Provider=SQLOLEDB;Integrated Security=True;Initial Catalog=MEDIA;Data
    Source=.\SQLEXPRESS
    Provider error '80040e21'
    Multiple-step OLE DB operation generated errors. Check each OLE DB status
    value, if available. No work was done.
    *****************************
    DSN=Media
    Microsoft OLE DB Provider for ODBC Drivers error '80004005'
    [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database "iMedia"
    requested by the login. The login failed.
    *****************************
    Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security
    Info=False;Initial Catalog=iMedia;Data Source=MEDIA/SQLEXPRESS
    Microsoft SQL Native Client error '80004005'
    Named Pipes Provider: Could not open a connection to SQL Server [51].
    *****************************
    BTW, with the DSN above I tried both Windows Authentication and SQL User
    Authentication. I also did the "Test Data Source" in both instances and it
    works. I know it is some syntax problem because I am using this last
    connection string succesfully with ASP.Net/C# and a VB.Net service both of
    which I wrote and are on the same machine/same website.
    All help is appreciated. Thanx,
    --
    Anil Gupte
    www.keeninc.net
    www.icinema.com

    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > You still have yet to show us the line that produces that error. If the
    > Connection.Open statement does not throw an error, then there is nothing
    > wrong with your connection string. I do not blieve the Connection.Open
    > statement can throw that particular error ... wait ... "Connection" may be
    > a reserved keyword. Try calling your connection object "cn" or "conn" or
    > something like that.
    >
    > The only other possibility is that the Connection.Open statement actually
    > IS throwing an error, but you have an "on error resume next" statement
    > earlier in your code that is masking te error. Make sure you comment out
    > that statement so you can see if the Open statement produces an error. If
    > it does, show us THAT error message.
    >
    > Anil Gupte wrote:
    >> Yes, I have that earlier in the file
    >> Dim Connection
    >> Set Connection=Server.CreateObject("ADODB.Connection")
    >>
    >> sConnString = "Provider=SQLNCLI.1;Integrated Security=SSPI;Persist
    >> Security Info=False;Initial Catalog=iMedia;Data
    >> Source=IMEDIA/SQLEXPRESS" ' Tried both the above and the below - no
    >> luck sConnString = "Provider=SQLOLEDB;Integrated Security=True;Initial
    >> Catalog=IMEDIA;Data Source=.\SQLEXPRESS"
    >> Connection.Open sConnString
    >> Dim Recordset 'Here we declare our variable that will hold our new
    >> object Set Recordset=Server.CreateObject("ADODB.Recordset")
    >>
    >> Thanx for your help
    >>
    >> "Bob Barrows [MVP]" <> wrote in message
    >> news:...
    >>> So the "Connection.Open sConnString" statement didn't raise an
    >>> error? I don't see any code in which you tried to use the connection
    >>> object. Neither do I see a
    >>>
    >>> Set Connection = Createobject("adodb.connection")
    >>>
    >>> statement. Does one exist?
    >>>
    >>> Anil Gupte wrote:
    >>>> 0xE7D:The connection cannot be used to perform this operation. It is
    >>>> either closed or invalid in this context.
    >>>>
    >>>>
    >>>> "Bob Barrows [MVP]" <> wrote in message
    >>>> news:OG%...
    >>>>> Anil Gupte wrote:
    >>>>>> I am using the following to try to connect to the database, but it
    >>>>>> does not seem to be working.
    >>>>>>
    >>>>>> Dim sConnString
    >>>>>> sConnString = "Provider=SQLNCLI.1;Integrated Security=SSPI;Persist
    >>>>>> Security Info=False;Initial Catalog=MediaDB;Data
    >>>>>> Source=MEDIAMACHINE\SQLEXPRESS" Connection.Open sConnString
    >>>>>> Dim Recordset
    >>>>>> Set Recordset=Server.CreateObject("ADODB.Recordset")
    >>>>>>
    >>>>>> Can someone tell me the correct syntax? The original syntax
    >>>>>> (which worked) for an Access DB was as follows:
    >>>>>> sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
    >>>>>> Server.MapPath("/Music/DB/MediaDB.mdb")
    >>>>>>
    >>>>> You really need to show us the error message, but i will say that I
    >>>>> have had little success connecting to SQL2005 with integrated
    >>>>> security from ASP. I would suggest creating a sql login on your
    >>>>> server and using its user id and password in your connection string
    >>>>> instead of Integrated Security=SSPI
    >>>>>
    >>>>> --
    >>>>> 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"
    >>>
    >>> --
    >>> 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"

    >
    > --
    > 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"
    >
     
    Anil Gupte, Nov 19, 2007
    #9
  10. Anil Gupte

    Anil Gupte Guest

    Did you ever get a resolution to this
    http://www.webservertalk.com/archive126-2007-3-1848937.html?

    --
    Anil Gupte
    www.keeninc.net
    www.icinema.com

    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > You still have yet to show us the line that produces that error. If the
    > Connection.Open statement does not throw an error, then there is nothing
    > wrong with your connection string. I do not blieve the Connection.Open
    > statement can throw that particular error ... wait ... "Connection" may be
    > a reserved keyword. Try calling your connection object "cn" or "conn" or
    > something like that.
    >
    > The only other possibility is that the Connection.Open statement actually
    > IS throwing an error, but you have an "on error resume next" statement
    > earlier in your code that is masking te error. Make sure you comment out
    > that statement so you can see if the Open statement produces an error. If
    > it does, show us THAT error message.
    >
    > Anil Gupte wrote:
    >> Yes, I have that earlier in the file
    >> Dim Connection
    >> Set Connection=Server.CreateObject("ADODB.Connection")
    >>
    >> sConnString = "Provider=SQLNCLI.1;Integrated Security=SSPI;Persist
    >> Security Info=False;Initial Catalog=iMedia;Data
    >> Source=IMEDIA/SQLEXPRESS" ' Tried both the above and the below - no
    >> luck sConnString = "Provider=SQLOLEDB;Integrated Security=True;Initial
    >> Catalog=IMEDIA;Data Source=.\SQLEXPRESS"
    >> Connection.Open sConnString
    >> Dim Recordset 'Here we declare our variable that will hold our new
    >> object Set Recordset=Server.CreateObject("ADODB.Recordset")
    >>
    >> Thanx for your help
    >>
    >> "Bob Barrows [MVP]" <> wrote in message
    >> news:...
    >>> So the "Connection.Open sConnString" statement didn't raise an
    >>> error? I don't see any code in which you tried to use the connection
    >>> object. Neither do I see a
    >>>
    >>> Set Connection = Createobject("adodb.connection")
    >>>
    >>> statement. Does one exist?
    >>>
    >>> Anil Gupte wrote:
    >>>> 0xE7D:The connection cannot be used to perform this operation. It is
    >>>> either closed or invalid in this context.
    >>>>
    >>>>
    >>>> "Bob Barrows [MVP]" <> wrote in message
    >>>> news:OG%...
    >>>>> Anil Gupte wrote:
    >>>>>> I am using the following to try to connect to the database, but it
    >>>>>> does not seem to be working.
    >>>>>>
    >>>>>> Dim sConnString
    >>>>>> sConnString = "Provider=SQLNCLI.1;Integrated Security=SSPI;Persist
    >>>>>> Security Info=False;Initial Catalog=MediaDB;Data
    >>>>>> Source=MEDIAMACHINE\SQLEXPRESS" Connection.Open sConnString
    >>>>>> Dim Recordset
    >>>>>> Set Recordset=Server.CreateObject("ADODB.Recordset")
    >>>>>>
    >>>>>> Can someone tell me the correct syntax? The original syntax
    >>>>>> (which worked) for an Access DB was as follows:
    >>>>>> sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
    >>>>>> Server.MapPath("/Music/DB/MediaDB.mdb")
    >>>>>>
    >>>>> You really need to show us the error message, but i will say that I
    >>>>> have had little success connecting to SQL2005 with integrated
    >>>>> security from ASP. I would suggest creating a sql login on your
    >>>>> server and using its user id and password in your connection string
    >>>>> instead of Integrated Security=SSPI
    >>>>>
    >>>>> --
    >>>>> 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"
    >>>
    >>> --
    >>> 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"

    >
    > --
    > 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"
    >
     
    Anil Gupte, Nov 19, 2007
    #10
  11. Anil Gupte

    Anil Gupte Guest

    I believe it is an ASP question because I can connect fine in other
    languages including C#/ASP.Net and VB.Net It is only in ASP that the
    connection strings are not working, so there must be a syntax issue.

    Thanx,
    --
    Anil Gupte
    www.keeninc.net
    www.icinema.com

    "Jon Paal [MSMD]" <Jon nospam Paal @ everywhere dot com> wrote in message
    news:...
    > This is not actually an ASP question.
    > Try posting your problem to the SQL server group here:
    >
    > microsoft.public.sqlserver.programming
    >
    >
     
    Anil Gupte, Nov 19, 2007
    #11
  12. Anil Gupte

    Anil Gupte Guest

    Here is another:

    Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;Initial Catalog=Media;User
    id=sa;Password=Abc
    ADODB.Recordset error '800a0bb9'

    Arguments are of the wrong type, are out of acceptable range, or are in
    conflict with one another.

    I also tried
    Provider=SQLNCLI.1;Data Source=.\SQLEXPRESS;Initial Catalog=Media;User
    id=sa;Password=Abc

    Same error.

    --
    Anil Gupte
    www.keeninc.net
    www.icinema.com


    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > You still have yet to show us the line that produces that error. If the
    > Connection.Open statement does not throw an error, then there is nothing
    > wrong with your connection string. I do not blieve the Connection.Open
    > statement can throw that particular error ... wait ... "Connection" may be
    > a reserved keyword. Try calling your connection object "cn" or "conn" or
    > something like that.
    >
    > The only other possibility is that the Connection.Open statement actually
    > IS throwing an error, but you have an "on error resume next" statement
    > earlier in your code that is masking te error. Make sure you comment out
    > that statement so you can see if the Open statement produces an error. If
    > it does, show us THAT error message.
    >
    > Anil Gupte wrote:
    >> Yes, I have that earlier in the file
    >> Dim Connection
    >> Set Connection=Server.CreateObject("ADODB.Connection")
    >>
    >> sConnString = "Provider=SQLNCLI.1;Integrated Security=SSPI;Persist
    >> Security Info=False;Initial Catalog=iMedia;Data
    >> Source=IMEDIA/SQLEXPRESS" ' Tried both the above and the below - no
    >> luck sConnString = "Provider=SQLOLEDB;Integrated Security=True;Initial
    >> Catalog=IMEDIA;Data Source=.\SQLEXPRESS"
    >> Connection.Open sConnString
    >> Dim Recordset 'Here we declare our variable that will hold our new
    >> object Set Recordset=Server.CreateObject("ADODB.Recordset")
    >>
    >> Thanx for your help
    >>
    >> "Bob Barrows [MVP]" <> wrote in message
    >> news:...
    >>> So the "Connection.Open sConnString" statement didn't raise an
    >>> error? I don't see any code in which you tried to use the connection
    >>> object. Neither do I see a
    >>>
    >>> Set Connection = Createobject("adodb.connection")
    >>>
    >>> statement. Does one exist?
    >>>
    >>> Anil Gupte wrote:
    >>>> 0xE7D:The connection cannot be used to perform this operation. It is
    >>>> either closed or invalid in this context.
    >>>>
    >>>>
    >>>> "Bob Barrows [MVP]" <> wrote in message
    >>>> news:OG%...
    >>>>> Anil Gupte wrote:
    >>>>>> I am using the following to try to connect to the database, but it
    >>>>>> does not seem to be working.
    >>>>>>
    >>>>>> Dim sConnString
    >>>>>> sConnString = "Provider=SQLNCLI.1;Integrated Security=SSPI;Persist
    >>>>>> Security Info=False;Initial Catalog=MediaDB;Data
    >>>>>> Source=MEDIAMACHINE\SQLEXPRESS" Connection.Open sConnString
    >>>>>> Dim Recordset
    >>>>>> Set Recordset=Server.CreateObject("ADODB.Recordset")
    >>>>>>
    >>>>>> Can someone tell me the correct syntax? The original syntax
    >>>>>> (which worked) for an Access DB was as follows:
    >>>>>> sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
    >>>>>> Server.MapPath("/Music/DB/MediaDB.mdb")
    >>>>>>
    >>>>> You really need to show us the error message, but i will say that I
    >>>>> have had little success connecting to SQL2005 with integrated
    >>>>> security from ASP. I would suggest creating a sql login on your
    >>>>> server and using its user id and password in your connection string
    >>>>> instead of Integrated Security=SSPI
    >>>>>
    >>>>> --
    >>>>> 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"
    >>>
    >>> --
    >>> 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"

    >
    > --
    > 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"
    >
     
    Anil Gupte, Nov 19, 2007
    #12
  13. I have recently upsized my access db to MSSQL too and here is the
    connection string I use and it is working fine. Might help:

    strConnString="provider=sqloledb;server=SE\SQLEXPRESS;uid=USER;pwd=PASSWORD;database=DB;"

    set my_Conn = Server.CreateObject("ADODB.Connection")
    my_Conn.Errors.Clear

    my_Conn.Open strConnString

    Anil Gupte wrote:
    > I believe it is an ASP question because I can connect fine in other
    > languages including C#/ASP.Net and VB.Net It is only in ASP that the
    > connection strings are not working, so there must be a syntax issue.
    >
    > Thanx,
     
    M. Savas Zorlu, Nov 19, 2007
    #13
  14. Anil Gupte

    Anil Gupte Guest

    Oh, Wow! This worked for me.

    I also had one more mistake - I had changed the variable Connection to Conn
    except in the
    Recordset.Open SQL, Conn
    statement

    --
    Anil Gupte
    www.keeninc.net
    www.icinema.com

    "M. Savas Zorlu" <> wrote in message
    news:%...
    >I have recently upsized my access db to MSSQL too and here is the
    >connection string I use and it is working fine. Might help:
    >
    > strConnString="provider=sqloledb;server=SE\SQLEXPRESS;uid=USER;pwd=PASSWORD;database=DB;"
    >
    > set my_Conn = Server.CreateObject("ADODB.Connection")
    > my_Conn.Errors.Clear
    >
    > my_Conn.Open strConnString
    >
    > Anil Gupte wrote:
    >> I believe it is an ASP question because I can connect fine in other
    >> languages including C#/ASP.Net and VB.Net It is only in ASP that the
    >> connection strings are not working, so there must be a syntax issue.
    >>
    >> Thanx,
     
    Anil Gupte, Nov 19, 2007
    #14
  15. Anil Gupte

    Anil Gupte Guest

    Resolved - see answer to Zorlu

    Thanx for your help.
    --
    Anil Gupte
    www.keeninc.net
    www.icinema.com

    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > You still have yet to show us the line that produces that error. If the
    > Connection.Open statement does not throw an error, then there is nothing
    > wrong with your connection string. I do not blieve the Connection.Open
    > statement can throw that particular error ... wait ... "Connection" may be
    > a reserved keyword. Try calling your connection object "cn" or "conn" or
    > something like that.
    >
    > The only other possibility is that the Connection.Open statement actually
    > IS throwing an error, but you have an "on error resume next" statement
    > earlier in your code that is masking te error. Make sure you comment out
    > that statement so you can see if the Open statement produces an error. If
    > it does, show us THAT error message.
    >
    > Anil Gupte wrote:
    >> Yes, I have that earlier in the file
    >> Dim Connection
    >> Set Connection=Server.CreateObject("ADODB.Connection")
    >>
    >> sConnString = "Provider=SQLNCLI.1;Integrated Security=SSPI;Persist
    >> Security Info=False;Initial Catalog=iMedia;Data
    >> Source=IMEDIA/SQLEXPRESS" ' Tried both the above and the below - no
    >> luck sConnString = "Provider=SQLOLEDB;Integrated Security=True;Initial
    >> Catalog=IMEDIA;Data Source=.\SQLEXPRESS"
    >> Connection.Open sConnString
    >> Dim Recordset 'Here we declare our variable that will hold our new
    >> object Set Recordset=Server.CreateObject("ADODB.Recordset")
    >>
    >> Thanx for your help
    >>
    >> "Bob Barrows [MVP]" <> wrote in message
    >> news:...
    >>> So the "Connection.Open sConnString" statement didn't raise an
    >>> error? I don't see any code in which you tried to use the connection
    >>> object. Neither do I see a
    >>>
    >>> Set Connection = Createobject("adodb.connection")
    >>>
    >>> statement. Does one exist?
    >>>
    >>> Anil Gupte wrote:
    >>>> 0xE7D:The connection cannot be used to perform this operation. It is
    >>>> either closed or invalid in this context.
    >>>>
    >>>>
    >>>> "Bob Barrows [MVP]" <> wrote in message
    >>>> news:OG%...
    >>>>> Anil Gupte wrote:
    >>>>>> I am using the following to try to connect to the database, but it
    >>>>>> does not seem to be working.
    >>>>>>
    >>>>>> Dim sConnString
    >>>>>> sConnString = "Provider=SQLNCLI.1;Integrated Security=SSPI;Persist
    >>>>>> Security Info=False;Initial Catalog=MediaDB;Data
    >>>>>> Source=MEDIAMACHINE\SQLEXPRESS" Connection.Open sConnString
    >>>>>> Dim Recordset
    >>>>>> Set Recordset=Server.CreateObject("ADODB.Recordset")
    >>>>>>
    >>>>>> Can someone tell me the correct syntax? The original syntax
    >>>>>> (which worked) for an Access DB was as follows:
    >>>>>> sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
    >>>>>> Server.MapPath("/Music/DB/MediaDB.mdb")
    >>>>>>
    >>>>> You really need to show us the error message, but i will say that I
    >>>>> have had little success connecting to SQL2005 with integrated
    >>>>> security from ASP. I would suggest creating a sql login on your
    >>>>> server and using its user id and password in your connection string
    >>>>> instead of Integrated Security=SSPI
    >>>>>
    >>>>> --
    >>>>> 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"
    >>>
    >>> --
    >>> 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"

    >
    > --
    > 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"
    >
     
    Anil Gupte, Nov 19, 2007
    #15
    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. Loane Sharp
    Replies:
    1
    Views:
    497
    Loane Sharp
    Sep 25, 2005
  2. farseer

    SQL Server 2005 + SQL Server Express

    farseer, Aug 7, 2006, in forum: ASP .Net
    Replies:
    3
    Views:
    360
    farseer
    Aug 8, 2006
  3. Replies:
    1
    Views:
    501
  4. Brad  Brening
    Replies:
    0
    Views:
    470
    Brad Brening
    Mar 1, 2007
  5. Jake Henderson

    Visual Web Developer 2005 Express and SQL 2005 Express

    Jake Henderson, Mar 10, 2006, in forum: ASP .Net Web Services
    Replies:
    0
    Views:
    261
    Jake Henderson
    Mar 10, 2006
Loading...

Share This Page