ADO recordset is nothing

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

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

On addition to GeoffKell.
When the recordset contains no data both eof and bof will be true.

Try this,
if recordset.State = adStateOpen then
  if recordset.recordcount > 0 then
     xxx
     xxx
     xxx
  end if
end if

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

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

Sorry I posted my earlier comment twice.

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

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

Thanks for your help.
Cheers,
Wai-man