Need Query Help for Search

I am writing a small search feature to return a list of companies whose name "Begins with" a certain string (up to 5 chars) provided by the user via a textbox. I want the results to only return results that begin with the letter/letters specified. Below I will put the code that I came up with that isn't working quite how I expected. I am new to this so any assistance and short explanation would be very much apperciated.

sql="SELECT distinct cm.cmmst_id, cm.cm_compno, cm.cm_cname1 + ' ' + cm.cm_cname2 AS cm_cname1, cm_tele, cm_fax, cm_s16 "

sql=sql & "FROM cmmst cm "

sql=sql & "WHERE cm_cname1 + ' ' + cm_cname2 LIKE '%" companyNameBegins,"'","''") & "%' " sql=sql & "AND (cm_mbtyp='M' OR cm_mbtyp='SUBDIV') " sql=sql & "ORDER BY cm_s16 DESC, cm_cname1 ASC"

companyNameBegins is the string passed in by the user

Thanks,

Zoop

(1) Use parameterized Queries. Your code will look simpler and neater and you can avoid SQL Injection Attacks (Google for more info on this topic)

(2) Append the "%" in the value rather than in the SQL. Also if you want to retrieve records starting with a value the % should be at the end like "SELECT...WHERE column like 'startwith%'"

Here's some sample code:

Dim myCommand As SqlCommand
Dim myParam As SqlParameter

myCommand = New SqlCommand()
myCommand.Connection = objcon
myCommand.CommandText = "SELECT distinct cm.cmmst_id, cm.cm_compno, cm.cm_cname1 + ' ' + cm.cm_cname2 AS cm_cname1, cm_tele, cm_fax, cm_s16 FROM cmmst cm WHERE cm_cname1 + ' ' + cm_cname2 LIKE @.companyNameBegins AND (cm_mbtyp='M' OR cm_mbtyp='SUBDIV') ORDER BY cm_s16 DESC, cm_cname1 ASC"

myCommand.Parameters.Add(New SqlParameter("@.companyNameBegins ",SqlDbType.varchar,100))
myCommand.Parameters("@.companyNameBegins").Value = companyNameBegins & "%"

Try
If objCon.State = 0 Then objCon.Open()
'ExecuteReader and fill some dataContainer.

Catch exc As Exception
Response.Write(exc)
Finally
If objCon.State = ConnectionState.Open Then
objCon.Close()
End If
End Try

|||

Thanks for the assistance, much smoother this way.

zoop