Ado.Net SqlConnection in Classic ASP

Discussion in 'ASP General' started by techno, Feb 28, 2010.

  1. techno

    techno Guest

    Hi Guys,

    I am having tough time since 2 days, and not able to figure out what
    should i do with my implementation. I am not sure if this could be
    really workable. Please help me on this.

    Below is my scenario:

    1. I have a .Net Dll which has a method that returns a SqlConnection
    object after opening it. Below is the function (similar to which i am
    using - this is a sample function)

    SqlConnection conn = new SqlConnection();

    conn.ConnectionString = @"Integrated Security=SSPI;Persist Security
    Info=False;Initial Catalog=myDatabase;Data Source=.
    \SQLEXPRESS;Trusted_Connection=true";

    conn.Open();

    2. I am installing this .dll in GAC using a private key.
    3. Then registering it using regasm.exe utility which exports it as
    tlb.
    4. Finally i am able to access this .net dll from my ASP application
    and able to get the return value from the method that i am accessing
    using the .dll class object. (There is certainly no issue in accessing
    the string value from a method, but this method i am trying to access
    the SqlConnection ado.net object)

    Below is my sample ASP Page to show how i am accessing and using the
    connection object:

    <!-- #include file="adovbs.inc" -->
    <%
    set objdll = Nothing
    set objConn = Nothing

    //creating class object from the .net dll
    set objdll = Server.CreateObject("gacDemo.clsGacDemo")

    //accessing the GetConnection() method using the object.
    //GetConnection() returns the SQLConnection ado.net object
    objConn = objdll.GetConnection()

    set objRS = Server.CreateObject("ADODB.Recordset")
    objRs.Source = "SELECT COUNT(*) AS CityCount FROM city"
    objRS.Open "select count(*) as count from city", objConn, 3, 3, 1

    Count = objRS.Fields("count").value
    response.Write Count

    %>

    I am getting this bad error which i am trying to fix it,

    Error Type:
    ADODB.Recordset (0x800A0BB9)
    Arguments are of the wrong type, are out of acceptable range, or are
    in conflict with one another.

    I am not sure if i can use the SqlConnection ado.net object in asp
    page.
    techno, Feb 28, 2010
    #1
    1. Advertising

  2. techno

    Bob Barrows Guest

    techno wrote:
    > Hi Guys,
    >
    > I am having tough time since 2 days, and not able to figure out what
    > should i do with my implementation. I am not sure if this could be
    > really workable. Please help me on this.
    >
    > Below is my scenario:
    >
    > 1. I have a .Net Dll which has a method that returns a SqlConnection
    > object after opening it. Below is the function (similar to which i am
    > using - this is a sample function)
    >
    > SqlConnection conn = new SqlConnection();
    >
    > conn.ConnectionString = @"Integrated Security=SSPI;Persist Security
    > Info=False;Initial Catalog=myDatabase;Data Source=.
    > \SQLEXPRESS;Trusted_Connection=true";
    >
    > conn.Open();
    >
    > 2. I am installing this .dll in GAC using a private key.
    > 3. Then registering it using regasm.exe utility which exports it as
    > tlb.
    > 4. Finally i am able to access this .net dll from my ASP application
    > and able to get the return value from the method that i am accessing
    > using the .dll class object. (There is certainly no issue in accessing
    > the string value from a method, but this method i am trying to access
    > the SqlConnection ado.net object)
    >
    > Below is my sample ASP Page to show how i am accessing and using the
    > connection object:
    >
    > <!-- #include file="adovbs.inc" -->
    > <%
    > set objdll = Nothing
    > set objConn = Nothing
    >
    > //creating class object from the .net dll
    > set objdll = Server.CreateObject("gacDemo.clsGacDemo")
    >
    > //accessing the GetConnection() method using the object.
    > //GetConnection() returns the SQLConnection ado.net object
    > objConn = objdll.GetConnection()
    >
    > set objRS = Server.CreateObject("ADODB.Recordset")
    > objRs.Source = "SELECT COUNT(*) AS CityCount FROM city"
    > objRS.Open "select count(*) as count from city", objConn, 3, 3, 1


    ???
    Why include the adovbs.inc file if you are not going to use the ADO
    constants it defines?
    This statement is kind of silly anyways. Why open a non-default cursor if
    all you want is the result of a "count(*)"? This resultset is not going to
    be updatable since it is the result of a calculation, and it only returns a
    single row, so you don't need scrollability. Just open a default
    forward-only read-only cursor:

    sql="SELECT COUNT(*) AS CityCount FROM city"
    Set objRS=objConn.Execute(sql,1)

    would make much more sense from an efficiency standpoint ... that is, it
    would make sense if objConn referred to an ADODB.Connection object.

    >
    > Count = objRS.Fields("count").value


    Oooh, Compounding the errors! Your recordset's source sql statement does not
    return a field called "count"
    Anyways, it's more efficient anyways to use the field's ordinal position to
    refer to it:

    Count=objRS.Fields(0).value

    or, if you have no reservations against taking advantage of default
    properties:

    Count=objRS(0)

    > response.Write Count
    >
    > %>
    >
    > I am getting this bad error which i am trying to fix it,
    >
    > Error Type:
    > ADODB.Recordset (0x800A0BB9)


    This is a huge hint. The error is being raised by the statement that opens
    your recordset ... not the statement that assigns the result of the call to
    the dll to the objConn variable.

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


    That's because you failed to use an object with type ADODB.Connection when
    attempting to open an _ADODB_ recordset.

    > I am not sure if i can use the SqlConnection ado.net object in asp
    > page.


    I am. You can't. Maybe, and I'm not really sure of this, your dll can be
    rewritten to use InterOp to instantiate an ADODB connection and return that
    instead.

    You might consider creating a .Net webservice that the asp page can use
    ServerXMLHTTPRequest to query for its data. Using InterOp, the webservice
    can return the xml that an ADODB recordset can use as its source. There are
    too many details for a newsgroup post so I suggest you do some research via
    the ADO documentation
    (http://msdn2.microsoft.com/en-us/library/ms675532.aspx), the .Net
    documentation and google.

    --
    Microsoft MVP - ASP/ASP.NET - 2004-2007
    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, Feb 28, 2010
    #2
    1. Advertising

  3. techno

    Bob Barrows Guest

    Bob Barrows wrote:

    I just noticed this:
    > //accessing the GetConnection() method using the object.
    > //GetConnection() returns the SQLConnection ado.net object
    > objConn = objdll.GetConnection()


    Your failure to use the Set keyword means that objConn does not even refer
    to a SQLConnection object. It MIGHT contain the connection string, which I
    think is the default propertof or that object, but it certainly does not
    refer to an object.

    > I.... Maybe, and I'm not really sure of this, your dll can
    > be rewritten to use InterOp to instantiate an ADODB connection and
    > return that instead.
    >


    However, it would probably be better if your dll returned the information in
    a string that can be used to open an Adodb connection object:

    sConn = objdll.GetConnectionString()
    set objConn=createobject("adodb.connection")
    objConn.Open sConn

    --
    Microsoft MVP - ASP/ASP.NET - 2004-2007
    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, Feb 28, 2010
    #3
    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. MSFT
    Replies:
    0
    Views:
    397
  2. nita
    Replies:
    1
    Views:
    851
    Saravana
    Nov 20, 2004
  3. ronaldlee

    Transfer ADO Code to ADO.NET

    ronaldlee, Dec 17, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    443
    Kevin Spencer
    Dec 17, 2004
  4. jason
    Replies:
    1
    Views:
    456
    Kevin Spencer
    May 9, 2005
  5. Replies:
    2
    Views:
    428
    tfsmag
    Jul 20, 2006
Loading...

Share This Page