using a wsc from asp to query a database

Posted on 2006-04-07
Last Modified: 2010-05-18
Hey Guys,

I want to make use of WSC's in my asp-based website. One of the things I want to put in a component is all the stuff for accessing a database.
I would like to pass the DSN to the WSC, then execute an SQL statement through the component and have the component return a recordset.
The problem is the returning of the recordset. Here is my wsc-code:

<?xml version="1.0"?>
<?component error="true" debug="true"?>

      <property name="dsn">
      <property name="queryResult">
      <method name="executeSQL">
            <PARAMETER name="SQLstatement"/>
      <method name="clearConnection">
      <method name="generateUUID">

<script language="VBScript">

Dim dsn,queryResult,Connection

function get_dsn()
      get_dsn = dsn
end function

function put_dsn(newValue)
      dsn = newValue
end function

function get_queryResult()
      get_queryResult = queryResult
end function

function executeSQL(SQLstatement)
      Dim tmpQuery
      Set Connection = CreateObject("ADODB.Connection")
      Connection.ConnectionString = dsn
      Set tmpQuery = Connection.Execute(SQLstatement)
      executeSQL = tmpQuery
      'Set tmpQuery = Nothing
end function

function clearConnection()
      Set Connection = Nothing      
end function

function generateUUID()
'Generates a unique ID
      Dim TypeLib
      Set TypeLib = CreateObject("Scriptlet.TypeLib")
      generateUUID = TypeLib.Guid
      Set TypeLib = Nothing
end function


In the asp page I do the following:

Dim objWSC, Qresult
      ' instantiate object
      Set objWSC = CreateObject("dbaction.wsc")
      ' test generateUUID()
      response.write("generateUUID:" & objWSC.generateUUID())
      ' pass DSN to component
      response.write("Setting DSN...")
      objWSC.dsn = "Provider=Microsoft.Jet.OLEDB.4.0; data source="& server.mappath("\data\test.mdb")
      ' Query the set DSN
      response.write("get DSN:" & objWSC.dsn)
      ' Pass a query and display result
      SET Qresult = objWSC.executeSQL("SELECT COUNT(*) FROM Afkortingen")
      response.write("QueryResult:" & Qresult(0))
      SET Qresult = Nothing
      ' Second query
      SET Qresult = objWSC.executeSQL("SELECT * FROM Afkortingen")
      do until Qresult.EOF
            response.write(Qresult("Afkorting") & "<br/>")
      SET Qresult = Nothing
      ' clear the connection after all database action is finished
      ' Finished with the object
      Set objWSC = Nothing

Now the query for geting the COUNT works, the problem occurs after I want to return more than one record.
In the component I assign the resulting recordset and in my asp code I assign this result to "Qresult". I assumed I could then use Qresult as if it were a recordset. The problem is it isn't recognised as such. When I try to do recordset specific things like "movenext" or ".EOF" this doesn't work.
It seems asp doesn't recognise my object as a recordset.
If anyone can give me an example of a working code where a recordset is passed from the WSC I'm a happy man ;)

Thanks in advance,
Question by:ricosuave
    LVL 25

    Expert Comment

    what's the error?
    LVL 2

    Author Comment

    Hey kevp75,

    The error is in Dutch, because my developmentmachine is in dutch, but it is:

    Runtimefout Microsoft VBScript (0x800A01B6)
    Deze eigenschap of methode wordt niet ondersteund door dit object: 'EOF'
    /index.asp, line 41

    Which trandslates roughly into:

    Runtime error Microsoft VBScript (0x800A01B6)
    This property or method is not supported by this object: 'EOF'
    /index.asp, line 41

    LVL 25

    Expert Comment

    can you temporarily comment these lines out of your wsc file:
    function executeSQL(SQLstatement)
         Dim tmpQuery
         Set Connection = CreateObject("ADODB.Connection")
         Connection.ConnectionString = dsn
         'Set tmpQuery = Connection.Execute(SQLstatement)     <-this one
         'executeSQL = tmpQuery               <-this one 2
         'Set tmpQuery = Nothing
    end function

    and try it again
    LVL 2

    Author Comment

    Sorry for my non-reply. I only remembered the question after I got the abandonement mail.

    The comments I got here were not a solution.
    I figured it out in the end myself.
    The solution is to replicate the methods you need in the WSC you write.

    So, if you write a "replacement" recordset-object in WSC, and you need a "movenext" you need to write a "movenext" in your WSC that passes this through to the recordset object.

    This solved it for me, none of the expert advices were any help I'm afraid.


    Accepted Solution

    PAQed with points refunded (200)

    Community Support Moderator

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
    I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now