Export into excel with multiple list boxes filter !!

Question

Hi There,

Hope you are doing good !!

i am working on one request where i have multiple of listboxes over the form where we can select multiple of values out of each list box. once select the values then click on button and save the output.

when i am selecting only single value in single list box then the form is working but when i am selecting mutliple of listboxes with multi values not working.

i am doubting in below code. could you pls suggest me what to write for taking multiple filters along with multiple listboxes. i.e. i am expecting "in" condition. i attahced all the docs.

--

string dbQuery = "select * from TBL_ERP_DAILY_INV_BATCH_REPORT Where ";

if (ListValue1 != "" )

{

dbQuery = dbQuery + "type = '"+ ListValue1 +"'";

}

if (ListValue2 != "" )

{

dbQuery = dbQuery + "[SUPPLIER NUM] = '" + ListValue2 + "'";

---

Ankit Sharma · Answer

Hi Tiger, Use OR clause like below string dbquery = "select * from TBL_ERP_DAILY_INV_BATCH_REPORT where type = '" + ListValue1 + "' or [SUPPLIER NUM] = '" + ListValue2 + "'" ; Hope this helps

Ankit Sharma · Answer

Refer for implementation http://codedisplay.com/how-to-get-all-selected-items-of-a-listbox-in-asp-net-c-vb-net/

Tiger Singh · Answer

Hi Ankit, here is my code . pls suggest where to place the GetSelectedIndices . currently is not capturing multiple values. protected void Button1_Click(object sender, EventArgs e) { string connectionString ="Data Source=csfsb-sql02;Initial Catalog=ACCENTURE_PROD;Integrated Security=True;"; SqlConnection connection = new SqlConnection(connectionString); string ListValue1 = LL1.Text; string ListValue2 = LL2.Text; string ListValue3 = LL3.Text; string ListValue4 = LL4.Text; string ListValue5 = LL5.Text; string ListValue6 = LL6.Text; //// Using GetSelectedIndices Label6.Text = ""; foreach (int i in LL1.GetSelectedIndices()) { Label6.Text += "Selected Value: " + LL1.Items[i].Value + " Selected Text: " + LL1.Items[i].Text + " "; } //string dbQuery = "select * from TBL_ERP_DAILY_INV_BATCH_REPORT where type = '" + ListValue1 + "' OR [SUPPLIER NUM] = '" + ListValue2 + "'"; string dbQuery = "select * from TBL_ERP_DAILY_INV_BATCH_REPORT where type = '" + ListValue1 + "' OR [SUPPLIER NUM] = '" + ListValue2 + "' OR [PO NUMBER] = '" + ListValue3 + "' OR [OPERTAING_UNIT] = '" + ListValue4 + "' OR [SUPPLIER] = '" + ListValue5 + "' OR [INVOICE NUM] = '" + ListValue6 + "'"; // if (ListValue1 != "" ) //{ // dbQuery = dbQuery + "type = '"+ ListValue1 +"'"; //} //if (ListValue2 != "" ) //{ // dbQuery = dbQuery + "[SUPPLIER NUM] = '" + ListValue2 + "'"; //} try { SqlCommand sqlCmd = new SqlCommand(dbQuery, connection); connection.Open(); SqlDataAdapter sda = new SqlDataAdapter(sqlCmd); DataSet dataset = new DataSet(); sda.Fill(dataset); connection.Close(); Gridview1.DataSource = dataset; Gridview1.DataBind(); ExportToExcel(dataset.Tables[0], "DailyBatch" + DateTime.Now.ToShortDateString()); //Response.Write(LL1.SelectedValue.ToString()); //ExportExcel(dataset); } catch { throw; } }

Ankit Sharma · Answer

Read through all the selected elements using below link https://stackoverflow.com/questions/1586078/getting-all-selected-values-from-an-asp-listbox then form your SQL statement .

Tiger Singh · Answer

Hi Ankit, i am thinking something like as below:- "string dbQuery = "select * from TBL_ERP_DAILY_INV_BATCH_REPORT where type IN '" + ListValue1 + "' OR [SUPPLIER NUM] IN '" + ListValue2 + "'";" how to pass multiple values in single variable thats why we are using listbox. can we do like this if not then what should be the way to do that ? Thanks in advance !!

Ankit Sharma · Answer

what do you mean by "multiple value in listbox". as per my understandingeach list box will contain one string value only.similarly each column in table contains single value only. in this case below query shpould work.

Tiger Singh · Answer

Hi Ankit , will it work for multiple values in list item. can we put instead "=" where Type "in" string dbquery = "select * from TBL_ERP_DAILY_INV_BATCH_REPORT where type = '" + ListValue1 + "' or [SUPPLIER NUM] = '" + ListValue2 + "'" ;

Export into excel with multiple list boxes filter !!

Answers (7)