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>
<?component error="true" debug="true"?>
<registration
      description="dbaction"
      progid="dbaction.WSC"
      version="1.00"
      classid="{f1700385-f8c0-4f5e-a001-89539e1a5224}"
>
</registration>

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



<script language="VBScript">
<![CDATA[

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
      Connection.open
      Set tmpQuery = Connection.Execute(SQLstatement)
      executeSQL = tmpQuery
      'Set tmpQuery = Nothing
end function

function clearConnection()
      Connection.close()
      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

]]>
</script>
</component>

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())
      response.write("<br/>")
      
      ' pass DSN to component
      response.write("Setting DSN...")
      response.write("<br/>")
      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)
      response.write("<br/>")
      
      ' 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/>")
      Qresult.MoveNext
        loop
      SET Qresult = Nothing
      
      ' clear the connection after all database action is finished
      objWSC.clearConnection()
      
      ' 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,
Erik
LVL 2
ricosuaveAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kevp75Commented:
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

Erik
kevp75Commented:
ok.
can you temporarily comment these lines out of your wsc file:
function executeSQL(SQLstatement)
     Dim tmpQuery
     Set Connection = CreateObject("ADODB.Connection")
     Connection.ConnectionString = dsn
     Connection.open
     '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.

Erik
GranModCommented:
PAQed with points refunded (200)

GranMod
Community Support Moderator

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.