Visual Basic COM objects for MTS

Lucutus

Hey Folks, Help required!

I'm writing an MTS com object in VB for ASP use. I want to access a
database within a function in the com and return the recordset to the ASP
for formatting and display to user.

I can return the recordset in a Variant Array, but this is really slow. Any
ideas how I can get around this? Can it be done? I want to be able to use
the usual ADO recordset commands like .movenext in my ASP.

Thanks in advance for anyone who wants to have a go! Code follows...(watch
for wrap)


Function GetSatelliteNews() As Recordset

Dim sSQL As String
Dim iId As Integer

'Change the number of this int to display the corresponding news items
iId = 1

sSQL = "SELECT NewsId, AuthorID, Date, Title, Source, Text From News Where
SubjectID =" & iId & " Order By Date desc"
Dim rs As New ADODB.Recordset
rs.CursorType = adOpenStatic
Const sConnect = "DSN=News;UID=*****;PWD=*****;"
rs.Open sSQL, sConnect
GetSatelliteNews = rs.GetRows
rs.Close
End Function

---

Lucutus of Borg

kayos

Try disconnecting the recordset and passing back the recordset object from the function.
This can be passed across to your calling function then just work away.
Function GetSatelliteNews() As Recordset

Dim sSQL As String
Dim iId As Integer

'Change the number of this int to display the corresponding news items
iId = 1
Const sConnect = "DSN=News;UID=*****;PWD=*****;"

sSQL = "SELECT NewsId, AuthorID, Date, Title, Source, Text From News Where
SubjectID =" & iId & " Order By Date desc"
Dim rs As New ADODB.Recordset
rs.cursorlocation = aduseclient
rs.CursorType = adOpenStatic
rs.locktype = adlockreadonly
dim conn as new adodb.connection
conn.open sConnect
rs.Open sSQL,conn
set rs.activeconnection = nothing
conn.close
set conn = nothing
GetSatelliteNews = rs
end function

Bard

I received the suggestion that you might "try to open the rs as OpenDynamic."

Any help?

---

Bard

|home page|scary éire

Bard

Ok, here's another - possibly more helpful -(?) solution...

--

he doesn't have to WRAP a Recordset into a VB activeX.dll to use a recordset
he can actually inistaiate an ADO recordset in ASP
Why do this
1. its faster no messing about with variant arrays
2. he can Loop through the entire Recordset Start to finish using a do while not EOF loop

Suggestion
rather than use a Function make a class Called clsSatelliteNews
which is a recordset
all the Properties of this class mimic the Properties of Recordset
and the Method he wants to use could be called ReturnNews
it would return the values he wants with getRows
why do this
1. Very Fast Objects also persist in memory

Help... Object Coding in VB
to create OO projects in VB needs some advanced VB skills
so if he isn't used to it use the first method
However you can get really good books on it and there are tonnes of resources...
As a rule of thumb any Kind of Data access on the Web using ASP/VB is called N tier and uses Objects

--

Hope that helps

---

Bard

|home page|scary éire

Lucutus

rofl. Okay, Yes I know how to initiate a recordset within an ASP, and yes I know it's fast and does everything I want it to do as far as returning directly the data I want to use. However, there is a reason I'm trying to wrap this up in an MTS Com, the main one being that this function is part of a bigger application I'm writing, and I'm wrapping all the database access up in it, for reasons *smile*.

So, that email from your friend doesn't help at all, it's just stating the obvious really. Thanks for the effort tho, appreciated.

Back to my original question. I've tried the two suggestions above and I keep getting this error...



If this pop out at anyone who knows about such things, throw me a response here! (or email!)

Thanks

---

Lucutus of Borg

Bard

Thanks again for your efforts anyway Niall, good luck tomorrow.

Cheers, T... I'm sure it'll be fun!... like Quake... sort of, but with real, albeit less destructive, weapons...

(/me is going paintballing tomorrow- get to shoot my boss... probably... which is always a good thing... I guess )

---

Bard

|home page|scary éire

Lucutus

Okay, I managed to make this fly, with a little help from X_OR.

I was so close, all I needed was ADODB.Recordset, instead of simply Recordset!

for the interested...

Public Function GetSpecialNews() As ADODB.Recordset
Dim Conn As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim sSQL As String
Dim iId As Integer

'Change the number of this int to display the corresponding news item
iId = 1

' Create instance of connection object and then open the
' connection.
Set Conn = New ADODB.Connection
Conn.Open "DSN=News;UID=****;PWD=****;"

' Create instance of recordset object and open the
' recordset object against a table.
Set Rs = New ADODB.Recordset

' Setting the cursor location to client side is important
' to get a disconnected recordset.
Rs.CursorLocation = adUseClient

sSQL = "SELECT NewsId, AuthorID, Date, Title, Source, Text From News Where SubjectID =" & iId & " Order By Date desc"

Rs.Open sSQL, Conn, ADODB.adOpenForwardOnly, ADODB.adLockBatchOptimistic

' Get the value of one of the fields from the recordset for test
Dim v
v = Rs.Fields(0).Value
MsgBox v

Set GetSpecialNews = Rs

End Function

Woohoo!

---

Lucutus of Borg

Bard

Hope the suggestions from the Cyrona expert were of some help

Glad you got it sorted!

---

Bard

|home page|scary éire

Lucutus

Unfortunatly No, your Cyrona 'expert' basically described to me the working code that I'd already written, as the mail I sent both of you outlines, and the code above shows, what I wanted to do was not, as you 'expert' suggested, return a Variant, but return a recordset.

Thanks again for your efforts anyway Niall, good luck tomorrow.

---

Lucutus of Borg

ecksor

Originally posted by Lucutus:


Okay, I managed to make this fly, with a little help from X_OR.

Ya come in late one morning and the whole place is falling down! :P

Lucutus

I Love You X_OR!

---

Lucutus of Borg