CODEWITHSUNDEEP
c#asp.netsql-serversqlparameter
user4283143
user4283143

Reputation:

ASPX SQL Parameter

I'm trying to make a filtered list based on the user's input and lack of input.

I am able to filter if the user has inputted a whole name or just the first character of a name, I have 3 different textboxes (name, surname, country) that's used for the filter. In SQL Server I have created the code to do this and to filter even if they don't input a value into one or all of the textboxes. So I believe the SQL stored procedure is fine.

I believe my issue is when I'm trying to grab this information from a user in Visual Studio through textboxes and using the SQL Parameters to send the information to the stored procedure. I have noticed that I can't get an empty string into the SQL Paramete. Is there anyway I get pass an empty string to a SQL parameter?

Here is the bits of code...

The relevant bits from the stored procedure:

@Namenvarchar(100) = ' ',
@Surname nvarchar(100) = ' ',
@Country nvarchar(100) = ' '

AND IsNull(Input.Name, '')  LIKE '%' + @Name+ '%'
AND IsNull(Input.Surname, '') LIKE '%' + @Surname + '%' 
AND IsNull(Input.Country, '') LIKE '%' + @Country + '%'

And here is the code related to the Visual Studio side:

SqlCommand cmd = new SqlCommand("FilterBasedOnInput", con);
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter Name = new SqlParameter("@Name",  TBName.Text);
SqlParameter Surname= new SqlParameter("@Surname",  TBSurname.Text);
SqlParameter Country= new SqlParameter("@Country",  TBCountry.Text);


cmd.Parameters.Add(Name);
cmd.Parameters.Add(Surname);
cmd.Parameters.Add(Country);

con.Open();

So lets say in the DB I have the... 

Names ("Tom", "Jones", "Nick")
Surname ("To", "Jo", "Ni")
Country ("England", "USA", "France")

If the user enters in the in the Name textbox "O", then both Tom and Jones would come up with their 3 columns but if they entered into the Surname textbox as well with "To" then only Tom would appear with his 3 columns. I do have this working on SQL Server but not in Visual Studio ASPX.

Upvotes: 0

Views: 95

Answers (1)

user4283143
user4283143

Reputation:

Alright guys, I found the answer and it's in the GridView!

I was displaying the data and when you configure the gridview, there's a Default Value column, just put %

Now I am able to retrieve values even with an empty textbox or two.

Thank you all for your guidance, it lend to solving this problem.!

Upvotes: 0

Related Questions