using a wsc from asp to query a database

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,
Who is Participating?
PAQed with points refunded (200)

Community Support Moderator
what's the error?
ricosuaveAuthor Commented:
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

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
ricosuaveAuthor Commented:
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.