Problem returning Recordset

Hi

I am trying to return a recordset from a function in a class back to my form in order to display the data.

Code is as follows:

In my Form -

Dim bs as New Bookstore
Public rsResult = New ADODB.Recordset

Private Sub mnuBook_Click()

Dim strIsbn As String

strIsbn = InputBox("Enter the ISBN", "Search by ISBN")
If strIsbn = "" Then
MsgBox "No ISBN entered", vbOKOnly, "Search"
Else
Showdata(bs.findbyIsbn(strIsbn))
End If

End Sub

Private Sub Showdata(rsResult As ADODB.Recordset)
txtIsbn.Text = rsResult.Fields("ISBN")
(...display into all my other text boxes....)

End Sub

and in my Bookstore Class:

Public Function findbyIsbn(strIsbn As String) As ADODB.Recordset
Dim rsResult As New ADODB.Recordset
Dim strSQL As String

strSQL = "SELECT Author.*, Books.* FROM Author INNER JOIN Books ON Author.[Author Code] = Books.[Author Code] WHERE (Books.ISBN)='" & strIsbn & "';"

rsResult.Open strSQL, mcon1, adOpenDynamic, adLockOptimistic
Set findbyIsbn = rsResult
End Function


The problem is I get a Type Mismatch error on the - Showdata(bs.findbyIsbn(strIsbn)) - line of code in my form

Its probably something really simply, but I fairly new to VB and any advice would be appreciated

Cheers

This is how i do it.


in the DLL/Class Module

VB Code:

Function GetAllEmployees() As Object 'Set The returned Recordset as an Object
Dim CN As New ADODB.Connection
Dim RS As New ADODB.Recordset
CN.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=Northwind"
CN.CursorLocation = adUseClient
RS.Open "SELECT * FROM Employees", CN, adOpenStatic, adLockOptimistic
Set GetAllEmployees = RS 'This Retunrs the REcordset to the Function
Set RS = Nothing
Set CN = Nothing
End Function

Then in Your Form something like this.

VB Code:

Private Sub Command1_Click()
Dim Rs As New ADODB.Recordset
Dim MyRS As New SQLServerDLL.Interface ' Set refrences to ADO and your DLL
Set Rs = MyRS.GetAllEmployees ' Set you Recordset = The recor returned by your Function
MSFlexGrid1.Cols = Rs.Fields.Count
MSFlexGrid1.Rows = Rs.RecordCount + 1
 
Do Until Rs.EOF ' Fill in a FlexGrid with the info, or do whatever you would like.
   DoEvents
    For i = 0 To Rs.Fields.Count - 1
      MSFlexGrid1.TextMatrix(0, i) = Rs(i).Name
      MSFlexGrid1.TextMatrix(lRow, i) = "" & Rs(i).Value
    Next
 lRow = lRow + 1
 Rs.MoveNext
Loop
End Sub

Thanks for that reply

But even when I change the return of the function as an Object, I still get the same error when I call the Showdata method in my form

Surely there is a simple way to display the returned recordset to text boxes on a form (as in my case)???

Yah there is a simple way... and i just showed it to you.. It doesnt get more simple than my example.



Let me change it so its adding the items to textboxes instead of a FlexGrid.

This is in your DLL

VB Code:

Function GetAllEmployees() As Object 'Set The returned Recordset as an Object
Dim CN As New ADODB.Connection
Dim RS As New ADODB.Recordset
CN.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=Northwind"
CN.CursorLocation = adUseClient
RS.Open "SELECT * FROM Employees", CN, adOpenStatic, adLockOptimistic
Set GetAllEmployees = RS 'This Retunrs the REcordset to the Function
Set RS = Nothing
Set CN = Nothing
End Function

This is in your Form..

VB Code:

Private Sub Command1_Click()
Dim Rs As New ADODB.Recordset
Dim MyRS As New SQLServerDLL.Interface ' Set refrences to ADO and your DLL
Set Rs = MyRS.GetAllEmployees ' Set you Recordset = The recor returned by your Function
 
Text1=Rs!LastName
Text2=Rs!FirstName
etc....
End Sub

Quote:

---

Originally posted by mickysavage
Hi

I am trying to return a recordset from a function in a class back to my form in order to display the data.

Code is as follows:

In my Form -

Dim bs as New Bookstore
Public rsResult = New ADODB.Recordset

Private Sub mnuBook_Click()

Dim strIsbn As String

strIsbn = InputBox("Enter the ISBN", "Search by ISBN")
If strIsbn = "" Then
MsgBox "No ISBN entered", vbOKOnly, "Search"
Else
Showdata(bs.findbyIsbn(strIsbn))
End If

End Sub

Private Sub Showdata(rsResult As ADODB.Recordset)
txtIsbn.Text = rsResult.Fields("ISBN")
(...display into all my other text boxes....)

End Sub

and in my Bookstore Class:

Public Function findbyIsbn(strIsbn As String) As ADODB.Recordset
Dim rsResult As New ADODB.Recordset
Dim strSQL As String

strSQL = "SELECT Author.*, Books.* FROM Author INNER JOIN Books ON Author.[Author Code] = Books.[Author Code] WHERE (Books.ISBN)='" & strIsbn & "';"

rsResult.Open strSQL, mcon1, adOpenDynamic, adLockOptimistic
Set findbyIsbn = rsResult
End Function


The problem is I get a Type Mismatch error on the - Showdata(bs.findbyIsbn(strIsbn)) - line of code in my form

Its probably something really simply, but I fairly new to VB and any advice would be appreciated

Cheers

---

First of al... get rid of the parenthesis:

VB Code:

Showdata bs.findbyIsbn(strIsbn)

A tip... for everybody. Try avoidin using

VB Code:

Dim variable As New VariableType

Use this instead:

VB Code:

Dim variable As VariableType
Set variable = New VariableType

Quote:

---

Originally posted by Mc Brain
A tip... for everybody. Try avoidin using

VB Code:

Dim variable As New VariableType

Use this instead:

VB Code:

Dim variable As VariableType
Set variable = New VariableType

---

Declaring variables As a new instance of an object, forces the compiler to control in each operation if the object has been previously created. So... this is what your "compiled code" would look like (Everyone know that it won't look like that... but it will be coded as if this is the source of your program)
http://www.vbforums.com/attachment.p...postid=1052123