ADO SQL Search That Returns 0 Records When it Should Return 1 
Author Message
 ADO SQL Search That Returns 0 Records When it Should Return 1

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



Wed, 05 Nov 2003 00:37:50 GMT  
 ADO SQL Search That Returns 0 Records When it Should Return 1
code the sql statement like this

"SELECT State From State_Hyperlinks WHERE
State='WI'"

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Wed, 05 Nov 2003 01:08:27 GMT  
 ADO SQL Search That Returns 0 Records When it Should Return 1
I wish life were that easy.  That is the format I was using before I tried creating the SQL statement in Access and pasting it in.  Same result.

Thanks anyway

Jack Siegel

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Wed, 05 Nov 2003 01:51:04 GMT  
 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



Wed, 05 Nov 2003 01:56:45 GMT  
 ADO SQL Search That Returns 0 Records When it Should Return 1
Hello jack

Try after setting  the cursor Location to adUseClient and the cursor type to
adOpenKeyset

Sukesh



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



Wed, 05 Nov 2003 02:41:55 GMT  
 ADO SQL Search That Returns 0 Records When it Should Return 1
The only cursor type available for adUseClient is adOpenStatic.

 - Tom


Quote:
> Hello jack

> Try after setting  the cursor Location to adUseClient and the cursor type
to
> adOpenKeyset

> Sukesh



Wed, 05 Nov 2003 03:03:25 GMT  
 ADO SQL Search That Returns 0 Records When it Should Return 1
You are right

Thanks

Sukesh


Quote:
> The only cursor type available for adUseClient is adOpenStatic.

>  - Tom



> > Hello jack

> > Try after setting  the cursor Location to adUseClient and the cursor
type
> to
> > adOpenKeyset

> > Sukesh



Wed, 05 Nov 2003 09:33:41 GMT  
 ADO SQL Search That Returns 0 Records When it Should Return 1
The problem, after much work, has been resolved.  Thanks for assistance

Jack Siegel

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Thu, 06 Nov 2003 00:19:05 GMT  
 
 [ 8 post ] 

 Relevant Pages 

1. SQL Statement returns records in Access but not in ADO

2. Searching for Records and Returning values...

3. Stateless search from database (Return 20 and 20 records)

4. Unable to return float, always returning int with ADO, IIS and SQLServer

5. Counting records returned by SQL Select statement

6. Using returned records from SQL Pass-thru in a report

7. PROBLEM: SELECT @@Identity return DBNull when insert new record (VisualBasic Dot Net, SQL 2000)

8. SQL Query to MS-Access only returns single record

9. ADODB and SQL Server and Limiting Returned Records

10. Access Table locked when no record return from SQL

11. SQL select returning EOF when record exists

12. SQL Query to MS-Access only returns single record

 

 
Powered by phpBB® Forum Software