How to write an sqlite query for the following scenario?

Question

3.67/5 (2 votes)

See more:

Here is my query for wpf form

Query = "select Cust_Id,Card_Number,Clients_Title,Address_Current,Phone_Number,Mobile_Number from Customer_New Where 1=1";
try
{
if (txt_title.Text != "")
Query += " and Clients_Title Like '%" + txt_title.Text + "%'";
if (txt_address.Text != "")
Query += " and Address_Current Like '%" + txt_address.Text + "%'";
if (txt_phone.Text != "")
Query += " and Phone_Number Like '%" + txt_phone.Text + "%'";
if (txt_mobile.Text != "")
Query += " and Mobile_Number Like '%" + txt_mobile.Text + "%'";
if (cbo_location.Text != "")
Query += " and AreaLocation Like '%" + cbo_location.Text + "%'";
}

catch { }

I want to report viewer query data like my wpf form . Here is the query that i am trying in report viewer

SELECT Cust_Id, Clients_Title, Card_Number, Key_Person, Address_Current, Phone_Number, Mobile_Number, AreaLocation
FROM Customer_New
WHERE (Clients_Title = @Clients_Title) OR
(Address_Current = @Address_Current) OR
(Phone_Number = @Phone_Number) OR
(Mobile_Number = @Mobile_Number) OR
(AreaLocation = @AreaLocation)

Can anyone tell me query for report viewer like wpf form . Note :-

I cant use cant use C# controls in report viewer.Here in report viewer i can only use sql

What is needed in report viewers query is:

When are all string of where clause are null then my report viewer
should select query is:

Query = "select Cust_Id,Card_Number,Clients_Title,Address_Current,Phone_Number,Mobile_Number from Customer_New ";

When any two string of where clause are not matching corresponding row db then nothing

will be displayed
Lastly, selection will be made if just one condition of where clause is provided

Posted 12-Nov-13 18:16pm

Member 8840306

Updated 12-Nov-13 20:42pm

v3

Add a Solution

Comments

Member 8840306 13-Nov-13 0:17am

Note : - I recently found that sqlite don't support store procedure .So i have only option to make the query in report viewer data set .

4 solutions

Solution 3

Can this work?

SQL

<pre>SELECT 
	Cust_Id, Clients_Title, Card_Number, Key_Person, 
	Address_Current, Phone_Number, Mobile_Number, AreaLocation
FROM 
	Customer_New
WHERE 
	(@Clients_Title IS NULL OR Clients_Title = @Clients_Title) AND
	(@Address_Current IS NULL OR Address_Current = @Address_Current) AND
	(@Phone_Number IS NULL OR Phone_Number = @Phone_Number) AND
	(@Mobile_Number IS NULL OR Mobile_Number = @Mobile_Number) AND
	(@AreaLocation IS NULL OR AreaLocation = @AreaLocation)

Posted 12-Nov-13 21:38pm

Jobless Creature

Comments

Member 8840306 13-Nov-13 4:06am

It is not giving mentioned functionality

Jobless Creature 13-Nov-13 7:16am

May be a empty string is getting passed I guess. Try to set the parameter as NULL is the parameter is empty.

Solution 1

I think u can write case statements in the sql query like in this Link. Try this

Thanks
Ganesh

Posted 12-Nov-13 21:08pm

Ganesh KP

Comments

Member 8840306 13-Nov-13 4:12am

Here is my case statement that i build by getting guide from this link
http://stackoverflow.com/questions/8730933/case-statement-in-where-clause-sql-server
SELECT Cust_Id, Clients_Title, Card_Number, Key_Person, Address_Current, Phone_Number, Mobile_Number, AreaLocation
FROM Customer_New
WHERE 1 = CASE WHEN @Clients_Title != " " THEN Clients_Title AND
WHEN @Address_Current != " " THEN Address_Current AND
WHEN @Phone_Number != " " THEN Phone_Number AND
WHEN @Mobile_Number != " " THEN Mobile_Number AND
WHEN @AreaLocation != " " THEN AreaLocation
END
but it is giving syntax error.-please correct it

Member 8840306 13-Nov-13 4:15am

I have also replaced @Clients_Title != " " with @Clients_Title != '' but still give syntax error

Solution 2

My suggestion is you create a stored procedure. Inside that stored procedure you return data based on stored procedure parameter and your report viewer you execute that sp. For example

SQL

create proc myproc
(
    @id int
    ,@title
)
as
begin
  if isnull(@id,'') == '' and isnull(@number,'') begin
        select all * from MyTable;
        return
  end
  else begin
       -- create user dynamic sql based on your stored procedure parameters and execute it exec sp_executeSql(@sql) just like you create sql inside wpf form.   
  end
end

Let me know if you understand my solution.

Posted 12-Nov-13 21:20pm

S. M. Ahasan Habib

Updated 12-Nov-13 21:21pm

v2

Comments

Member 8840306 13-Nov-13 4:07am

see my comment above :
Note : - I recently found that sqlite don't support store procedure .So i have only option to make the query in report viewer data set .

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 8840306 · Accepted Answer · 2013-12-02T23:18:00

SQL

SELECT        Cust_Id, Clients_Title, Card_Number, Key_Person, Address_Current, Phone_Number, Mobile_Number, AreaLocation
FROM            Customer_New
    where        (CASE
        WHEN @Clients_Title != ''   THEN Clients_Title=@Clients_Title
      ELSE
         NULL IS NULL
      END)
      AND(CASE
        WHEN @Address_Current != '' THEN Address_Current =@Address_Current
      ELSE
         NULL IS NULL
      END)
              AND(CASE
        WHEN @Phone_Number != '' THEN Phone_Number=@Phone_Number
      ELSE
         NULL IS NULL
      END)
      AND(CASE
        WHEN @Mobile_Number != '' THEN Mobile_Number=@Mobile_Number
      ELSE
         NULL IS NULL
      END)
      AND(CASE
        WHEN @AreaLocation != '' THEN AreaLocation =@AreaLocation
      ELSE
         NULL IS NULL
      END)

How to write an sqlite query for the following scenario?

4 solutions

Solution 3

Solution 1

Solution 2

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us