SQL question in VB.net and Access

[Newbie]

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

 

[Cor Ligthert [MVP]]

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

 

[Tim Ferguson]

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

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