Why doesn't this sql statement work

[Tig201]

I am having difficulties with the following code. When I run it I get a zero length table but if I copy the sql statement out of the third text box go into access and paste it into the sql view and run it I get the one record I’m looking for.

        Dim CaddTxtFile As String = "C:\WINDOWS\Temp\Acad_Log_Out.txt"
        Dim CaddFile As String, SqlTxt As String
 
        Dim dSet As New DataSet
        Dim dAdapter As OleDb.OleDbDataAdapter
 
        Dim TxtStream As New System.IO.StreamReader(CaddTxtFile)
        CaddFile = TxtStream.ReadLine
        TxtStream.Close()
 
        CaddFile = CaddFile.Substring(0, 5) & "*"
        SqlTxt = "SELECT CADD_File_Name, Drawing_Number, Drawing_Title FROM Drawing_Log" & " WHERE CADD_File_Name LIKE '" & CaddFile & "'"
 
        dbCon.Open()
        dAdapter = New OleDb.OleDbDataAdapter(SqlTxt, dbCon)
        dAdapter.Fill(dSet, "Log")
 
        dbCon.Close()
 
        TxtCadd.Text = dSet.Tables("log").Rows.Count
        TxtDwg.Text = CaddFile
        TxtTitle.Text = SqlTxt
 

Any help would be appreciated.

[Plater]

Well I've never seen an unnamed table query return a table name. Get rid of the , "Log") section and use
dAdapter.Fill(dSet);

Note: You can also fill just a DataTable instead of a DataSet if you only are returning one table.

[Tig201]

If I change that then what do I change line 20 to to get the information back out of the dataset. Besides My code works fine if i comment out

& " WHERE CADD_File_Name LIKE '" & CaddFile & "'"
 

[Jerry Winston]

Your ad hoc sql

"SELECT CADD_File_Name, Drawing_Number, Drawing_Title FROM Drawing_Log" & " WHERE CADD_File_Name LIKE '" & CaddFile & "'"

Evaluates to:

SELECT CADD_File_Name, Drawing_Number, Drawing_Title FROM Drawing_Log WHERE CADD_File_Name LIKE 'someFileName'

The problem is your like statement is missing the underscore or wildcard character '%'. I think you want your code to look like this:

"SELECT CADD_File_Name, Drawing_Number, Drawing_Title FROM Drawing_Log" & " WHERE CADD_File_Name LIKE '%" & CaddFile & "%'"

or

"SELECT CADD_File_Name, Drawing_Number, Drawing_Title FROM Drawing_Log" & " WHERE CADD_File_Name LIKE '_" & CaddFile & "%'"

[Tig201]

Here is the Sql String that is compiled by the code.

SELECT CADD_File_Name,Drawing_Number,Drawing_Title
FROM Drawing_Log
WHERE CADD_File_Name LIKE 'C1130*'
 

I would have prefered 'C1130*' to be "C1130*"
But am unsure how to do this and the sql works in access with the single quote vice the double quote.

[Jerry Winston]

What's your database? SQL or Access? What versions?

[Tig201]

My database is Access 2003

[Jerry Winston]

It looks like you're coding in VB.NET. If you are use two " marks to insert a single " inside of quotes.

"SELECT CADD_File_Name,Drawing_Number,Drawing_Title
FROM Drawing_Log
WHERE CADD_File_Name LIKE ""C1130*""  "
 

Also, add a break point at the top line and inspect your data objects while your code is running.

[Tig201]

I've been playing with this and I think I've narrowed it down to the wild card character. Here is my Code

Dim CaddTxtFile As String = "C:\WINDOWS\Temp\Acad_Log_Out.txt"
Dim CaddFile As String, SqlTxt As String
 
Dim dSet As New DataSet
Dim dAdapter As OleDb.OleDbDataAdapter
 
Dim TxtStream As New System.IO.StreamReader(CaddTxtFile)
CaddFile = TxtStream.ReadLine
TxtStream.Close()
 
CaddFile = ControlChars.Quote & CaddFile.Substring(0, 5) & "*" & ControlChars.Quote
CaddFile = ControlChars.Quote & CaddFile.Substring(0, 5) & "-XX" & ControlChars.Quote
SqlTxt = "SELECT CADD_File_Name, Drawing_Number, Drawing_Title FROM Drawing_Log" & " WHERE CADD_File_Name LIKE " & CaddFile
 
dbCon.Open()
dAdapter = New OleDb.OleDbDataAdapter(SqlTxt, dbCon)
dAdapter.Fill(dSet, "Log")
dbCon.Close()
 
TxtCadd.Text = dSet.Tables("Log").Rows.Count
TxtDwg.Text = CaddFile
TxtTitle.Text = sqlTxt'dSet.Tables("Log").Rows(0).Item(2)
 

Line 11 returns 0 rows where Line 12 returns 1 row. Since the database I’m using has multiple variations for the last three characters I need to be able to use a wild card. Can anybody explain how to use a wild card in this situation? And before somebody suggest it have already tried

CaddFile = ControlChars.Quote & CaddFile.Substring(0, 5) & "???" & ControlChars.Quote
 

to no avail.

[Plater]

Try using the % as the wildcard anyway? The .NET warraper classes might convert it to a *, but use the % for consistency

[Tig201]

Thank you Plater that was the answer. Sorry Jerry you gave me the answer earlier and I missed it. I didn't realize the % replaced the *. Thanks everybody for all your help.