[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 & "'"
               [B] rstRecord.Open(SelectCountQry, cnn, 3, 3)[/B]
                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.

[b]rstRecord[/b].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.