Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Wanet Telecoms Ltd on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

WHERE clause in ADO 3

Status
Not open for further replies.

RonRepp

Technical User
Feb 25, 2005
1,031
US
Hi all:

What I'm looking for is how to write a WHERE clause for an ADO Connection that is from a CSV file. I've included my code thus far.

Set Con = New ADODB.Connection
Set RS = New ADODB.Recordset

Con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Y:\DebtNegotiationsCSV\;Extended Properties=""text;HDR=No;FMT=Delimited"""

Con.Open Con.ConnectionString
Sql = "Select * from " & Station & ".csv"

On Error GoTo WrongStation
Retry:
RS.Open Sql, Con, adOpenDynamic, adLockBatchOptimistic
Do While Not RS.EOF
ZIP = RS(0).Value
If Len(ZIP) = 3 Then ZIP = "00" & ZIP
If Len(ZIP) = 4 Then ZIP = "0" & ZIP
City = RS(1).Value
County = RS(2).Value
State = RS(3).Value
If S = True Then
InsertSQL
ElseIf A = True Then
AddToAccess
Else
AddToAccess
InsertSQL
End If

Rec = Rec + 1
lblRec.Caption = Format(Rec, "##,###") & " Records Added"
DoEvents

RS.MoveNext
Loop
RS.Close
Con.Close

Thanks,

Ron


Ron Repp
 
Unless your where clause would restrict the valid records to only a very small percentage of the records returned in the csv file, you could just use the filter property.
Code:
With RS
   .Open Sql, Con, adOpenDynamic, adLockBatchOptimistic
   .Filter = 'a valid WHERE clause (well, most valid WHERE clauses), expressed as a string
   Do Until .EOF
      'etc
   Loop
   .Close
End With
Con.Close
I also put an example of a With block in for you, since it's more efficient. (VB only has to look up RS's pointer once.)

HTH

Bob
 
Bob:

Thanks, I never thought of a filter, but doesn't a filter have to be used against a certain field?

For instance, I want to find a certain Station within the CSV file, let's say WKRP. Will the filter then bring up everything listed with WKRP in it?

I guess that I don't quite understand.

Ron

Ron Repp
 
<doesn't a filter have to be used against a certain field?
The following code works fine:
Code:
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Set rs = New ADODB.Recordset
Set cn = New ADODB.Connection
cn.Open "provider=sqloledb;data source=RRODES2k;initial catalog=pubs;user id=sa"
rs.Open "select * from authors", cn
[b]rs.Filter = "city = 'Oakland' and au_lname like 'S%'"[/b]
On the other hand, it might not work for CSV files. I'll let you try it.

To answer your second question, the short answer is "yes." A filter is like applying a where clause after the fact of opening the recordset. All of the properties like recordcount will be the same as if you applied the where clause in the sql statement. The main practical difference is in performance. If your filter restricts a recordset of 10,000 records to 3 records, and you iterate through it, it will still iterate through the 10,000 records. However, you can base your iteration logic on only those 3 records.

I hope that makes sense.

Bob
 
Forgive my ignorance, but when making an ADO call to a .csv file, what exactly is interpreting the SQL code? Does the Jet OLEDB driver do this itself? When querying SQL server, I must construct my syntax in transact-sql. When querying an Access file I structure the query the way Access interprets SQL (which is usually close, but annoyingly different sometimes).
So for a .csv accessed via OLEDB, what SQL syntax would be used? I take it that a normal where clause does not work?

Sql = "Select * from " & Station & ".csv WHERE MyColumn = 'xxx'
 

Can you edit the csv file with that connection object?
If not
rs.CursorLocation= asUseServer
rs.CursorType = adOpenForwardOnly
rs.LockType = adLockReadOnly
rs.Source = "Select * from " & Station & ".csv Where Station ='" MySelectedStation & "'"

is faster

Also change
ZIP = RS(0).Value
If Len(ZIP) = 3 Then ZIP = "00" & ZIP
If Len(ZIP) = 4 Then ZIP = "0" & ZIP
to
ZIP = Right("00" & RS(0).Value, 5)
 
I haven't ever used JET/ADO to interact with a text file. I'm assuming from your question that you can't put a where clause in the select statement, because the provider won't support it. If so, then Jerry's where clause doesn't work. As for editing, I have no idea. You could try it and post back here. Other than the possibly invalid where clause, I second everything Jerry says.

As for your question, jasen: OLE DB is a collection of COM components that define a standard means of providing and consuming data. ADO is an example of a consumer, and the most often used one. (DAO would be another one.) There are many providers, since there are many types of data sources. For example, there is the SQL OLE DB provider, the Oracle OLE DB provider, the iSeries one (used with the AS400), and the ODBC and JET providers.

So, with the JET ("Joint Engine Technology", I kid you not)OLE DB provider, you have access to the wide range of ISAM databases, and text files, and so on that are supported by JET, including of course Access ("native JET"; gotta love those Microsoft marketing types). What this means is that all of the data sources that JET can access, can be consumed via ADO using the JET OLE DB provider.

So, the fact that you can specify a text file as a data source in your connection string is dependent on the fact that you have specified the JET OLE DB Provider and that provider supports text file access. It's the provider that determines what can be accessed, and on what terms it can be. So, perhaps if you find out defnitively whether or not the JET OLE DB provider supports not only csv file access, but editing of same and subset selection via where clauses, you will post your findings here for everyone.

HTH

Bob

HTH,

Bob
 
Thanks everyone, but still no luck. This is what I tried. If anyone can figure it out, I'd be eternally greatful.

With RS
.Open Sql, Con, adOpenDynamic, adLockBatchOptimistic
.Filter = .Fields(0).Value & "='" & txtZip.Text & "'"
Do While Not .EOF
MsgBox "ZIP: " & RS(0).Value & vbCrLf & "City: " & RS(1).Value
.MoveNext
Loop
.Close
End With

Thanks again.

Ron

Ron Repp
 
I think that you need
Code:
.Filter = .Fields(0).[COLOR=red]Name[/color] & "='" & txtZip.Text & "'"
The format of the filter clause is

FieldName Operator Value

for instance

[ZIPCode] = '123456'
 
You might try to trim it as there may be empty spaces and then the filter wont work
 
All righty, then, I did a little digging around. Here are the things affecting filtering and your where clause:

1. As you probably know, a csv file may or may not have the field names of the table in the first row. Your connection string says "HDR=No", which means to not treat the first row as the field names. If there are field names in the first row, they will be treated as the data in the first record, unless you say HDR=Yes. Conversely, if you say HDR=Yes and the first row isn't the field names, the first record in your table will be missing from the table and will instead be the field names of the table.

So, assuming that you have this right, and the source csv does not have field names in the first row: HDR=No assigns the field names F1, F2, F3 and so on. You can use these names in the where clause, forget about the filter. (You could use it there too.)

To use the following test code, I first went into Access, and exported the Customers table from the Northwind database to C:\Temp\Customers.txt. I played around with a file that has headers and a file that doesn't, and also with the Hdr setting in your connectionstring. You can use .Fields(n).Name to find the names of fields.

Code:
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection

Public Sub DoStuff()
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
Dim cLine As String
Dim i As Integer

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\;Extended Properties=""text;HDR=No;FMT=Delimited"""
With rs
    .Open "Select * from customers.txt where F1 = 'ALFKI'", cn
    Do Until .EOF
        cLine = .Fields(0)
        For i = 1 To .Fields.Count - 1
            cLine = cLine & vbTab & .Fields(i)
        Next i
        Debug.Print cLine
        .MoveNext
    Loop
End With
rs.Close
cn.Close
End Sub

Private Sub Command1_Click()
DoStuff
End Sub

So, being on a roll as I were, I thought I would give this a try:

Code:
Private Sub DoUpdate()
Dim x As Long
Set cn = New ADODB.Connection
Set cmd = New ADODB.Command
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\;Extended Properties=""text;HDR=No;FMT=Delimited"""
cmd.CommandText = "update customers.txt set f1 = 'ALFIE' where f1 = 'ALFKI'"
cmd.ActiveConnection = cn
cmd.Execute x
MsgBox x & " Records affected."
End Sub

This raises an error when trying to run cmd.Execute:
Code:
'In the debug window
? err.source & " " & vbobjecterror - err.Number & " " & err.Description
Microsoft JET Database Engine 245755 Updating data in a linked table is not supported by this ISAM.

One of Microsoft's clearer error descriptions, I would say. So, updating doesn't work directly.

So, I did some more digging around. The following code opens the Customers.txt file, saves it as an XML file, opens the xml file into the recordset, makes changes in the recordset, and saves the result in the same xml file. If you wanted to persist these updates to your text file, you could simply open the xml file into a recordset, and write code to write the recordset into a new text file.

Code:
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\;Extended Properties=""text;HDR=No;FMT=Delimited"""
With rs
    .CursorLocation = adUseClient
    .Open "Select * from customers.txt", cn, , adLockBatchOptimistic
    'add code here to delete target file if it exists; the following only raises an error
    .Save "c:\temp\customers.xml", adPersistXML
    .Close
    .Open "c:\temp\customers.xml", "Provider=MSPersist", , adLockBatchOptimistic
    .Filter = "F1 = 'ALFKI'"
    !F1 = "ALFIE"
    .Filter = "" 'Make sure you do this or you'll only save the records that pass the filter
    'add code here to delete the xml file
    'note that the Update method is unnecessary; the Save method simply translates the current state of the recordset into XML, so no underlying posting happens.
    .Save ,adPersistXML
End With
The following section can be found in the resulting xml file:
Code:
	<rs:update>
		<rs:original>
			<z:row F1='ALFKI' F2='Alfreds Futterkiste' F3='Maria Anders' F4='Sales Representative' F5='Obere Str. 57'
				 F6='Berlin' F8='12209' F9='Germany' F10='030-0074321' F11='030-0076545'/>
		</rs:original>
		<z:row F1='ALFIE'/>
	</rs:update>

So, if you want to support updates, you could use ADO to open your text file, save it as an xml file, open the xml file, make changes to it and resave, and then convert the result to text. Kind of long-winded, of course. One of the reasons that xml is supplanting text as an interplatform data storage standard is that it's easier for a given platform to manipulate it, since it has a richer metadata context.

p. s. looks like Golom's right.

Bob
 
Thanks everyone.

I'll persist and try Bob's method to see if that helps. I'm beginning to think that the easiest thing to do, though, is load it in Excel and parse it that way.

Ron

Ron Repp
 
Thanks again to all. Here is the solution.

==============================
For No Header:

Set Con = New ADODB.Connection
Set RS = New ADODB.Recordset

Con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=Y:\DebtNegotiationsCSV\;Extended Properties=""text;HDR=No;FMT=Delimited"""

Con.Open Con.ConnectionString
Sql = "Select * from " & txtStation.Text & ".csv"

Retry:
With RS
.Open Sql, Con, adOpenDynamic, adLockBatchOptimistic
.Filter = "F1='" & txtZip.Text & "'"
Do While Not .EOF
MsgBox "ZIP: " & RS(0).Value & vbCrLf & "City: " & RS(1).Value
.MoveNext
Loop
End With
==============================
With a header use this:

Set Con = New ADODB.Connection
Set RS = New ADODB.Recordset

Con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=Y:\DebtNegotiationsCSV\;Extended Properties=""text;HDR=Yes;FMT=Delimited"""

Con.Open Con.ConnectionString
Sql = "Select * from " & txtStation.Text & ".csv"

Retry:
With RS
.Open Sql, Con, adOpenDynamic, adLockBatchOptimistic
.Filter = .Fields(0).Name & "='" & txtZip.Text & "'"
Do While Not .EOF
MsgBox "ZIP: " & RS(0).Value & vbCrLf & "City: " & RS(1).Value
.MoveNext
Loop
End With

Thanks again,

Ron

Ron Repp
 
Two observations:
1. Your "no header" and "header" versions differ (besides specifying the fact in the connectionstring) only in the Filter setting:
Code:
'no header
    .Filter = "F1='" & txtZip.Text & "'"
'header
    .Filter = .Fields(0).Name & "='" & txtZip.Text & "'"
The way you have them set up here, the header version works fine for either no header or header, because .Fields(0).Name will be "F1" in the no header version. So, you don't really need to put two blocks of mostly repetitive code, if you do it this way. Consider just putting a boolean argument (maybe "IsHeader") as an argument to the proc this code is in, and specify the connectionstring appropriately.
2. Filter can be inefficient, and should be used judiciously. Keep in mind that if a filter represents a small subset of the whole recordset, you will still iterate through all of the records in the recordset in your "Do while not .EOF" construct (being a brevity fanatic, I prefer to use "Do Until .EOF"). Consider putting the logic into the where clause.

So, for example:
Code:
Sub GetMyData(IsHeader as Boolean)
[COLOR=red]Dim cnString as string[/color]
Set Con = New ADODB.Connection
Set RS = New ADODB.Recordset
[COLOR=red]cnString =[/color] "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=Y:\DebtNegotiationsCSV\;Extended Properties=""text;HDR=" & _
    [COLOR=red]IIf(IsHeader, "Yes", "No")[/color] & ";FMT=Delimited"""
Con.Open [COLOR=red]cnString
'As a general rule, attempting to minimize references to objects results in more efficient code.[/color]
Sql = "Select * from " & txtStation.Text & ".csv [COLOR=red]where " & .Fields(0).Name & "='" & txtZip.Text & "'"[/color]
Retry:
With RS
    .Open Sql, Con, adOpenDynamic, [COLOR=red]adLockOptimistic 
    'Batch Optimistic is used with disconnected recordsets, which are client side and static.
    'Also, I wouldn't use a Dynamic recordset without good reason.  ForwardOnly or Static cursors 
    'are the most efficient.
    '(Deleted Filter setting)[/color]
    Do Until .EOF
        MsgBox "ZIP: " & RS(0).Value & vbCrLf & "City: " & RS(1).Value
        .MoveNext
    Loop
End With
End Sub

HTH

Bob
 
Bob:

I'll try rewriting it your way. I agree that the filter seems clunky, but when I tried using the WHERE clause, I kept getting errors. However, now that I see how you've done it, maybe I'll get it right.

Thanks again,

Ron

Ron Repp
 
If you're still getting errors, feel free to post your code and I'll see if I can find the problem.

Bob
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top