Unable To Fetch Records From SQL Table To DataGridView In C# Using Stored Procedure

Question

I have a windows form where the user can input multiple values in multiple textboxes for faster search results. But when running, it takes only 1st parameter i.e., the fullname and ignores the other. Don't know the reason why is it so. Am getting the full table in the result which I don't want. I have created a stored procedure.

CREATE PROCEDURE USP_SELECT_CUSTOMERS (
    @fullName VARCHAR(100) = '',
    @Address VARCHAR(100) = '',
    @ContactNumber VARCHAR(15) = ''
)
AS BEGIN
    SELECT * FROM Customers 
        WHERE ((ContactName LIKE @fullName+'%') OR (@fullName = ''))
            OR ((Address LIKE @Address+'%') OR (@Address = ''))
            OR ((Phone LIKE @ContactNumber+'%') OR (@ContactNumber = ''))
END

Here's how am i calling the stored procedure in my program :=>

private void btnSubmit_Click(object sender, EventArgs e)
        {
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("USP_SELECT_CUSTOMERS", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@fullName", txtFullName.Text);
                cmd.Parameters.AddWithValue("@Address", txtAddress.Text);
                cmd.Parameters.AddWithValue("@ContactNumber", txtContactNumber.Text);                
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    dataGridView1.Visible = true;
                    dataGridView1.DataSource = ds.Tables[0];
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message.ToString());
            }
            finally
            {
                con.Close();
            }
        }

What I want is even if the 1 textbox if filled, the stored procedure should give the desired output. Kindly help me out in this.

Thanks.

Those ORs at the beginning of the 2nd two lines should probably be ANDs? — noelicus
– noelicus, Commented Mar 1, 2014 at 10:16
Can you add the code you are using to call the stored procedure? As it stands, the stored procedure you're using will return the full table unless you are passing all three parameters as values which are not '' to the stored procedure. Changing the ORs to ANDs would fix that. — thudbutt
– thudbutt, Commented Mar 1, 2014 at 10:17
Raging Bull's answer below will fix your problem with the whole table being returned when only one textbox is filled. Do Address and ContactNumber still get ignored if you make the below changes? — thudbutt
– thudbutt, Commented Mar 1, 2014 at 10:27
@user3095539 - I have added an answer that in combination with Raging Bull's should give you the results you're after. — thudbutt
– thudbutt, Commented Mar 1, 2014 at 11:20

Raging Bull · Accepted Answer · 2014-03-01 12:26:30Z

1

You should AND instead of OR. Try this:

CREATE PROCEDURE USP_SELECT_CUSTOMERS (
    @fullName VARCHAR(100),
    @Address VARCHAR(100),
    @ContactNumber VARCHAR(15)
)
AS BEGIN
    SELECT * FROM Customers 
        WHERE ((ContactName LIKE @fullName+'%') OR (@fullName = ''))
            AND ((Address LIKE @Address+'%') OR (@Address = ''))
            AND ((Phone LIKE @ContactNumber+'%') OR (@ContactNumber = ''))
END

It will give you the desired result.

See SQL Fiddle

EDIT: As per your requirement, use this query:

CREATE PROCEDURE USP_SELECT_CUSTOMERS 
(
        @fullName VARCHAR(100),
        @Address VARCHAR(100),
        @ContactNumber VARCHAR(15)
)
AS 
BEGIN
    IF LEN(@fullName)=0
        SET @fullName='$$$'
    IF LEN(@Address)=0
        SET @Address='$$$'
    IF LEN(@ContactNumber)=0
        SET @ContactNumber='$$$'

    SELECT * FROM Customers 
        WHERE ((ContactName LIKE @fullName+'%'))
           OR ((Address LIKE @Address+'%'))
           OR ((Phone LIKE @ContactNumber+'%'))
END

Added a simple trick. See SQL Fiddle.

Replace $$$ with anything which will not be included in ContactName,Address and Phone fields.

edited Mar 1, 2014 at 12:26

answered Mar 1, 2014 at 10:18

Raging Bull

18.8k13 gold badges52 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user3095539 Over a year ago

Not a single result if I use AND instead of OR.. I want that even if a single textbox is filled, the result should come.

user3095539 Over a year ago

Ok. Nice. 1 more question, what if I want multiple rows to be fetched ? Like if there are 2 people with same name, how to show should i display it ?

Raging Bull Over a year ago

Since you are using LIKE operator, it will select both of them who have same name. See this example

user3095539 Over a year ago

thanks bud. now what if user inputs name as maria and address as Åkergatan 24. the result is not being fetched. I want both the results from northwind database , hence in my question, i had used OR

Raging Bull Over a year ago

Now I get your problem. See Edit in my answer.

|

Kunal Ved · Accepted Answer · 2014-03-01 11:17:27Z

0

I agree with what @Baljeet said ... it should fetch the desired results. Make Sure you have done the following 1) Intitilise the the parameter value as '' (blank) if varchar eg @fullName VARCHAR(100) = ''

and in where condition like given below, people tend to make mistake in the second condtion of OR. Make sure the condtion is Where ( 'Field Name' like '@parametername' OR @parametername ='')

 WHERE ((ContactName LIKE @fullName+'%') OR (@fullName = ''))

answered Mar 1, 2014 at 11:17

Kunal Ved

12 bronze badges

Comments

thudbutt · Accepted Answer · 2014-03-01 11:22:47Z

0

As well as carrying out the changes in Raging Bull's answer, try adding ISNULL to your second check for each parameter. So

@fullname = ''

becomes

ISNULL(@fullname, '') = ''

and do the same for the other two parameters.

From memory Sql Server will only use the default value you specify for the parameters, if you don't pass the parameter at all, if you do pass the parameter but the value is null then your query will only work with the change above.

edited Mar 1, 2014 at 11:22

answered Mar 1, 2014 at 10:48

thudbutt

1,5411 gold badge20 silver badges33 bronze badges

Comments

Dipendu Paul · Accepted Answer · 2014-03-01 14:57:09Z

My suggestion would be to define the stored procedure with default value of parameters as NULL. Database is good at handling NULL values and it would keep the stored procedure definition simple.

Then pass the parameters from C# conditionally:

if(!String.IsNullOrEmpty(txtFullName.Text)) 
    cmd.Parameters.AddWithValue("@fullName", txtFullName.Text);
//...add other parameters the same way

Stored procedure should be defined as below:

CREATE PROCEDURE USP_SELECT_CUSTOMERS (
    @fullName VARCHAR(100) = NULL,
    @Address VARCHAR(100) = NULL,
    @ContactNumber VARCHAR(15) = NULL
)
AS BEGIN
    SELECT * FROM Customers 
        WHERE ContactName LIKE @fullName+'%'
            OR Address LIKE @Address +'%'
            OR Phone LIKE @ContactNumber+'%'
            --in case you want to select all rows if all the parameters are NULL then add
            --this condition too 
            --OR 1 = CASE WHEN COALESCE(@fullName, @Address, @ContactNumber,'') = '' THEN 1 ELSE 0 END
END

Baljeetsingh Sucharia · Accepted Answer · 2014-03-02 02:03:09Z

0

IMO,

this should do

CREATE PROCEDURE USP_SELECT_CUSTOMERS (
    @fullName VARCHAR(100) = '',
    @Address VARCHAR(100) = '',
    @ContactNumber VARCHAR(15) = ''
)
AS BEGIN
    SELECT * FROM Customers 
        WHERE ((ContactName LIKE @fullName+'%') OR (@fullName = ''))
            AND ((Address LIKE @Address+'%') OR (@Address = ''))
            AND ((Phone LIKE @ContactNumber+'%') OR (@ContactNumber = ''))
END

using northwind i get following , command with EXEC USP_SELECT_CUSTOMERS 'maria','','' enter image description here

exec USP_SELECT_CUSTOMERS 'maria','Åkergatan 24','' --result row is displayed now check the next pic enter image description here

** exec USP_SELECT_CUSTOMERS 'maria','Åkergatan 24 ','' ** --because of extra space end of address parameter you could see no result enter image description here

edited Mar 2, 2014 at 2:03

answered Mar 1, 2014 at 10:21

Baljeetsingh Sucharia

2,0772 gold badges20 silver badges39 bronze badges

5 Comments

user3095539 Over a year ago

It is giving, but the whole table is being fetched as result and not the desired result.

Baljeetsingh Sucharia Over a year ago

can you share what extact values were send ?

user3095539 Over a year ago

if you are having northwind database in sql management studio, you can do it coz i am also sending the values from my form like for name i am giving Maria and when i press the search button, not a single result is fetched..

user3095539 Over a year ago

same here. now what if user inputs name as maria and address as Åkergatan 24. the result is not being fetched. i want both the results , hence in my question, i had used OR

Baljeetsingh Sucharia Over a year ago

i tried again with name and address inputs i see the results !! there is no reason it shouldnt come for you except the address is not exact. see the updated picture in a while and i suggest you update sp with '%' + @Address+'%'

Collectives™ on Stack Overflow

Unable To Fetch Records From SQL Table To DataGridView In C# Using Stored Procedure

5 Answers 5

6 Comments

Comments

Comments

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

6 Comments

Comments

Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related