Using Parameters

aadz5 · April 25th, 2004, 05:31 AM

Hi guys,

can you use parameters to select everything from a column??

For example I have a column called areas, a person enters an area and that area is put into the SQL statement. But I want to have a selection where the person can select all the areas for this column. Is this possible using parameters??

Any Ideas??

Adz - The World is not enough

melvik · April 26th, 2004, 12:16 AM

if u r using Textbox u can choose ComboBox instead!
its all from me! Sorry but u were not CLEAR!

Always:),
Hovik Melkomian.

aadz5 · April 27th, 2004, 03:22 AM

I have a dropdownlist which has many area values i.e Portsmouth, Southampton, Northampton, all, one of the values within the drop down list is all. This value, when selected should show all the types of selections. I am using parameters to to select from the database. I want to know how to select everything from a column using parameters, here is my code: -

objCommand.Parameters.Add("RentingPrice", OleDbType.Integer, 6, "RentingPrice");
  objCommand.Parameters.Add("HouseHold", OleDbType.VarChar, 32, "HouseHold");
  objCommand.Parameters.Add("Smoking", OleDbType.VarChar, 32, "Smoking");
  objCommand.Parameters.Add("Furnished", OleDbType.VarChar, 32, "Furnished");

  objCommand.Parameters["RentingPrice"].Value = Payment.Text;
  objCommand.Parameters["HouseHold"].Value = strAreaFilter;
  objCommand.Parameters["Smoking"].Value = strSmokingFilter;
  objCommand.Parameters["Furnished"].Value = strFurnished;

strAreaFilter gets it value from the dropdownlist. It uses OleDbType.VarChar, I dont kow if you can use this to select everything?? Any ideas guys??

Adz - The World is not enough

bmains · April 27th, 2004, 07:01 AM

You shouldn't supply the parameter if you want all records. Usually you supply parameters when you want to limit the criteria returned. What does your SQL look like?

Brian

Imar · April 27th, 2004, 07:28 AM

Alternativly, you can pass in null (using DBNull.Value) as the parameter's value.

Then inside your sproc you can compare the parameter with null and have all records returned. Something like this:

Code:

...
@HouseHold varchar(32)
...
SELECT Column1, Column2 FROM MyTable WHERE (HouseHold = @HouseHold OR @HouseHold = null)

When @HouseHold has a value, all records with the matching HouseHold will be filtered. If @HouseHold is null, the test will return true for all records (as @HouseHold is null all the time) and all records are returned.

To assign the value to your paramaters collection try something like this:

Code:

if (strAreaFilter.Length > 0)
{
  objCommand.Parameters["HouseHold"].Value = strAreaFilter;
}
else
{
  objCommand.Parameters["HouseHold"].Value = DBNull.Value;
}

HtH,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.