Solved

ADO recordset is nothing

Posted on 2001-07-22
12
2,768 Views
Last Modified: 2013-11-25
Can someone tell me when a recordset will return 'nothing' and when a recordset will return no record (i.e. both BOF and EOF are true)? It appears for some of my procedures the recordsets return NOTHING sometimes and for some other routines they returns BOF and EOF.  To avoid the confusion, I use the routine as follow:

...
dim rs as adodb.recordset
set rs = createobject("ADODB.recordset")
...
rs.open mySQLstatement, adoConn
..
If not rs is nothing then
    do while not (rs.eof or rs.bof)
       xxx
       xxx
    loop
else
    err.raise  xxxxxx
end if


It is OK but I find it a little bit too cumbersome. Is there a better way to handle it?

Thanks in advance,
Wai-man
0
Comment
Question by:waiman
12 Comments
 

Expert Comment

by:Alon_h
ID: 6307384
Hi waiman

Try this...

...
dim rs as adodb.recordset
set rs = createobject("ADODB.recordset")
...
rs.open mySQLstatement, adoConn
...
while not rs.eof
   xxx
   xxx
   rs.MoveNext
loop




0
 
LVL 5

Expert Comment

by:GeoffKell
ID: 6307446
By creating the recordset with your statement

set rs = createobject("ADODB.recordset")

will ensure that the recordset object exists (i.e. it is not Nothing)

When the recordset is opened the BOF and EOF properties will reflect the state of the current data in the recordset.

The only time I'm aware of a recordset being Nothing is when using ADO to return multiple recordsets and when the last recordset has been processed and

Set newrs = rs.NextRecordset

the newrs object is returned as Nothing

Hope this helps to shed some light

Regards
GK
0
 
LVL 22

Expert Comment

by:CJ_S
ID: 6307754
On addition to GeoffKell.
When the recordset contains no data both eof and bof will be true.
0
 

Expert Comment

by:ashish_1319
ID: 6307802
Try this,
if recordset.State = adStateOpen then
  if recordset.recordcount > 0 then
     xxx
     xxx
     xxx
  end if
end if
0
 
LVL 17

Expert Comment

by:inthedark
ID: 6307825
In simple terms RS is Nothing if the SQL was faulty.  But because you used the set rs = createobject("ADODB.recordset") the object will always exists and never be nothing. In your case if there is a problem with the DB this code will crash.

A method for avoiding this problem is to use the connection object to create the recordset. I have inclduded some samples how to do this below.

Once you have opened a recordset you can ignore the the BOF property beacse you will be positioned at the first record or at the end of the file.

I am sure you will agree that as a computer programmer your objectives should be to receive zero support calls.
 
I access ADO through a class module to make life simple and nerver get bugs.

I called the class ADOSubLib and will post the code below.

First create a class module then paste the class below and save as ADOSubLib.CLS in your common project conents folder where you save the items which you add to all projects.

To use the code. The the ADOSubLib.cls to your project. In your form declarations you need the following:

Dim ADO as New ADOSubLib

In any sub you can then say:

Dim SQL as string
Dim CN as ADODB.Connection
Dim RS as ADODB.Recordset
Dim Ok as Boolean

' In your formload you can register a connection string to SQL oracle or Access database.

Here is a sample form load event:

Private Sub Form_Load()

Dim CN As ADODB.Connection
Dim RS As ADODB.Recordset
Dim SQL$
Dim Ok

' Register your ODBC connection string
ADO.Value = "driver={SQL Server};server=MyServer;uid=Administrator;pwd=MyPass;database=MyDB"

' or
ADO.RegisterConnectionString ("Your ODBC String")

' or to connect to an access database
ADO.MDBConnect ("MyDB.MDB")


Ok = ADO.ConnectionOK(CN)
If Not Ok Then
    MsgBox ADO.GetLastError(CN) + vbCrLf + "Connection: " + ADO.Value, vbExclamation, "ODBC Connection Error"
Else
    MsgBox "Connected OK"
End If
   
End Sub

'====================================

Here is a read recordset sample:

Private Sub Command1_Click()

Dim SQL As String
Dim CN As ADODB.Connection
Dim RS As ADODB.Recordset
Dim Ok As Boolean

' In your formload you can register a connection string to SQL oracle or Access database.

e.g.

ADO.RegisterConnectionString ("Your ODBC String")
' or to connect toan access database
ADO.MDBConnect ("MyDB.MDB")

' to open a connection:

Ok = ADO.ConnectOK(CN)
If Not Ok Then
    MsgBox ADO.GetLastError(CN) + vbCrLf + "Connect String:" + ADO.Value, vbExclamation, "Database Connection Error"
    Exit Sub
End If

' To Read read only recordset
SQL = "Select * From [YourTable];"
Ok = ADO.OpenRSROOK(CN, RS, SQL)
If Not Ok Then
    MsgBox ADO.GetLastError(CN) + vbCrLf + "SQL:" + SQL, vbExclamation, "SQL Error"
    Set CN = Nothing
    Exit Sub
End If

' Optional handle for no records
If RS.EOF Then
    MsgBox "No Records"
    Exit Sub
End If

' skips if there are no records in recordset
Do While Not RS.EOF
    ' process record
    RS.MoveNext
Loop

' Close record set

RS.Close
Set RS = Nothing
cs.Close
Set cs = Nothing
   

End Sub

'=========================================

Here is the code for the ADOSubLib class

Option Explicit

Dim DefaultODBCConnect As String
Function ConnectionODBCOK(ConnectionObj As ADODB.Connection, ODBCConnect As String) As Boolean
   
    ' Allows connection to any database
   
    ' Create a connection object
    Set ConnectionObj = New ADODB.Connection

   
    ' Setup the connection params
    ConnectionObj.ConnectionString = ODBCConnect
    ConnectionObj.CommandTimeout = 1200
    ConnectionObj.ConnectionTimeout = 10
   
    ' Make sure no errors
    On Error Resume Next
    Err.Clear
    ConnectionObj.Open      ' try to open the connection
    If Err.Number <> 0 Then
        ConnectionODBCOK = False
        'qw "Connection Error: " & CStr(Err.Number) & " " & Err.Description & " ODBC: " & ODBCConnect
    Else
        ConnectionODBCOK = True
    End If
   
End Function
Function ConnectionOK(ConnectionObj As ADODB.Connection) As Boolean
    ' Connect to the registered connection
    ConnectionOK = ConnectionODBCOK(ConnectionObj, DefaultODBCConnect$)
   
End Function
Function ExecuteSQLOK(SQL As String) As Boolean

    ' Will execute an SQL Command
    ' Retruns True/False error status Fales=Fail
   
    Dim CN As ADODB.Connection ' Setup a connection object
    Dim Ok
   
    Ok = ConnectionOK(CN)
    If Not Ok Then
        ExecuteSQLOK = False
    Else
        On Error Resume Next
        Err.Clear
        CN.Execute SQLCheck(SQL) ' Run the command
        If Err.Number <> 0 Then
            ExecuteSQLOK = True
        Else
            ExecuteSQLOK = False
        End If
        On Error GoTo 0
        CN.Close
       
    End If
   
    Set CN = Nothing

End Function
Public Function InsertGetCounter(CN As ADODB.Connection, SQLforInsertInto As String) As Boolean
   
' Fires an Insert Into satement then gets the Indetity/autoincreament of the last error

Dim RS As Recordset
Dim SQL$
   
SQL$ = "SET NOCOUNT ON;" + SQLforInsertInto
If Right$(SQL, 1) <> ";" Then
    SQL = SQL + ";"
End If
SQL$ = SQL$ + " Select @@Identity as RecID;SET NOCOUNT OFF;"

On Error Resume Next
Err.Clear
Set RS = CN.Execute(SQL$)
If Err.Number = 0 Then
    InsertGetCounter = RS("RecID")
    RS.Close
Else
    InsertGetCounter = 0
End If

Set RS = Nothing

End Function


Public Function cSQL(SQLData As String)

' Puts a string into an Insert SQL satement
' removes ' character which will cause crash.

Dim SQLRep As String 'String in Error Routine
Dim iSLen As Integer 'String Length in Error Routine
Dim cCHar As String
Dim iCharCo As Integer

SQLRep = ""
iSLen = Len(SQLData)
For iCharCo = 1 To iSLen
    cCHar = Mid(SQLData, iCharCo, 1)
    SQLRep = SQLRep & cCHar
    If cCHar = "'" Then SQLRep = SQLRep & cCHar
    If cCHar = """" Then SQLRep = SQLRep & cCHar
Next iCharCo
cSQL = SQLRep

End Function
Function GetADOCounter(CN As Connection) As Long

' Return the indetity/autonumber of the last inserted record.
' Warning if triggers add record to other tables this work

' It is faster to use the InsertGetCounter routine.

Dim stRSQL$
Dim rsNewAutoIncrement As ADODB.Recordset

stRSQL = "SELECT @@Identity"
   
On Error Resume Next
Set rsNewAutoIncrement = New ADODB.Recordset
rsNewAutoIncrement.Open stRSQL, CN, adOpenForwardOnly, _
                        adLockReadOnly, adCmdText
GetADOCounter = rsNewAutoIncrement.Fields(0).Value
rsNewAutoIncrement.Close

If Err.Number <> 0 Then GetADOCounter = 0

End Function

Public Function GetLastError(CN As ADODB.Connection) As String
' Obtains the last error on a connection
Dim M$

Dim E As ADODB.Error
Dim Elist As ADODB.Errors
Set Elist = CN.Errors
For Each E In Elist
    M$ = M$ + CStr(E.Number) + " " + E.Description + " " + E.Source + " " + E.SQLState + vbCrLf
Next
   
GetLastError = M$

End Function

Public Sub RegisterConnectString(ConnectionString As String)
' Regiser an ODBC connection string
DefaultODBCConnect = ConnectionString
End Sub
Public Property Let MDBConnect(AccessDatabaseName As String)

' Sets up a connection string for an access database

DefaultODBCConnect$ = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + AccessDatabaseName + ";"

End Property

Function OpenRSOK(CN As ADODB.Connection, RS As ADODB.Recordset, SQL As String, pCursorType As ADODB.CursorLocationEnum, pLockType As ADODB.CursorTypeEnum) As Boolean
   
    ' Open a recordset
   
    ' Warning CN Must have not been used for anything other then a connection
    ' is CN is nothing then a new connection will be opened
   
    ' usage example

    'OK = OpenRSOK(CN, RS, "Select * from [Test];", adUseServer, adOpenDynamic)


   
    Dim Ok
    Dim didopen
   
    didopen = False
   
    ' Open The Connection
    If CN Is Nothing Then
        Ok = ConnectionOK(CN)
        If Not Ok Then
            OpenRSOK = False
            Exit Function
        End If
        didopen = True
    End If
   
    On Error Resume Next
   
    Err.Clear
    Set RS = New ADODB.Recordset
    RS.Open SQLCheck(SQL), CN, pCursorType, pLockType ', padcmdtext
    If Err.Number <> 0 Then
       ' qw "OpenRSOK Error: " & CStr(Err.Number) & "  " & Err.Description & "  " & SQLCheck(SQL)
        OpenRSOK = False
        If didopen Then
            CN.Close
            Set CN = Nothing
            Set RS = Nothing
        End If
    Else
        OpenRSOK = True
    End If
   
End Function


Function OpenRSROOK(CN As ADODB.Connection, RS As ADODB.Recordset, SQL As String) As Boolean
    ' Open a readonly record set
   
    OpenRSROOK = OpenRSOK(CN, RS, SQL, adOpenForwardOnly, adLockReadOnly)
End Function


Public Function Prep(StringtoPrepare As String)
' This function is oibsolete and has been replaced by cSQL

Prep = "'" + Replace(StringtoPrepare, "'", "^", 1, -1, vbBinaryCompare) + "'"
End Function


Public Function PrepDate(RequiredDate As Date)

' Converts a date/time to SQL server format placing within single quotes
' Example:
' SQL=SQL + "InsertDate = "+ADO.PreDate(Now)

PrepDate = "'" + Format$(RequiredDate, "YYYY-MM-DD HH:Nn:SS") + "'"

End Function

Public Function SQLCheck(SQL As String) As String

' Place sql syntac check here
' or Auto conversion from SQL to Oracle etc.

SQLCheck = SQL

End Function

Public Property Let Value(DefaultConnectionString As String)
' Make this the Default Procedure (see tools default attributes)
DefaultODBCConnect$ = DefaultConnectionString
End Property

Public Property Get Value() As String
Value = DefaultODBCConnect$
End Property




0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 17

Expert Comment

by:inthedark
ID: 6307864
warning ashish_1319  proposes the use of the recordcount property which will not work.  Recordcount is not mentioned in the ADODB recordset open documentation.  However, EOF is.  Furthermore, it is quite possible that ADO does not know how many records are in the recordset, in this case the recordcount is = -1
0
 
LVL 17

Expert Comment

by:inthedark
ID: 6307873
Sorry I posted my earlier comment twice.
0
 
LVL 2

Expert Comment

by:Lunchy
ID: 6309313
inthedark, I deleted your dup comment.

ashish_1319, I have rejected your answer as it clearly does not address the problem.  Please try and use comments only, especially in a question where some discussion is taking place.  You are not new here, I should not have to remind you of the etiquette of the VB topic area.

Lunchy
Friendly Neighbourhood Community Support Moderator

0
 

Author Comment

by:waiman
ID: 6310481
TO intheDark:
I am a bit dumb here but can you tell me what's the main difference between your code and my code, given that I am also using the adoConn object to open the recordset?
I am using Createobject because I am working on an ASP project and was told this is the correct way to initialise object for VB ActiveX DLL.

Hope this help.
Cheers,
Wai-man
0
 
LVL 17

Accepted Solution

by:
inthedark earned 100 total points
ID: 6313290
waiman you ask what is the difference in code?

Your code creates the object before the open is made and therefore if the open fails the object will still exists and so the recordset will never be nothing.  

So your code may blow up with  unhanded error.

The example I posted generates the record set from the Connection object.  In this case the recordset will be nothing if the open fails.

I am not saying you should use the ADO object that I posted but to help you make a judgement here is a list of the advantages it provides:

1) Relieves tedium - as so nothing is cumbersome.

All I have to do is type "ADO." and I get a list of the functions that I need to use.  I have made it very simple to open a readonly recordset and set up the standard recordset open parameters to autolist when you try to select them from the ADOSubLib class.

2) Error Handling

If you don't handle errors you get lots of phone calls, the more you get,  the less you can earn. You need to set error traps if you want to make a good job of writing  software.  Using my ADO object handles errors for you so you just ask a simple question:

ok=ADO.OpenRSOK(CN,RS,SQL) ' Open recordset read only OK

And to handle the error you can pass the SQL statement to a general error handle which will place you code on the clipboard so you can see what went wrong..  You can also pass a list of the errors from the connection object.  You can get the list of errors by typing ADO.GetLastError(CN).

 If Not Ok Then
    HandleSQLErrors "Stage: 1510", ADO,GetLastError(CN), SQL
    Exit Sub
End if

3) Future Proofing

You did not specify the Open method's options when opening your recordset.  This can cause problems when you connect to a different data source.  You can also get caught out when Microsoft changes its defaults which they can and will do at some time in the future.

4)  Easy Conversion to  New Technologies

By making only one or two places in your software talk to the ADO methods Connect  & Open Recordset and Execute Command you can make it easy to convert to another system.  We've seen DAO and now ADO, what's next?  If you plane for a change now you will be better off in the future.

For example there may be minor changes with syntax between Oracle, IBM BD2, MYSQL, and SQL Server you can handle these differences simply as you only have to enhance a couple of subroutines rather than a whole application.

Others may share different views.  My philosophy is based around retaining the ownership of your own software for decades, making it easy to support and quick to enhance. Hope this helps you......
0
 

Author Comment

by:waiman
ID: 6315330
Thanks for your help.
Cheers,
Wai-man
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now