[RESOLVED] ADODB. How to use?

[vbdevil]

Anybody can point it out to me how I'm going to perform multiple query with once conn by using adodb.
As code been highlighted below has throw me an exception error, "operation is not allow when object is open"

Try
cnn.Open("Provider=SQLOLEDB.1;Password=sa;Persist Security Info=True;User ID=sa;Initial Catalog=Optimizer2;Data Source=LAM")
Catch exc As Exception
DisplayErrorMsg(DatabaseErrorMsg)
End Try

Dim qry As String = "SELECT PASSWORD FROM S_USER WHERE USER_ID='" & sUserName _
& "' AND PASSWORD_EXPIRE_DATE > = '" & CurrDate & "'"
rstRecord.Open(qry, cnn, 3, 3)

If rstRecord.RecordCount = 0 Then
'AttemptCount = AttemptCount + 1
Dim SelectCountQry As String = "SELECT FAIL_COUNT FROM S_USER WHERE USER_ID='" _
& sUserName & "'"
rstRecord.Open(SelectCountQry, cnn, 3, 3)
Dim nFailCount As Integer = rstRecord.Fields("FAIL_COUNT").Value
Dim nNewFailCount As Integer = nFailCount + 1
If (nFailCount < 3) Then
Dim UpdateCountQry As String = "UPDATE S_USER SET FAIL_COUNT='" & nNewFailCount & _
"' WHERE USER_ID ='" & sUserName & "'"
Dim AttemptsErrorMsg As String = "You have used" & nNewFailCount _
& "out of 3 attempts. The application will been closed if you fail at your last attempt login."
DisplayErrorMsg(AttemptsErrorMsg)
ElseIf nFailCount = 3 Then
Dim LastAttemptsErrorMsg As String = "You have used" & nFailCount _
& "out of 3 attempts. Please consult your sdministrator for further information."
DisplayErrorMsg(LastAttemptsErrorMsg)
End If
'DisplayErrorMsg(NotValidUsrMsg)


Thanks.

[RobDog888]

You've already opened it earlier.

You should really be using ADO.NET instead since your using VB.NET. ;)
rstRecord.Open(qry, cnn, 3, 3)

[vbdevil]

I know. I'm newbie of VB.net. this method is recommended by my friend. I know the conn is open, but from the code I posted above, if I want to perform another query as stated as above is

Dim SelectCountQry As String = "SELECT FAIL_COUNT FROM S_USER WHERE USER_ID='" _

Am i using the same statement as

rstRecord.Open(qry, cnn, 3, 3)

But, my second query string is SelectCountQry instead of qry. So, I need to write as

qry = "SELECT FAIL_COUNT FROM S_USER WHERE USER_ID='" _

to perform my second query in the same function.
I will going trying out ADO.net soon. But, I also need to solve this problem as well. Thanks.

[RobDog888]

then you need to .Close your recordset object after your done with using the first query. Then .Open it again using the second query.

Not the pretiest way to do it but it will get the job done.