heres a form i have created. its a search form.
it searches and lists the results in list view.
heres my mssql query :
select transactions.trans_id,transactions.cust_id,transactions.trans_amount," _ & "transactions.trans_date,customers.fname+' '+ customers.lname as customer from transactions inner join customers on transactions.cust_id=customers.cust_id " _ & "where trans_type='inv' and (trans_id= " + tbx_transaction_id.Text + " or customers.cust_id = " + tbx_cust_id.Text + " or trans_amount = " + tbx_amount.Text + " or (fname='" + tbx_name.Text + "%' or lname='" + tbx_name.Text + "%'))
Regardless of using AND or OR, it doesn't give me the desired results.
I have to type something in all textboxes for an exception to not to occur. Its obvious.
If I use % in sql query, its good for strings, but not for numbers.
for example if i dont type anything in customer_ID textbox and hit search,
what should be the query :
customers.cust_id like %%
customers.cust_id like '%%'
customers.cust_id like '%'
I want it to list all possible results if customer_id is not mentioned.
both examples i stated above are useless when I search with a customer ID,
customers.cust_id like %5%
it will just list all customers_id containing 5. like 5,51,52,54,505... 15,45,65,..so on.
PLEASE SQL EXPERTS..ITs BEEN MANY DAYS I AM SEARCHING FOR SOLUTION.