
ADO SQL Search That Returns 0 Records When it Should Return 1
Jack,
A RecordCount of -1 is "by-design". Make sure to read the following ADO FAQ
http://www.able-consulting.com/ADO_Faq.htm#Q2
--
Thanks,
Carl Prothman
Microsoft Visual Basic MVP
http://www.able-consulting.com
I am new to Visual Basic. I am trying to access an Access database (Access
2000)
from within Visual Basic. The big picture--The end user sees a map of the
USA,
clicks on a particular state, and information either appears on a form, or
an
Internet connection is triggered.
Here is the code that I am using with respect to database access:
'HANDLE BUTTONCLICK ON STATE
Private Sub Rose1_ButtonPress(ByVal ButtonName As String, ByVal ButtonIndex
As
String)
'DEFINE THE ADO OBJECTS
Dim adoConnect As ADODB.Connection
Dim adoRecordset As ADODB.Recordset
Dim adoCommand As ADODB.Command
Dim s_SQL As String
'DYNAMICALLY BUILD THE CONNECTION
Set adoConnect = New ADODB.Connection
Set adoRecordset = New ADODB.Recordset
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist
Security
Info=True;Data Source=F:\PROJECT_NONPROFITS\Map2\stateinfo.mdb"
adoConnect.Open ConnectionString
If adoConnect.State = adStateOpen Then
MsgBox "Open"
Else
MsgBox "Close"
End If
'THIS IS ONE WAY TO CREATE A RECORDSET AND IT WORKS
'adoRecordset.Open "State_Hyperlinks", adoConnect
'adoRecordset.MoveFirst
'Do Until adoRecordset.EOF
' MsgBox adoRecordset("Url")
' adoRecordset.MoveNext
'Loop
s_SQL = "SELECT State_Hyperlinks.State From State_Hyperlinks WHERE
(((State_Hyperlinks.State)='WI'))"
adoRecordset.Open s_SQL, adoConnect, adOpenDynamic,
adLockOptimistic,
adCmdText
If adoRecordset.State = adStateOpen Then
MsgBox "Open"
Else
MsgBox "Close"
End If
MsgBox adoRecordset.RecordCount
'MsgBox adoRecordset.("State_HyperLinks.State")
adoRecordset.Close
adoConnect.Close
Set adoRecordset = Nothing
Set adoConnection = Nothing
End Sub
As you will note, I have been testing whether the connection is open--it is.
I have also built a recordset with the commented out code--it works.
It is the SQL statement that runs into trouble. I have tested it in Access
and
it works--in fact the code is straight out of Access. Originally, I was not
prefacing State with State_Hyperlinks, but I took the current format out of
Access.
I also check the recordset count. It keeps returning -1, apparently meaning
that
there are no records in the recordset.
When I write the SQL statement as SELECT * FROM State_Hyperlinks--it works.
I have read a good chunk of three Visual Basic Database programming books,
but am
unable to see my way out of the box I am in.
Any assistance would be greatly appreciated.
Thanks
Jack Siegel