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.

[code=vbnet]
Dim CaddTxtFile As String = "C:\WINDOWS\Tem p\Acad_Log_Out. txt"
Dim CaddFile As String, SqlTxt As String

Dim dSet As New DataSet
Dim dAdapter As OleDb.OleDbData Adapter

Dim TxtStream As New System.IO.Strea mReader(CaddTxt File)
CaddFile = TxtStream.ReadL ine
TxtStream.Close ()

CaddFile = CaddFile.Substr ing(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.OleDbData Adapter(SqlTxt, dbCon)
dAdapter.Fill(d Set, "Log")

dbCon.Close()

TxtCadd.Text = dSet.Tables("lo g").Rows.Cou nt
TxtDwg.Text = CaddFile
TxtTitle.Text = SqlTxt
[/code]

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(d Set);

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
[code=vbnet]
& " WHERE CADD_File_Name LIKE '" & CaddFile & "'"
[/code]

[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.
[code=vbnet]
SELECT CADD_File_Name, Drawing_Number, Drawing_Title
FROM Drawing_Log
WHERE CADD_File_Name LIKE 'C1130*'
[/code]
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
[code=vbnet]
Dim CaddTxtFile As String = "C:\WINDOWS\Tem p\Acad_Log_Out. txt"
Dim CaddFile As String, SqlTxt As String

Dim dSet As New DataSet
Dim dAdapter As OleDb.OleDbData Adapter

Dim TxtStream As New System.IO.Strea mReader(CaddTxt File)
CaddFile = TxtStream.ReadL ine
TxtStream.Close ()

CaddFile = ControlChars.Qu ote & CaddFile.Substr ing(0, 5) & "*" & ControlChars.Qu ote
CaddFile = ControlChars.Qu ote & CaddFile.Substr ing(0, 5) & "-XX" & ControlChars.Qu ote
SqlTxt = "SELECT CADD_File_Name, Drawing_Number, Drawing_Title FROM Drawing_Log" & " WHERE CADD_File_Name LIKE " & CaddFile

dbCon.Open()
dAdapter = New OleDb.OleDbData Adapter(SqlTxt, dbCon)
dAdapter.Fill(d Set, "Log")
dbCon.Close()

TxtCadd.Text = dSet.Tables("Lo g").Rows.Cou nt
TxtDwg.Text = CaddFile
TxtTitle.Text = sqlTxt'dSet.Tab les("Log").Rows (0).Item(2)
[/code]
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
[code=vbnet]
CaddFile = ControlChars.Qu ote & CaddFile.Substr ing(0, 5) & "???" & ControlChars.Qu ote
[/code]
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.