search form

[bilal_fazlani]

heres a form i have created. its a search form.

http://img266.imageshack.us/img266/722/paymentsh.jpg

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,

suppose,

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.

ANYONE HELP
PLEASE SQL EXPERTS..ITs BEEN MANY DAYS I AM SEARCHING FOR SOLUTION.

[GeekByChoiCe]

You could try something like this:

Dim DummyParameters() As String = New String() {
		 If(tbx_transaction_id.Text = "", Nothing, String.Format("trans_id='{0}'", tbx_transaction_id.Text)),
		 If(tbx_cust_id.Text = "", Nothing, String.Format("customers.cust_id = '{0}'", tbx_cust_id.Text)),
		 If(tbx_amount.Text = "", Nothing, String.Format("trans_amount = '{0}'", tbx_amount.Text)),
		 If(tbx_name.Text = "", Nothing, String.Format("fname='{0}%' or lname='{0}%'", tbx_name.Text))}

		Dim finalParameters As IEnumerable(Of String) = DummyParameters.Where(Function(s) Not String.IsNullOrEmpty(s))

		Dim query As String = String.Format("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' {0}", If(finalParameters.Count > 0, String.Format(" AND {0}", String.Join(" or ", finalParameters)), Nothing))

		Debug.WriteLine(query)

[bilal_fazlani]

Its so advanced that I am reading it for 3 rd time...still trying to understand.. :)
thank you.

LOL

what's IEnumerable btw ?

and @geekbychoice, I had sent you a private msg earlier, I am sure u know hoe to fix it.

[GeekByChoiCe]

The IEnumerable exposes the enumerator, which supports a simple iteration over a collection of a specified type (in this case string).

For explaination:
DummyParameters contains either the Searchstring (if the textfield is not empty), or Nothing (if field is empty)

finalParameters:
Getting all searchstrings, that are not Nothing

[bilal_fazlani]

cool...
I have sent you my zipped project ,its created in vs2010.