DateReader I do not understand

[lrfcbabe]

Is there a reason why one sql statement works and not the other?

Public Sub ReadMyData(ByVal myString As String)
'This first statement is returned from the cbo_click and works fine
'SELECT * FROM tblA_Files ORDER BY File;
'This statement is returned by the bthSearch_Click and does not work
'SELECT * FROM tblA_Files WHERE Title LIKE "*lake*" AND Title LIKE "*water*" ORDER BY FILE;
'Both statements work fine in Access sql view
If TestDBConn() Then
Dim myCommand As New OleDbCommand(myString, cngisfmDB)
drgisfmDB = myCommand.ExecuteReader()
While drgisfmDB.Read()
.
.
'populate Listview
End While
End If
End Sub

Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click

If TestDBConn() Then
.
.
ListView1.Items.Clear()
For i = 0 To cboDocumentGroup.Items.Count - 1
txtGroup = cboDocumentGroup.Items(i).ToString
If txtGroup <> "All" Then
If rdoAnd.Checked = True Then
If txtS1.Length > 0 And _
txtS2.Length > 0 Then
strcngisfmdb = "SELECT * FROM tbl" + cboDocumentGroup.Items(i).ToString + _
" WHERE Title LIKE ""*" + txtS1 + "*"" AND Title LIKE ""*" + txtS2 + "*"" ORDER BY FILE;"
ReadMyData(strcngisfmdb)
drgisfmDB.Close()
End If
End If
End If
Next i
End If
Me.Text = ListView1.Items.Count.ToString
End If
bolSearch = False
txtGroup = ""
ListView1.Select()
cngisfmDB.Close()
End Sub


Private Sub cboDocumentGroup_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboDocumentGroup.SelectedIndexChanged
If cboDocumentGroup.Text = "All" Then
.
.
Else
ListView1.Items.Clear()
strcngisfmdb = "SELECT * FROM tbl" + cboDocumentGroup.Text + " ORDER BY File;"
ReadMyData(strcngisfmdb)
ListView1.Select()
drgisfmDB.Close()
cngisfmDB.Close()
End If
End Sub

 

[Andrzejek]

"This statement is returned by the bthSearch_Click and does not work" - so, what does it do? Any errors?



Have fun.

---- Andy

 

[SkipVought]

strcngisfmdb = "SELECT * FROM tbl" + cboDocumentGroup.Items(i).ToString + _
" WHERE Title LIKE "[b][red]'[/red][/b]*" + txtS1 + "*[b][red]'[/red][/b]" AND Title LIKE "[b][red]'[/red][/b]*" + txtS2 + "*[b][red]'[/red][/b]" ORDER BY FILE;"

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lrfcbabe]

No Errors just nothing in the listview. If I select an item from the combo box then I get my listview to populate.
This did not work.
strcngisfmdb = "SELECT * FROM tbl" + cboDocumentGroup.Items(i).ToString + _
" WHERE Title LIKE ""*" + txtS1 + "*"" AND Title LIKE ""*" + txtS2 + "*"" ORDER BY FILE;"
Skip, I think you ment this, but did not work.
strcngisfmdb = "SELECT * FROM tbl" + cboDocumentGroup.Items(i).ToString + _
" WHERE Title LIKE '*" + txtS1 + "*' AND Title LIKE '*" + txtS2 + "*' ORDER BY FILE;"
I even tried this.
strcngisfmdb = "SELECT * FROM tbl" + cboDocumentGroup.Items(i).ToString + _
" WHERE Title LIKE " + """*" + txtS1 + "*""" + " AND Title LIKE " + """*" + txtS2 + "*""" + " ORDER BY FILE;"
Maybe I will reinstall .Net. I do not see anything in my Recent Project list on startup. Maybe it is XP 32.
Maybe I need a break.

 

[SkipVought]

Let's consider your intent. This statement

'SELECT * FROM tblA_Files WHERE Title LIKE "*lake*" AND Title LIKE "*water*" ORDER BY FILE;

would return data for a Title like
[tt]
Water front property on lake
[/tt]
but would NOT return for
[tt]
Lake front property
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Andrzejek]

It would be better if you would show us what you get when you do:
[tt]
Debug.Print strcngisfmdb
[/tt]

What Skip is showing you is: you may want to use OR instead of AND in your statement:
[tt]
SELECT * FROM tblA_Files WHERE Title LIKE '*lake*' [blue]OR[/blue] Title LIKE '*water*' ORDER BY FILE;
[/tt]

Have fun.

---- Andy

 

[lrfcbabe]

strcngisfmdb = "SELECT * FROM tbl" + cboDocumentGroup.Items(i).ToString + _
" WHERE Title LIKE ""*" + txtS1 + "*"" AND Title LIKE ""*" + txtS2 + "*"" ORDER BY FILE;"
returns
SELECT * FROM tblA_Files WHERE Title LIKE "*lake*" AND Title LIKE "*water*" ORDER BY FILE;

strcngisfmdb = "SELECT * FROM tbl" + cboDocumentGroup.Text + " ORDER BY File;"
returns
SELECT * FROM tblA_Files ORDER BY File;

Just can't hack it..oh wow I just had a thought that it might be the .tostring, but no.

 

[Andrzejek]

So, does it work for you the way you want it to? I would guess: no, otherwise you would say so.

If this statement:[tt]
SELECT * FROM tblA_Files WHERE Title LIKE "*lake*" AND Title LIKE "*water*" ORDER BY FILE[/tt]
does not give you want you want, please give a sample of your data and the expected outcome of your Select statement.

Have fun.

---- Andy

 

[lrfcbabe]

That is correct Skip.

strcngisfmdb = "SELECT * FROM tbl" + cboDocumentGroup.Items(i).ToString + _
" WHERE Title LIKE ""*" + txtS1 + "*"" AND Title LIKE ""*" + txtS2 + "*"" ORDER BY FILE;"
returns
SELECT * FROM tblA_Files WHERE Title LIKE "*lake*" AND Title LIKE "*water*" ORDER BY FILE;

SELECT * FROM tbl" + cboDocumentGroup.Text + " ORDER BY File;
returns
SELECT * FROM tblA_Files ORDER BY File; 'This works

I hope I did not just post this twice.

 

[lrfcbabe]

I am not seeing anything past Andys' second comment. I can't figure this site out.

 

[lrfcbabe]

OK I can see all the comments now.
I pasted SELECT * FROM tblA_Files WHERE Title LIKE "*lake*" AND Title LIKE "*water*" ORDER BY FILE;
into access and this returned one record.

I pasted SELECT * FROM tblA_Files WHERE Title LIKE '*lake*' AND Title LIKE '*water*' ORDER BY FILE;
into access with the same results.
This is XP VB2008 and access 2003. This can't be the sql statement it has to be something else. This is too easy.
How do I attach a screen shot of the query results?

 

[Andrzejek]

I see your responces, but I still do not see any answers to questions posted 16 May 12 8:40

If you expect help from anybody here, you need to do your part and provide requested information. "this returned one record" is NOT it. Did you expect just one record? If not, how many records did you expect? And out of what data?

You see, I am NOT asking these questions just to ask questions. I would like to get the information from you because you are the only person who has this information, and if you are NOT going to provide it, nobody will be able to help you other than just guessing what the problem may be.

Have fun.

---- Andy

 

[SkipVought]

That is correct Skip.

You have failed to answer the questions addressed to you.

WHAT results did you get from WHAT query?

WHAT results did you expect?

Please post EXAMPLES of each!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Andrzejek]

This is my guess (again) of what you data may look like:

Drink your water, it is good for you.
The old man and a lake.
There is some clean water in Lake Michigan.
There is some clean [blue]water[/blue] in [blue]lake[/blue] Michigan.
My Lake And My Water Lilies.
The [blue]lake[/blue] was empty with no [blue]water[/blue] in it.

Only rows with [blue]BLUE[/blue] pieces would show based on your Select statement: there have to be 'water' and 'lake' in your Title for it to show. No 'Lake' or 'wAtEr'.

Have fun.

---- Andy

 

[lrfcbabe]

I used the percent sign and it is working great.
strcngisfmdb = strcngisfmdb + " WHERE Title LIKE ""%" + txtS1 + "%"" AND Title LIKE ""%" + txtS2 + "%"" ORDER BY FILE;"
passes
SELECT * FROM tblTest WHERE Title LIKE "%Lake%" AND Title LIKE "%water%" ORDER BY FILE;
And this returns all occurences of the field Title(memo) having lake and water anywhere within that field.
Case sensitvety does not apply. These are all returned.
Lake Water
lake water
this tastes like lakE WaTer
this Water tastes like Lake Michigan

Sorry if I wasn't more clear with what I was looking for.
Thanks