how to use connection/recordset class

I read Thinker's thread (here) (http://www.xtremevbtalk.com/showthread.php?threadid=34799) but I'm getting a little confused:

How do I use the recordset object

Public Function Query(ByVal QueryString As String) As ADODB.Recordset
Dim rsData As ADODB.Recordset

Set Query = Nothing

If blnConnected Then

Set rsData = New ADODB.Recordset
cnxDataBase.Open

With rsData
Set .ActiveConnection = cnxDataBase
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
.Source = QueryString
.Open
Set .ActiveConnection = Nothing
End With

cnxDataBase.Close
Set Query = rsData
Set rsData = Nothing
End If

End Function

I thought to use the following function for the login :
Private Function checkLogin(ByVal strUN As String, ByVal strPW As String) As Boolean
Dim clDB As New DBCaQ
clDB.Query ("SELECT * FROM tblPersoneel WHERE Username = '" & strUN & " AND Password = '" & strPW & "'")
End Function

Private Sub cmdOK_Click()
'check for correct password
If txtPassword.Text <> "" Then
Dim clDB As New DBCaQ
If clDB.Connected = False Then
clDB.Connect "sa", "", "zepv2", "HP"
End If
End If

OK = checkLogin(Trim(txtUserName.Text), Trim(txtPassword.Text))
If OK = True Then
Me.hide
Else
MsgBox "Wrong username or password!", vbExclamation, "Login...."
End If
End Sub

NOTE : OK is a public boolean!
Now I don't know how to use the recordset object from his class file :( .
It look's like i'm a noob ;)

Hi,

If I get it right:


Dim clDB As New DBCaQ
Dim rs As ADODB.Recordset
Set rs= clDB.Query ("SELECT * FROM tblPersoneel WHERE Username = '" & strUN & " AND Password = '" & strPW & "'")


Shurik.

Hi,

If I get it right:


Dim clDB As New DBCaQ
Dim rs As ADODB.Recordset
Set rs= clDB.Query ("SELECT * FROM tblPersoneel WHERE Username = '" & strUN & " AND Password = '" & strPW & "'")


Shurik.

Yeah, thought also that way but I tried it this way:
Private Function checkLogin(ByVal strUN As String, ByVal strPW As String) As Boolean
Dim clDB As New DBCaQ
Set objRs = New ADODB.Recordset
Set objRs = clDB.Query("SELECT * FROM tblPersoneel WHERE Username = '" & strUN & " AND Password = '" & strPW & "'")
With objRs
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
End With
If objRs.RecordCount > 0 Then
MsgBox "Login succeeded!"
Else
MsgBox "Login failes!"
End If

End Function

to get some output but I get an error message : "Object variabele or With Block variabele not set!"

Thanks for your quick reply although!!!

Please try this:

Private Function checkLogin(ByVal strUN As String, ByVal strPW As String) As Boolean

Dim clDB As New DBCaQ
Dim objRs As ADODB.Recordset
Set objRs = clDB.Query("SELECT * FROM tblPersoneel WHERE Username = '" & strUN & " AND Password = '" & strPW & "'")
With objRs
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
End With
If objRs.RecordCount > 0 Then
MsgBox "Login succeeded!"
Else
MsgBox "Login failes!"
End If

End Function

sorry Shurik12 but still no success...it gives the above mentioned error on the line ".CursorLocation = adUseClient"

:rolleyes:

What completely escaped my attention is that you are setting the cursor type and location AFTER opening the recordset (which is should be vice versa otherwise it won't take effect).

Also change

Dim objRs As ADODB.Recordset
to
Dim objRs As New ADODB.Recordset

Regards,
Shurik.

Thanks shurik, that worked out :p

Anyway, i had to change all because the function i've written doesn't work at all --> there's no boolean as outcome :(


Private Function checkLogin(ByVal strUN As String, ByVal strPW As String) As Boolean

Dim clDB As New DBCaQ
Dim objRs As New ADODB.Recordset
Set objRs = clDB.Query("SELECT * FROM tblPersoneel WHERE Username = '" & strUN & "' AND Password = '" & strPW & "'")
With objRs
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
End With

End Function


Anny suggestion on that??? (how to get a boolean out of that function???)

I've sorted it out already......
Shurik12, many thanks for your replies..... :D

Good, one thing though (I already told about):
Setting .CursorLocation = adUseClient and .CursorType = adOpenDynamic
AFTER opening the recordset doesn't make sense, it doesn't change anything...

Regards,
Shurik.

Shurik12,

You're right...i had already changed it but copied/pasted the code from my for-last post ;)

I am used to work with a module to create connection and fill a recordset but instead of that workaround i tried to use the class file....and for now, I'm not very sure about what the difference is or which methode is better....

Just a suggestion....

Instead of wrapping your connection string within a boolean function, you can get the same boolean functionality and better overall control by using the WithEvents method. If you create a new ADODB connection object via WithEvents, you can trap events such as UpdateCompleted, Close and Open events, etc. There are several good articles on this..do an MSDN search. Good luck.

MKoslof,

Thanks for your advise ;) but i already know quite a few about ADO.
The thing with withEvents methode is that you have to declare it in each form. I know it's a better solution to trap willComplete etc..

In this particular case i creat a connection in a class file and per sub or function just check if it's already been made or open or eventhough, closed after a certain inactivity-time :rolleyes: