Apply Filter 1

[dorandoran]

I have a applyfilter that works with another page(using this sample

http://www.c-sharpcorner.com/Blogs/BlogDetail.aspx?BlogId=825).

can similar be implemented with following datasource (following datasource is for child gridview). I have a label where user will input a value for filter. i like to take that value and implement it with the code i posted so that the childgrid now have filtered data. hope it makes sense. (see example here:

http://www.codeproject.com/KB/aspnet/EditNestedGridView.aspx

) please suggest.


private DataTable ChildDataSource(string strlpID, string strSort)
{
SqlConnection conn;
SqlDataAdapter daProducts;
DataTable dsProducts;
string strQRY = "";
conn = new SqlConnection(@"Server=riverstone; Integrated Security=SSPI; Database=nw");
strQRY = "SELECT [Orders].[CustomerID],[Orders].[OrderID]," +
"[Orders].[ShipAddress],[Orders].[Freight],[Orders].[ShipName] FROM [Orders]" +
" WHERE [Orders].[CustomerID] = '" + strlpID + "'" +
"UNION ALL " +
"SELECT '" + strlpID + "','','','','' FROM [Orders] WHERE [Orders].[CustomerID] = '" + strlpID + "'" +
"HAVING COUNT(*)=0 " + strSort;
daProducts = new SqlDataAdapter(strQRY, conn);
dsProducts = new DataTable();
daProducts.Fill(dsProducts);
return dsProducts;
}

 

[jmeckley]

If i understand the "applyFilter" you mean you want to append additional predicates to the sql statement before executing. yes this is possible. You build the query statement. In it's crudest form it would look like this

var sql = "select * from table where @value is null or column = @value";

command.CommandText = sql;
command.Parameters.Add(new Parameter("value", theOptionalParameter);

your code above works, but is error prone.
1. you using injected sql instead of parameterized queries.
2. you are not properly disposing of your ado.net objects.
3. you have the sql string hard coded.

all of these are easily remedied.
1. use pramaters, not injected sql. there are all kinds of posts about this online.
2. use the "using" keyword, or try/finally blocks to dispose of your connection.
3. store the connection string in the web.config and reference the connection string from ConfigurationManager.ConnectionStrings["key"].ConnectionString.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190

 

[jmeckley]

In it's crudest form it would look like this

disregard this comment. I started down a different path when I was responding and changed the code without reviewing the entire post. the sql above isn't "crude".

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190

 

[dorandoran]

Jason, I will go ahead and give this a try. I know inline sql can be messy and vunerable but i will fix that later. I am trying to get the concept worked out. Hope you understand. oh, thanks for the help...

 

[dorandoran]

Jason,

Initially I dont have anything selected from my dropdownlist (in my case "",30,60,90) which I am using for parameter. How do I setup optional parameter cause it's breaking when I dont select a value from the dropdownlist.

Thanks again.

 

[jmeckley]

How do I setup optional parameter cause it's breaking when I dont select a value from the dropdownlist.

define breaking down.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190

 

[dorandoran]

this is the error "Input string was not in a correct format." i m getting. and da.products.fill(dtproducts); is highlighted.

 

[jmeckley]

If i had to guess I would say it's an issue with the sql produced. switch to parametrized queries to reduce the injection of values, this will help eliminate the formatting error.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190

 

[dorandoran]

hmm. Jason, I thought I am using parameterized query. can you please see blow and correct me? thanks and appreciate your time and help.

private DataTable ChildDataSource(string strlpID, string strSort)
{
SqlConnection conn;
SqlDataAdapter daProducts;
DataTable dtProducts;
string strQRY = "";
string streql = "=";
conn = new SqlConnection(@"Server=STONE; Integrated Security=SSPI; Database=NORTHWEST");
strQRY = @"SELECT Part, Quantity,
FROM INVENTORY
WHERE location= '" + strlpID + "' AND (Quantity >= @qty)"
+ strSort;
SqlCommand cmd = new SqlCommand(strQRY,conn);
SqlParameter param = new SqlParameter();
param.ParameterName="@qty";
param.SqlDbType=SqlDbType.Int;
param.Direction=ParameterDirection.Input;
if (ddlQty2.SelectedValue != null)
{
param.Value = ddlQty2.SelectedValue.TrimStart();
}
else
{
param.Value = 1;
}

cmd.Parameters.Add(param);
daProducts = new SqlDataAdapter(cmd);
dtProducts = new DataTable();
daProducts.Fill(dtProducts);

return dtProducts;

}

 

[jmeckley]

along with the code below setup your dropdownlist to require a selected value and set the blank option with a value of one. this removes the need for an if/then check.

note changes to the sql statment:
1. no injected sql.
2. since your sort is dynamic i moved that to code instead of the db.
3. i fixed a syntax issue in the sql statement. invalid select clause.

var quantity = int.Parse(ddlQty2.SelectedValue);
var locationId = GetLocationIdFromSomeWhere();

var results = new DataTable();

using(var connection = new SqlConnect(ConfigurationManager.ConnectionStrings["[key]"].ConnectionString))
using(using(var command = connection .CreateCommand())
{
   command.CommandText = @"SELECT Part, Quantity FROM INVENTORY WHERE location= @locationId AND Quantity >= @qty ";

   var quantityParameter = command.CreateParameter();
   quantityParameter.Value = quantity;
   quantityParameter.Name = "qty";
   command.Parameters.Add(quantityParameter);

   var locationParameter = command.CreateParameter();
   locationParameter.Value = locationId;
   locationParameter.Name = "locationId";
   command.Parameters.Add(locationParameter);

   connection.Open();
   results.Load(command.ExecuteReader());

}
return new DataView(results, [sort expression goes here]).ToDataTable();

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190

 

[dorandoran]

wow. datz completely different. let me try. again THANKS A LOT.

 

[dorandoran]

Jason, I followed your code and getting error on this line

int quantity = int.Parse(ddlQty2.SelectedValue)

error: input string was not in correct format (the drop down is blank unless user picks a value, which is optional)

 

[jmeckley]

right, change the input so the dropdown has 1 for the value of "blank" text and make it required. set option 1 as selected by default.

MyDropDown.Items[0].Selected = true;

or

MyDropDown.Items.Insert(0, new ListItem
   {
      Text = "", 
      Value = 1.ToString(), 
      Selected = true
   });

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190

 

[dorandoran]

hmm. remember the first item in the drop down is empty or null or "". This is optional therefore i cant force user to pick a value from it. However, i did put ddl.items[0].selected=true on page load.

but getting same error...

 

[jmeckley]

remember the first item in the drop down is empty or null or "". This is optional therefore i cant force user to pick a value from it.

I understand that. My suggestion is make the default value 1 not null/empty and make the selection of this required, not optional. this removes the if/then check and reduces the complexity of the code behind.

This concept is known as the Null Object Pattern. in this case we are not working with null directly, but the application of the pattern still applies.



Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190

 

[dorandoran]

okay, i added this in my default.aspx
<asp:ListItem Text="" Value="1" />

by default this is having index of 0. so far so good. NO ERROR. however, nothing happens when I pick a value from drop down for filter. (it worked before. before i was able to pick a value from drop down and filter, only problem was the empty string in drop down)

 

[jmeckley]

you'll need to provide more code. are you using autopostback on the DDL? did you set the default value? have you stepped through the code to see what the problem may be.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190

 

[dorandoran]

ddl has autopostback=true

i also put the ddl.clearselection() on pageload ispostback area and put ddl.selectedindex=-1

let me step into and find out...

 

[jmeckley]

ddl.selectedindex = -1
this is a problem. -1 means nothing is selected.
ddl.selectedindex = 0
instead

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190

 

[dorandoran]

ddl.selectedindex=0 in my if(!ispostback) but not doing anything. hmm