[RESOLVED] VB6 ADO error when adding WHERE clause in query

[*PsyKE1*]

I have a csv file with the following information:

NOMBRE,EDAD,FECHA
MARIO,30,24/12/2002
MARIA,90,24/12/2001
PEDRO,10,24/12/2004

Then I want to interact with it using ADO like this (I've added Microsoft ActiveX Data Objects 2.0 Library reference):

Dim oConn As New ADODB.Connection
Dim oRS As New ADODB.Recordset

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & Environ("temp") & ";" _
& "Extended Properties=""text;HDR=Yes;FMT=Delimited(,)"";Persist Security Info=False"

Set oRS = oConn.Execute("select * from datos.csv")
Dim ofield As ADODB.Field

Do Until oRS.EOF
    For Each ofield In oRS.Fields
        Debug.Print "Field Name = " & ofield.Name & " Field Value = " & ofield.Value
    Next ofield
    oRS.MoveNext
Loop

It works! Returns:

Field Name = NOMBRE Field Value = MARIO
Field Name = EDAD Field Value = 30
Field Name = FECHA Field Value = 24/12/2002
Field Name = NOMBRE Field Value = MARIA
Field Name = EDAD Field Value = 90
Field Name = FECHA Field Value = 24/12/2001
Field Name = NOMBRE Field Value = PEDRO
Field Name = EDAD Field Value = 10
Field Name = FECHA Field Value = 24/12/2004

But If I try to do a query like this:

SELECT EDAD FROM datos.csv WHERE NOMBRE='MARIO'

I have the following error:

-2147467259 (800040005) Error in 'Execute' method of '_Connection' object.

What am I dismissing? The only thing I've done is adding a 'where' clause in the query...
If I compile the code, the same error appears:

[LaVolpe]

Does bracketing the table name resolve the problem?

SELECT EDAD FROM [datos.csv] WHERE NOMBRE='MARIO'

[*PsyKE1*]

Hi LaVolpe,

Thank you, but I'm afraid it returns the same error.

[*PsyKE1*]

Hi LaVolpe,

Thank you, but I'm afraid it returns the same error.

[LaVolpe]

Just guessing here. I'm not on a VB machine & I've used similar code in dozens of projects to open/query csv files.

One thing you are doing that I don't. I do not use .Execute to return recordsets only to execute update/delete queries. Suggest using oRS.Open vs oCnn.Execute. Also pick the type of recordset to return in the .Open command, i.e., OpenDynamic.

[*PsyKE1*]

Thanks,

following with your advice, this is my new code:

Dim oConn As New ADODB.Connection
Dim oRS As New ADODB.Recordset

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & Environ("temp") & ";" _
& "Extended Properties=""text;HDR=Yes;FMT=Delimited(,)"";Persist Security Info=False"

oRS.Open "SELECT EDAD FROM [datos.csv] WHERE NOMBRE='MARIO'", oConn, adOpenDynamic, adLockReadOnly
Dim ofield As ADODB.Field

Do Until oRS.EOF
    For Each ofield In oRS.Fields
        MsgBox "Field Name = " & ofield.Name & " Field Value = " & ofield.Value
    Next ofield
    oRS.MoveNext
Loop

But returns:

-2147467259 (800040005) Error in 'Open' method of '_Recordset' object.

[LaVolpe]

Is your system locale set for Spanish? If so, bracket the field names too. On English systems, Name is a key word and without brackets, queries will fail. Similar with Date and other keywords used by SQL.

[*PsyKE1*]

Yes, is set in spanish.
But 'NOMBRE' or 'EDAD' are not keywords like 'NAME' or 'DATE'.

Anyway I've changed it to:

oRS.Open "SELECT [EDAD] FROM [datos.csv] WHERE [NOMBRE]='MARIO'", oConn, adOpenDynamic, adLockReadOnly, adCmdText

And now the error is:

2147217904 (8004e10)  There are no specified values for some of the required parameters.

[LaVolpe]

Yes, is set in spanish.
But 'NOMBRE' or 'EDAD' are not keywords like 'NAME' or 'DATE'.

And now the error is:

2147217904 (8004e10)  There are no specified values for some of the required parameters.

Well it was worth a shot, didn't know if locale's would be a factor or not.

Since I really can't play along (i.e., test stuff myself), I'll back off and hopefully others may see the problem. In the mean time, have you looked at the FAQ portion of the forums regarding DBs?

[DataMiser]

I would suggest changing your reference from ADO 2.0 to ADO 2.5 as that is what most of us would be using. I haven't used 2.0 in many years can't remember what it does or does not support nor what bugs were in it. Try using 2.5 and see if you still get the error.

[*PsyKE1*]

I've change the reference tu ADO 2.5 and now it works! THank you very much, DataMiser!