N
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
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