Could someone please help with an SQL question.  First Let me explain
what I'm trying to accomplish.  I have a small Address book application
that has a search feature.  Currently you have to type the Firstname
and LastName exactly as it was entered in the Access Database record
for the routine to find it.  I would like to modify the SQL statement
using the LIKE keyword and the % wildcard so that a user can enter
something close and have the search routine find the record.  Here is
the SQL statement I have been working on.

searchName = "'" & "%" & LCase(objSearch.txtFName.Text) & " " _
                         & LCase(objSearch.txtLname.Text) & "%" & "';"

searchSQL = "SELECT UserID, FirstName & Chr(34) & Chr(32) & Chr(34) &
LastName AS Name," _
                        & " CompanyName, Address, City, State, Zip,
Email, UserNote, Phone, Ext," _
                        & " Cell, Fax FROM Address WHERE Lcase(Name)
LIKE " & searchName

What I'm trying to do is concatenate the FirstName and LastName fields
as an alias field "Name" and then convert that to lower case to be
compared in the LIKE test.

ie:  FirstName = "Bob"
     LastName = "Smith"

and then basicly SELECT UserID, FirstNamd+' '+LastName AS Name FROM
Address WHERE Lcase(Name) LIKE '%Bob%'

Verses

WHERE Lcase(FirstName) = 'Bob' AND Lcase(LastName) = 'Smith'


Here is the actual search routine Any Help on this would really be
appreaciated.


******************************************
Private Sub Search_rec(ByVal type As Integer)

        Dim searchSQL As String
        Dim searchName As String
        Dim objSearch As New Search



        If type = 1 Then
            objSearch.rdoByName.Checked = True
            objSearch.rdoByComp.Checked = False
        Else
            objSearch.rdoByComp.Checked = True
            objSearch.rdoByName.Checked = False
        End If
        objSearch.ShowDialog()

        If objSearch.DialogResult <> DialogResult.OK Then
            Exit Sub
        End If




        If objSearch.rdoByName.Checked Then

            searchName = "'" & "%" & LCase(objSearch.txtFName.Text) & "
" _
                         & LCase(objSearch.txtLname.Text) & "%" & "';"

           searchSQL = "SELECT UserID, FirstName & Chr(34) & Chr(32) &
Chr(34)" _
                    & "LastName AS Name, CompanyName, Address, City,
State, Zip," _
                    & " Email, UserNote, Phone, Ext, Cell, Fax FROM
Address WHERE" _
                    & " Lcase(Name) LIKE " & searchName

        Else
            searchSQL = "SELECT * FROM Address WHERE Lcase(CompanyName)
LIKE " & "'" _
                & "%" & LCase(objSearch.txtCompName.Text) & "%" & "'"""
        End If


        Dim daSearch As New OleDbDataAdapter
        Dim dsSearch As New DataSet
        Dim dv As DataView
        Dim i As Integer

        Dim objSearchcommand As New OleDbCommand(searchSQL,
objConnection)
        daSearch.SelectCommand = objSearchcommand

        Try

            daSearch.Fill(dsSearch, "Search")

        Catch oledbexceptionErr As OleDbException
            MessageBox.Show(oledbexceptionErr.Message, "Access SQL")
        End Try

        If dsSearch.Tables("Search").Rows.Count = 1 Then
            i = dsSearch.Tables("Search").Rows(0).Item("UserID")

            dv = New DataView(dsUsers.Tables("Addressbook"))
            dv.Sort = "UserID"
            intIndex = dv.Find(i)

            Call NavigateRecords()
            Call Count()
        Else
            MsgBox("No matching records found",
MsgBoxStyle.Information)
        End If

        objSearchcommand.Dispose()
        daSearch.Dispose()
        dsSearch.Dispose()
        objSearchcommand = Nothing
        daSearch = Nothing
        dsSearch = Nothing

    End Sub

Newbie,

As advice bring first your question back to the elementair part of your
question (only one or two fields by instance). We have now to analyze a hug
complex part of code (not the language vb part but the SQL part). Maybe
there is somebody who want to do that, but I am sure not much.

As second advice, there is a special newsgroup for this kind of ADONET
questions.

microsoft.public.dotnet.framework.adonet

As thirth one, be aware that in the SQL string the Like is different for
SQLClient and for Jet (access).

I hope this helps something,

Cor

Show quoteHide quote

@g43g2000cwa.googlegroups.com:

  jetCriterion = _
     "FirstName LIKE ""*" & objSearch.txtFName.Text & "*""" & _
     " AND LastName LIKE ""*" & objSearch.txtLName.Text & "*"""


  jetSQL = "SELECT etc etc etc" & jetCriterion



I think that will be more likely to get the result you are after.

HTH

Tim F

trying to understand classes
initialize a complex constant in a class
SendKeys Doesn't Always Work?
MDI Child Forms
Resetting Properties Panel
RaiseEvent not working correctly
Text from a Word.Doc
Putting an application into my vb.net control (like a panel)
How to query the key and value pair in ComboBox
User interface ideas?