waiman
asked on
ADO recordset is nothing
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.record set")
...
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
...
dim rs as adodb.recordset
set rs = createobject("ADODB.record
...
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
By creating the recordset with your statement
set rs = createobject("ADODB.record set")
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
set rs = createobject("ADODB.record
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.
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
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.record set") 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;ui d=Administ rator;pwd= MyPass;dat abase=MyDB "
' or
ADO.RegisterConnectionStri ng ("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.RegisterConnectionStri ng ("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(Connectio nObj 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.ConnectionSt ring = ODBCConnect
ConnectionObj.CommandTimeo ut = 1200
ConnectionObj.ConnectionTi meout = 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(Connectio nObj, 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(Conn ectionStri ng As String)
' Regiser an ODBC connection string
DefaultODBCConnect = ConnectionString
End Sub
Public Property Let MDBConnect(AccessDatabaseN ame As String)
' Sets up a connection string for an access database
DefaultODBCConnect$ = "Provider=Microsoft.Jet.OL EDB.4.0;Da ta 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(DefaultConnectionStr ing 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
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;ui
' or
ADO.RegisterConnectionStri
' 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.RegisterConnectionStri
' 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(Connectio
' Allows connection to any database
' Create a connection object
Set ConnectionObj = New ADODB.Connection
' Setup the connection params
ConnectionObj.ConnectionSt
ConnectionObj.CommandTimeo
ConnectionObj.ConnectionTi
' 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
' Connect to the registered connection
ConnectionOK = ConnectionODBCOK(Connectio
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(
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(Conn
' Regiser an ODBC connection string
DefaultODBCConnect = ConnectionString
End Sub
Public Property Let MDBConnect(AccessDatabaseN
' Sets up a connection string for an access database
DefaultODBCConnect$ = "Provider=Microsoft.Jet.OL
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(DefaultConnectionStr
' 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
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your help.
Cheers,
Wai-man
Cheers,
Wai-man
Try this...
...
dim rs as adodb.recordset
set rs = createobject("ADODB.record
...
rs.open mySQLstatement, adoConn
...
while not rs.eof
xxx
xxx
rs.MoveNext
loop