Wild Card Search Problem

[Steve Bishop]

I using a text box on my form to enter search criteria. How would I get
a subset of records based on the first 3 characters the user types. In
SQL, I can use "Like field%". I'm having trouble using something like
this with the variable in my code. Help appreciated. Thanks:

public function GetCustomers(byval CustomerName as string) as dataset
dim strconn as string
dim sqlstring as string
dim myconn as OdbcConnection
dim myadap as OdbcDataAdapter
dim ds as new dataset()

strconn = "dsn=SOTAMAS90AUTO;uid=sys;pwd=Huey;"
sqlstring = "Select Division, CustomerNumber, CustomerName,
AddressLine1 FROM AR1_CustomerMaster"
sqlstring += " where CustomerName = '" + CustomerName +"'"

 

[Guest]

Tr
sqlstring += " where CustomerName LIKE '%" + CustomerName +"%'

Suresh


----- Steve Bishop wrote: ----

I using a text box on my form to enter search criteria. How would I ge
a subset of records based on the first 3 characters the user types. I
SQL, I can use "Like field%". I'm having trouble using something lik
this with the variable in my code. Help appreciated. Thanks

public function GetCustomers(byval CustomerName as string) as datase
dim strconn as strin
dim sqlstring as strin
dim myconn as OdbcConnectio
dim myadap as OdbcDataAdapte
dim ds as new dataset(

strconn = "dsn=SOTAMAS90AUTO;uid=sys;pwd=Huey;
sqlstring = "Select Division, CustomerNumber, CustomerName
AddressLine1 FROM AR1_CustomerMaster
sqlstring += " where CustomerName = '" + CustomerName +"'


*** Sent via Developersdex http://www.developersdex.com **
Don't just participate in USENET...get rewarded for it

 

[Steve Kass]

First of all, I hope this isn't your real uid and password. Second, be
sure you aren't just taking whatever the user wants to type into the
text box, since if you are, you are opening yourself up to a SQL
injection attack if the login for the connection has permission to do
anything but select from the AR1_CustomerMaster table. A much better
solution is to write a SQL stored procedure without dynamic SQL, then
pass CustomerName to that procedure as a parameter. You can then
restrict the user [sys] to have permission only to execute that stored
procedure and others it needs.

SK

 

[mgwalm]

Use somethng like the following

sqlstring = "Select Division, CustomerNumber, CustomerName,
AddressLine1 FROM AR1_CustomerMaster"
sqlstring += " where CustomerName like '" + CustomerName.Substring(0, 3) + "%'"

Regards
Michael