Searching between 2 dates and criteria

Getting Started
1

Hi,
I want to search Mysql Data using search between and a criteria, for example,
I have a database list of cars, I want to find specific brand based for 2 dates data search.

I have brand,
Volvo
mustang
bmw

I want to get the result of ‘bmw’ in specific date.

I tried this code, but no work,

sql="SELECT * FROM "+cmbOutlet.Text+"_report"+" WHERE Date BETWEEN'" + date1.text + "' AND '" + date2.text +"' AND carBrand ='" + TextField1.text + "'"

any helps

thanks,
Arief

2

You need to check the database.errormessage to see what’s wrong. I’d guess that the date values are not in the format MySQL expects them (they need to be in “SQL”-format, e.g. ‘2017-06-23’).

3

when I find out,

the result is shows, while I do type the criteria completely. how to make it work with criteria content.? I meant, if I type ‘musta’ for example, the result contains that word, shows on the list.

thanks
regards,
arief

4 
carBrand LIKE '%" + TextField1.text + "%'"

But please, when you work with user input, use Prepared Statements to prevent SQLInjections.

[code]Dim ps As PreparedSQLStatement

Database.Prepare( “SELECT * FROM ? WHERE Date BETWEEN ? AND ? AND carBrand LIKE ?” )

SearchString = “%” + SearchString + “%”

ps.Bind(0, DateValue_Start, MySQLPreparedStatement.MYSQL_TYPE_DATE)
ps.Bind(1, DateValue_End, MySQLPreparedStatement.MYSQL_TYPE_DATE)
ps.Bind(2, SearchString.ConvertEncoding(Encodings.ENCODINGOFYOURDATABASETABLE), MySQLPreparedStatement.MYSQL_TYPE_STRING)

RecordSet = Database.SQLSelect(ps)[/code]

(Written from my mind. Not actual working code)

5

thanks,

it works.

regards,
arief