ADO with text files, problem doing JOIN

[vorlonken]

Ive been working for several days to solve various problems using text files as tables in ADO. Up until now Ive solved them by myself but this one is proving to be tricky. Im working with VB.net in VS 5.



Problem: Do a join on two tables, tables are text files in the same directory, there is a correct schema.ini file describing the text files, one file is fixedlength and the other is csvdelimited. I have no control over the fixedlength file, it comes from our IBM mainframe. (There are a few reasons that I dont want to import the text files into real Access tables; lets assume that isnt an option! I dont want MS access involved at all, not even with linked text-tables and stored procedures. That will be a last, last, LAST resort.)



This (non-join) code works great:



Code Snippet

connectionstring = "Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=" & StrTxtDbPath & ";Extended Properties=""text;HDR=NO;FMT=FixedLength"""



Using connection As New Odbc.OdbcConnection(connectionstring)
connection.Open()



amtcredit = + or -
invamt = double with IMPLIED decimal, needs to be /100 later
sqlstring = "select agency, invnum, sum(cdbl(amtcredit & invamt)) as charge from currpacedata.txt group by agency, invnum order by agency;"



Dim reader As Odbc.OdbcDataReader = command.ExecuteReader()
If reader.HasRows Then
Do While reader.Read()
money = Format(reader("charge") / 100, "###,###,##0.00")
Console.WriteLine(reader("agency") & " " & reader("invnum") & " " & "\$ ".Substring(1, 15 - money.Length) & money)
Loop
End If
reader.Close()
end using






And produces:AC 0107001 $ 1,897.75
AD 0107002 $ 933.33
AE 0107003 $ 366.00
AG 0107004 $ 138.19
AU 0107005 $ 1,832.00
etc.



HOWEVER! This (join) code does not work [Broken External Image]:http://forums.microsoft.com/MSDN/images/emoticons/smile_sarcastic.gif




Code Snippet

amtcredit = + or -
invamt = double with IMPLIED decimal, needs to be /100 later
sqlstring = "SELECT Currpacedata.txt.[agency], AgencyMapping.txt.[agencyname] FROM Currpacedata.txt inner join AgencyMapping.txt on Currpacedata.txt.[agency] = AgencyMapping.txt.[agency];"



Dim reader As Odbc.OdbcDataReader = command.ExecuteReader()
If reader.HasRows Then
Do While reader.Read()
money = Format(reader("charge") / 100, "###,###,##0.00")
Console.WriteLine(reader("agency") & " " & reader("agencyname") & " " & reader("invnum") & " " & "\$ ".Substring(1, 15 - money.Length) & money)
Loop
End If
reader.Close()





The SQL string seems to be the problem. You will notice that I have bracketed the tablenames. I also tried other combinations of bracketing:

.[field] and

.field.



With SQL string in the above code I get this error on the command.ExecuteReader() line: "ERROR [42000] [Microsoft][ODBC Text Driver] Syntax error in JOIN operation". Microsoft.com gives only a generic description - not helpful!



I tried bracketing the table/field names because when I did NOT bracket them I got the same JOIN syntax error message and I hypothesized that "SELECT Currpacedata.txt.agency, ..." might be problematic for the SQL parser.



I had first tried SQL syntax similar to this:
"select cpd.agency, ag.agencyname from currpacedata.txt cpd inner join agencymapping.txt ag on cpd.agency = ag.agency".



While I believe that is correct SQL syntax it generated this error:
"ERROR [07002] [Microsoft][ODBC Text Driver] Too few parameters. Expected 1."



If anyone out there has experience or theories I will surely be grateful for what help, answers or pointers you can give.



-Ken

Continue reading...