Overflow Error Opening ADO Recordset 
Author Message
 Overflow Error Opening ADO Recordset

I am receiving an unhandled vb6 overflow error on an ADO(2.5)
recordset.open statement connected to SQL Server 2000(SP2).
Some hopefully useful notes...
-this error only occurs in the compiled .exe on a different windows
2000 machine, not on the development windows 2000 machine (.exe or
ide).
-the error should be handled considering there is an on error goto at
the beginning of the procedure, however this statement is ignored and
the program exits ungracefully.
-Break on unhandled errors is selected in the general options tab.
-Same version MDAC both machines.

Any help would be greatly appreciated,
Justin

Reference Code: ------------------------------------------

Private Sub xcmdOk_Click()
On Error GoTo xcmdOk_Click_err

    If Len(xtxtUser.Text) = 0 Then
        xtxtUser.xErrorState = True
        Exit Sub
    End If

    Dim rstLogin As Recordset
    Set rstLogin = New Recordset

    rstLogin.Open "SELECT * FROM tblUser " & _
    "WHERE fldUserName = '" & xtxtUser.Text & "'", gcnnSQLDB

    If Not rstLogin.BOF And Not rstLogin.EOF Then
        If xtxtPass.Text = rstLogin!fldUserPassword Then
            SaveSetting "Leonardo", "Login", "Lastuser", xtxtUser.Text
                gintUserID = rstLogin!fldUserID
                rstLogin.Close
'###############CULPRIT OPEN STATEMENT ############################
                rstLogin.Open "SELECT fldPermissionMenu FROM
tblPermissions INNER JOIN " & _
"(tblUser INNER JOIN tblUserPermissions ON tblUser.fldUserID = " & _
                "tblUserPermissions.fldUserID) ON
tblPermissions.fldPermissionID = " & _
                "tblUserPermissions.fldPermissionID WHERE
tblUser.fldUserID = " & _
                gintUserID & "AND fldPermissionMenu Is Not Null"
'##################################################################
                Do While Not rstLogin.EOF

                    frmMain.Controls(rstLogin!fldPermissionMenu).Enabled
= True
                    rstLogin.MoveNext

                Loop

                'Loop through and set all disabled menus to
descriptive caption...
                Dim ctlSetEnabled As Control
                For Each ctlSetEnabled In frmMain.Controls
                    If TypeOf ctlSetEnabled Is Menu Then
                        If Not ctlSetEnabled.Enabled Then
                            ctlSetEnabled.Caption = "Not Available"
                        End If
                    End If
                Next ctlSetEnabled

            Unload Me
        Else
            MsgBox "The password is incorrect.", , "Password"
            xtxtPass.Text = vbNullString
            xtxtPass.SetFocus
        End If
    Else
        MsgBox "The user name is invalid.", , "User name"
        xtxtUser.SetFocus
    End If

    rstLogin.Close
    Set rstLogin = Nothing

Exit Sub
xcmdOk_Click_err:

    Call subErrHandler("frmLogin", "xcmdOk_Click", err.Number,
err.Description)
    Resume Next

End Sub
-----------------------------------------------



Thu, 13 May 2004 08:44:10 GMT  
 Overflow Error Opening ADO Recordset
I have a couple of thoughts for you.

1. The problem is that you need to put a space character after the
gintUserID and the word "and". This should fix the problem.

2. Consider fully qualifying your types. Rather than

        Dim rstLogin as Recordset

you should do ...

        Dim rstLogin as ADODB.Recordset

This avoids so much confusion

3. When you run into a problem such as the one your have documented
here, put a breakpoint at the offending line, copy the code of the
select statement and paste it into the immediate window to evaluate the
string (preceeding the code with a '?'). This allows you to see exactly
what is being passed to the open method - makes debugging so much
easier.

Quote:

> I am receiving an unhandled vb6 overflow error on an ADO(2.5)
> recordset.open statement connected to SQL Server 2000(SP2).
> Some hopefully useful notes...
> -this error only occurs in the compiled .exe on a different windows
> 2000 machine, not on the development windows 2000 machine (.exe or
> ide).
> -the error should be handled considering there is an on error goto at
> the beginning of the procedure, however this statement is ignored and
> the program exits ungracefully.
> -Break on unhandled errors is selected in the general options tab.
> -Same version MDAC both machines.

> Any help would be greatly appreciated,
> Justin

> Reference Code: ------------------------------------------

> Private Sub xcmdOk_Click()
> On Error GoTo xcmdOk_Click_err

>     If Len(xtxtUser.Text) = 0 Then
>         xtxtUser.xErrorState = True
>         Exit Sub
>     End If

>     Dim rstLogin As Recordset
>     Set rstLogin = New Recordset

>     rstLogin.Open "SELECT * FROM tblUser " & _
>     "WHERE fldUserName = '" & xtxtUser.Text & "'", gcnnSQLDB

>     If Not rstLogin.BOF And Not rstLogin.EOF Then
>         If xtxtPass.Text = rstLogin!fldUserPassword Then
>             SaveSetting "Leonardo", "Login", "Lastuser", xtxtUser.Text
>                 gintUserID = rstLogin!fldUserID
>                 rstLogin.Close
> '###############CULPRIT OPEN STATEMENT ############################
>                 rstLogin.Open "SELECT fldPermissionMenu FROM
> tblPermissions INNER JOIN " & _
> "(tblUser INNER JOIN tblUserPermissions ON tblUser.fldUserID = " & _
>                 "tblUserPermissions.fldUserID) ON
> tblPermissions.fldPermissionID = " & _
>                 "tblUserPermissions.fldPermissionID WHERE
> tblUser.fldUserID = " & _
>                 gintUserID & "AND fldPermissionMenu Is Not Null"
> '##################################################################
>                 Do While Not rstLogin.EOF

>                     frmMain.Controls(rstLogin!fldPermissionMenu).Enabled
> = True
>                     rstLogin.MoveNext

>                 Loop

>                 'Loop through and set all disabled menus to
> descriptive caption...
>                 Dim ctlSetEnabled As Control
>                 For Each ctlSetEnabled In frmMain.Controls
>                     If TypeOf ctlSetEnabled Is Menu Then
>                         If Not ctlSetEnabled.Enabled Then
>                             ctlSetEnabled.Caption = "Not Available"
>                         End If
>                     End If
>                 Next ctlSetEnabled

>             Unload Me
>         Else
>             MsgBox "The password is incorrect.", , "Password"
>             xtxtPass.Text = vbNullString
>             xtxtPass.SetFocus
>         End If
>     Else
>         MsgBox "The user name is invalid.", , "User name"
>         xtxtUser.SetFocus
>     End If

>     rstLogin.Close
>     Set rstLogin = Nothing

> Exit Sub
> xcmdOk_Click_err:

>     Call subErrHandler("frmLogin", "xcmdOk_Click", err.Number,
> err.Description)
>     Resume Next

> End Sub
> -----------------------------------------------

--
Scott R. Quier
Don't cry because its over; smile because it happened.


Fri, 14 May 2004 07:16:49 GMT  
 Overflow Error Opening ADO Recordset
I don't know how this could be the problem because for one, there is a
space between the two.  Secondly, this would cause a syntax error,
which I am not receiving on the development machine, nor as an error
message on the test machine (overflow error).

The variable gintUserID and the word AND are separated as such:
   gintUserID & "AND

As for the var declaration qualification, I have considered this, but
until we reference more than one data access library it is just easier
to accept the default.  As far as I know there are no problems
associated with referencing ADO as a default when there is no other DB
libraries referenced.

Any other ideas?

Thanks again,
Justin



Mon, 17 May 2004 12:22:36 GMT  
 Overflow Error Opening ADO Recordset
He means...
    gintUserID & "AND
should become...
    gintUserID & " AND

Bill


: I don't know how this could be the problem because for one, there is a
: space between the two.  Secondly, this would cause a syntax error,
: which I am not receiving on the development machine, nor as an error
: message on the test machine (overflow error).
:
: The variable gintUserID and the word AND are separated as such:
:    gintUserID & "AND
:
: As for the var declaration qualification, I have considered this, but
: until we reference more than one data access library it is just easier
: to accept the default.  As far as I know there are no problems
: associated with referencing ADO as a default when there is no other DB
: libraries referenced.
:
: Any other ideas?
:
: Thanks again,
: Justin



Sun, 23 May 2004 00:11:56 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Open ADO recordset on another ADO recordset - possible?

2. Opening ADO recordset error help !

3. Error opening ADO recordset hitting Access 2000

4. ADO Method Open recordset Error !!!

5. open ADO recordset - Error

6. Error: Opening ADO-Recordset

7. Opening ADO recordset error ??

8. Error 3709 VB opening ADO recordset

9. ADO fails to release Recordset cursor after opening disconnected Recordset

10. Opening an ADOX Catalog Whilst ADO Recordset is Open

11. COM+ overflow error with empty recordset.

12. Overflow Error Setting ADODB Recordset

 

 
Powered by phpBB® Forum Software