KerryG
asked on
Getting "index not found" querying DBase file
I am trying to do some queries from VB6 into an old DBase file. If I do a "select * from mytable" I can return all the rows. However, it would be MUCH nicer to do a query like "select * from mytable where refnum = '12455'". The problem is that when I use a "where" clause, I get an "Index Not Found" error.
Here is the code I am using:
Dim sql As String
Dim MyConn As ADODB.Connection
Dim RS As New ADODB.Recordset
sql = "SELECT * From mytable where REFNO = '74963'"
Set MyConn = New ADODB.Connection
' tried the following with same results
' MyConn.ConnectionString = "Provider=Microsoft.Jet.OL
MyConn.ConnectionString = "Driver={Microsoft dBASE Driver (*.dbf)};" & _
"DriverID=21;" & _
"Dbq=f:\;"
MyConn.Open
RS.Open sql, MyConn, adOpenForwardOnly, adLockReadOnly, adCmdText
Here is the code I am using:
Dim sql As String
Dim MyConn As ADODB.Connection
Dim RS As New ADODB.Recordset
sql = "SELECT * From mytable where REFNO = '74963'"
Set MyConn = New ADODB.Connection
' tried the following with same results
' MyConn.ConnectionString = "Provider=Microsoft.Jet.OL
MyConn.ConnectionString = "Driver={Microsoft dBASE Driver (*.dbf)};" & _
"DriverID=21;" & _
"Dbq=f:\;"
MyConn.Open
RS.Open sql, MyConn, adOpenForwardOnly, adLockReadOnly, adCmdText
ASKER
REFNO is actually a string field and not a numeric field, but the result is the same, the "index not found" error.
Have you tried this:
sql = "SELECT REFNO From mytable"
I am just wondering if it would return just that column.
Leon
sql = "SELECT REFNO From mytable"
I am just wondering if it would return just that column.
Leon
ASKER
yes, that returned the same error.
When you return the recordset witht he wildcard (*), go through the fields collection and see what are the filed names returned. There may be extra characters which we are not seeing. Return it as:
Debug.Print "[" & RS.Fields(intCount).Name & "]"
this way we may be able to spot the exta stuff inside the [] characters.
Leon
Debug.Print "[" & RS.Fields(intCount).Name & "]"
this way we may be able to spot the exta stuff inside the [] characters.
Leon
ASKER
Using that code I get:
"Item cannot be found in the collection corresponding to the requested name or ordinal"
"Item cannot be found in the collection corresponding to the requested name or ordinal"
Ok, lets try this:
For intCount = 1 to RS.Fields.Count
If FieldIsString(RS.Fields(in
Debug.Print "[" & RS.Fields(intCount).Name & "]"
End If
Next
Private Function FieldIsString(FieldObject As ADODB.Field) As Boolean
' From http://www.freevbcode.com/ShowCode.asp?ID=279
If Not TypeOf FieldObject Is ADODB.Field Then Exit Function
Select Case FieldObject.Type
Case adBSTR, adChar, adVarChar, adWChar, _
adVarWChar, adLongVarChar, adLongVarWChar
FieldIsString = True
Case Else
FieldIsString = False
End Select
End Function
For intCount = 1 to RS.Fields.Count
If FieldIsString(RS.Fields(in
Debug.Print "[" & RS.Fields(intCount).Name & "]"
End If
Next
Private Function FieldIsString(FieldObject As ADODB.Field) As Boolean
' From http://www.freevbcode.com/ShowCode.asp?ID=279
If Not TypeOf FieldObject Is ADODB.Field Then Exit Function
Select Case FieldObject.Type
Case adBSTR, adChar, adVarChar, adWChar, _
adVarWChar, adLongVarChar, adLongVarWChar
FieldIsString = True
Case Else
FieldIsString = False
End Select
End Function
ASKER
The problem definitly seems to be that I can't do certain queries without accessing the index file. Although the index file (.cdx) is there, ADO/RDO/ODBC doesn't seem to use it. I have read other places about installing the BDE but the main package for it wont install without Delphi installed so that isn't a workable solution.
Granted, I can fetch all 100,000 records and do a compare for the ONE record I am looking for, but that's a bit slow to say the least.
Granted, I can fetch all 100,000 records and do a compare for the ONE record I am looking for, but that's a bit slow to say the least.
ASKER
Here is the output from your code:
[DMVCHK]
[DMVREG]
[VOTECHK]
[PHONCHK]
[CB_CODE]
[EXPORT]
[PDC_DATA]
[OTH_DATA]
996]
[ 997]
[ 998]
[ 999]
It then errored after that with "Item cannot be found in the collection corresponding to the requested name or ordinal"
[DMVCHK]
[DMVREG]
[VOTECHK]
[PHONCHK]
[CB_CODE]
[EXPORT]
[PDC_DATA]
[OTH_DATA]
996]
[ 997]
[ 998]
[ 999]
It then errored after that with "Item cannot be found in the collection corresponding to the requested name or ordinal"
How many columns are in the table? I do not see REFNO as one of the column names.
Leon
Leon
ASKER
There are 88 fields. Those returned ones are not in any order nor are the last 4 numbers one part of the fields.
ASKER
I reran it after fixing a small code error:
[ADDRESS1]
[ADDRESS2]
[SQADD]
[AGECOMMENT]
[AGREFNO]
[CITY]
[CLIENTKEY]
[COLLCODE]
[DEBTYPE]
[DRIVERLIC]
[HARD_LETT]
[HPHONE]
[LASTLETER]
[LETTERSEQ]
[LASTNAME]
[FIRSTNAME]
[COMMNAME]
[CONTACT1]
[CONTACT2]
[INDUST]
[TAKEINT]
[NEXTLETTER]
[PAYPLAN]
[PRESTAT]
[REF1]
[REF2]
[SALUTAION]
[SEX]
[SINGLELET]
[COFIRST]
[COLAST]
[SSN]
[STATE]
[STATUS]
[WPHONE]
[ZIP]
[TRANAGCY]
[CLISTAT]
[PATIENT]
[DEBTREASON]
[ORIGCRED]
[EQUIPDESC]
[EQUIP2]
[PRIORITY]
[REASON]
[CASENO]
[CBRCHK]
[XXCHK]
[OPER411]
[CTACHK]
[DMVCHK]
[DMVREG]
[VOTECHK]
[PHONCHK]
[CB_CODE]
[EXPORT]
[PDC_DATA]
[OTH_DATA]
[ADDRESS1]
[ADDRESS2]
[SQADD]
[AGECOMMENT]
[AGREFNO]
[CITY]
[CLIENTKEY]
[COLLCODE]
[DEBTYPE]
[DRIVERLIC]
[HARD_LETT]
[HPHONE]
[LASTLETER]
[LETTERSEQ]
[LASTNAME]
[FIRSTNAME]
[COMMNAME]
[CONTACT1]
[CONTACT2]
[INDUST]
[TAKEINT]
[NEXTLETTER]
[PAYPLAN]
[PRESTAT]
[REF1]
[REF2]
[SALUTAION]
[SEX]
[SINGLELET]
[COFIRST]
[COLAST]
[SSN]
[STATE]
[STATUS]
[WPHONE]
[ZIP]
[TRANAGCY]
[CLISTAT]
[PATIENT]
[DEBTREASON]
[ORIGCRED]
[EQUIPDESC]
[EQUIP2]
[PRIORITY]
[REASON]
[CASENO]
[CBRCHK]
[XXCHK]
[OPER411]
[CTACHK]
[DMVCHK]
[DMVREG]
[VOTECHK]
[PHONCHK]
[CB_CODE]
[EXPORT]
[PDC_DATA]
[OTH_DATA]
I still do not see REFNO as one of the column names. Maybe you should try AGREFNO instead?
Leon
Leon
ASKER
sorry, its actually AGREFNO, the 5th one down, I hand typed the first section of code into here.
So what happens if you try:
sql = "SELECT * From mytable where AGREFNO = '74963'"
or
sql = "SELECT AGREFNO From mytable"
Are you still getting an error?
Leon
sql = "SELECT * From mytable where AGREFNO = '74963'"
or
sql = "SELECT AGREFNO From mytable"
Are you still getting an error?
Leon
ASKER
sql = "SELECT * From mytable where AGREFNO = '74963'"
Run-time error '-2147217900 (80040e14)':
[Microsoft][ODBC dBase Driver] Index not found.
-------------------
sql = "SELECT AGREFNO From mytable"
Returns all of the records
-------------------
sql = "SELECT AGREFNO From mytable where AGREFNO = '99475'
Run-time error '-2147217900 (80040e14)':
[Microsoft][ODBC dBase Driver] Index not found.
As it is right now, the only way I seem to be able to get around this is to select * and then do a string compare against every record to look for the match I am looking for. Highly ineffecient to say the least.
Run-time error '-2147217900 (80040e14)':
[Microsoft][ODBC dBase Driver] Index not found.
-------------------
sql = "SELECT AGREFNO From mytable"
Returns all of the records
-------------------
sql = "SELECT AGREFNO From mytable where AGREFNO = '99475'
Run-time error '-2147217900 (80040e14)':
[Microsoft][ODBC dBase Driver] Index not found.
As it is right now, the only way I seem to be able to get around this is to select * and then do a string compare against every record to look for the match I am looking for. Highly ineffecient to say the least.
Ok, one more thing, lets try:
sql = "SELECT AGREFNO From mytable where AGREFNO LIKE '%99475%'
Leon
sql = "SELECT AGREFNO From mytable where AGREFNO LIKE '%99475%'
Leon
ASKER
Guess dBase doesn't like LIKE. Every variation on it returns a syntax error.
Have you tried limiting the WHERE clause on other fields? I would like to know if it acts like this in every case or only for certain cases.
Leon
Leon
ASKER
Ok, I tried it on a few other fields, Status, FirstName, LastName, and State all work, but an Index Not found occurs on AGREFNO, SSN, Tickle, and ActionDate. So it seems to be pretty hit or miss and of course, the one way I really would like to search on (with SSN as a secondary) doesnt work.
It seems like the fields you are having problems with are numeric in nature. Lets try this as a test:
sql = "SELECT * From mytable where AGREFNO > 1"
sql = "SELECT * From mytable where AGREFNO > 1"
ASKER
Nope, index not found.
Ok, I am out of ideas. Try posting a pointer (20pts) to this question in the Database area. Maybe someone there will know wha is happening.
Leon
Leon
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have tried installing the BDE but that registry entry is never there.
My point was that you do not need BDE. Try creating the registry entry.
But is seems BobLamberson has the right idea in your original question here:
https://www.experts-exchange.com/questions/21076398/Dbase-III-Date-Query-Issue.html#11913277
so I will bow out.
https://www.experts-exchange.com/questions/21076398/Dbase-III-Date-Query-Issue.html#11913277
so I will bow out.
Hi KerryG,
This idea might solve part of your immediate problem.
> sql = "SELECT * From mytable where REFNO = '74963'"
try
sql = "SELECT * From mytable where val(REFNO) = 74963"
for some reason my dbf would find this when it wouldn't find the string.
Bob
This idea might solve part of your immediate problem.
> sql = "SELECT * From mytable where REFNO = '74963'"
try
sql = "SELECT * From mytable where val(REFNO) = 74963"
for some reason my dbf would find this when it wouldn't find the string.
Bob
ASKER
Manually adding the registry key did the trick. Its not fast, but it works.
>>Its not fast, but it works.<<
Exactly. You are not using any index.
Exactly. You are not using any index.
ASKER
I wish there was a way to use the index but at least this will work for now.
Did you able to to solve the problem ? as I am getting the same problem.
Rahul
Rahul
For a .cdx index file, you have to use the proper connectivity driver to get the benefit of it.
This is not dBASE and BDE doesn't apply at all.
If you have a .dbf file nowadays, more often than not, it is from a FoxPro application. And, since you have a .cdx file, that tells you it is a FoxPro compound index file and dBASE can't use it and certainly can't take advantage of it to get any speed advantage that you would like in your queries.
Download the free MS ODBC Driver for VFP at the MS Downloads site and use it rather than any lowest common denominator dBASE driver or any other Borland database add-on.
This is not dBASE and BDE doesn't apply at all.
If you have a .dbf file nowadays, more often than not, it is from a FoxPro application. And, since you have a .cdx file, that tells you it is a FoxPro compound index file and dBASE can't use it and certainly can't take advantage of it to get any speed advantage that you would like in your queries.
Download the free MS ODBC Driver for VFP at the MS Downloads site and use it rather than any lowest common denominator dBASE driver or any other Borland database add-on.
sql = "SELECT * From mytable where REFNO = 74963"
Leon