Web Form with List Boxes and Export Button !!

Question

Dear All Happy to join here  hoping to excel my knowledge in various way by asking you superior tech people. below is the synopsis of my project and details input. I have small request where on have one form which has mainly listbox and export button which gonna save the record in local drive. 1. If N user lead to this form then wish to select multiple item from list box and once they click on button has to query the data for expected records and save into text or excel format. 2. If N user lead to this form and wish to not select any item from list boxes and may able to expport the entire data by clicking on export button. so basically the button event should work fo both (1 & 2 ) input. i have built the list boxes and populated data (binding) into them but now task is how to bind the list boxes data to export button and save the record to drive. Please help me to complete the task as i am beginner in technolgy.

Ankit Sharma · Answer

Hi tiger you need to pass Listvalue as string so you need to enclose it with quotes. Update dbQuery string as below string dbQuery = select * from TBL_ERP_DAILY_INV_BATCH_REPORT Where type = ' + ListValue + ' ; If your issue is resolved then accept the answer and close this question else reply back with your problem. thanks

Ankit Sharma · Answer

Hi Tiger Try below code string dbQuery = select * from TBL_ERP_DAILY_INV_BATCH_REPORT Where type = ' + ListValue1 + ' and supply no = ' + ListValue2 + ' and supplier name in (' + ListValue3 + '' + ListValue4 + ') ; And a word of advice.Do a basic google search before throwing your questrion in QnA forums.

Tiger Singh · Answer

Hi Ankit suppose i have multiple of columns and filetrs in the query as i have couple of listboxes then how to write the code. pls suggest. i.e. I have 6 columns as filter in listboxes. type supply no PO number supplier name and etc.. below query is showing only for single column Type and i want it should accept all columns of values not just = even has to be In. Please help me here. string dbQuery = select * from TBL_ERP_DAILY_INV_BATCH_REPORT Where type = ' + ListValue + ' ;

Ankit Sharma · Answer

Hi Tiger Visit this link to see how to install packages using Nuget package manager http://www.c-sharpcorner.com/UploadFile/8a67c0/use-nuget-package-manager-in-visual-studio-2015/ Use nuget command : Install-Package ClosedXML -Version 0.88.0 and include using ClosedXML.Excel; in your .cs file

Tiger Singh · Answer

Hi Ankit Hope you are doing good !! i had tried toi run my code but noithing is happening when click on button. can u pls tell me more about your prior statements  ou need to install Closed XML before running this code. Use nuget command : Install-Package ClosedXML -Version 0.88.0 and include using ClosedXML.Excel; in your .cs file . i am attching the enire code and screen shot. pls help me out here. even i can see red error on some lines.

Ankit Sharma · Answer

Hi Tiger Glad that i can be of your help. Feel free to post your questions in this forum. Cheers!!!

Tiger Singh · Answer

Hi Ankit Thanks so much for the assiatnce and help so far. really arrpiciate your knowledge sharing and patience over the request. i am working on the export functinality hence will get back to you incase face any issue.

Tiger Singh · Answer

Hi Ankit getting error as attached. might be some syntax error. pls advice.

Ankit Sharma · Answer

Hi Tiger The code looks fine to me. Just execute it and check if you are getting the desired output or not. I cant connect you over webex. you can ask your questions in forum. thanks

Tiger Singh · Answer

Hi Ankit Thanks for the assiatnace. so now my code looks like as attached .pls review. is it looks fine ? if possible can we meet over webex ?

Ankit Sharma · Answer

Hi Tiger You need to have an entry in your web.config file for your connection string like below then refer mycon in your .cs file like below string connectionString = ConfigurationManager.ConnectionStrings[ mycon ].ConnectionString.ToString(); this will resolve your error. OR if you dont want to specify your coneection string in web.config then use below code to directly include conection string in .cs file string connectionString = Data Source=ANKITS-401\SQLEXPRESS;Initial Catalog=myTestDB;Integrated Security=True; ; For you second problem use below code string ListValue = listboxid.Text; string dbQuery = select * from table Where tbl_column_name=  +ListValue; Hope this helps

Tiger Singh · Answer

Hi Ankit  any idea why i am getting this error? and how to add my list box values tothe query ?

Ankit Sharma · Answer

Hi Tiger Please try below code and let me know string connectionString = ConfigurationManager.ConnectionStrings[ mycon ].ConnectionString.ToString(); SqlConnection connection = new SqlConnection(connectionString); string dbQuery = SELECT TYPE FROM TBL_ERP_DAILY_INV_BATCH_REPORT ; try { SqlCommand sqlCmd = new SqlCommand(dbQuery connection); connection.Open(); SqlDataAdapter sda = new SqlDataAdapter(sqlCmd); DataSet dataset = new DataSet(); sda.Fill(dataset); connection.Close(); ExportExcel(dataset); } catch { throw ; }

Tiger Singh · Answer

Hi Ankit i am not using any SP in my process. it's just select query with single table. hence request you to please amend the code based on my request. Appriciate your support here as being new learner.

Tiger Singh · Answer

Hi Ankit I have the code as per you suggested below. pls help me out with colour marked. protected void Button1_Click(object sender EventArgs e) { try { string constring = ConfigurationManager.ConnectionStrings[Data Source=XXX;Initial Catalog=XXX;Integrated Security=True].ConnectionString; SqlConnection con = new SqlConnection(constring); SqlCommand cmd = new SqlCommand(SELECT TYPE FROM TBL_ERP_DAILY_INV_BATCH_REPORT con); SqlDataAdapter da = new SqlDataAdapter(); DataSet ds = new DataSet(); cmd.Parameters.Add(new SqlParameter(@InputParam1 LL1 -- here i am passing the listbox name which is LL1 do i need to pass any other value? )); //cmd.Parameters.Add(new SqlParameter(@InputParam2 InputParameterForStoredProcedure)); cmd.CommandType = CommandType. ?? what to pass here ; da.SelectCommand = cmd; da.Fill(ds); ExportExcel(ds); } catch { throw; } }

Ankit Sharma · Answer

Hi Tiger Use below code for your reference protected void Button_Click( object sender EventArgs e) { try { string constring = ConfigurationManager.ConnectionStrings[ connectionString ].ConnectionString; SqlConnection con = new SqlConnection(constring); SqlCommand cmd = new SqlCommand( StoredProcedurename  con); SqlDataAdapter da = new SqlDataAdapter(); DataSet ds = new DataSet(); cmd.Parameters.Add( new SqlParameter( @InputParam1  InputParameterForStoredProcedure)); cmd.Parameters.Add( new SqlParameter( @InputParam2  InputParameterForStoredProcedure)); cmd.CommandType = CommandType.StoredProcedure; da.SelectCommand = cmd; da.Fill(ds); ExportExcel(ds); } catch { throw ; } } Change the values as per your requirement Hope this helps

Tiger Singh · Answer

Hi Ankit Thanks for the sugestion and script. can you suggest the script for how to  On button click you can call the database and store the output in Dataset.  ?

Ankit Sharma · Answer

Hi Tiger On button click you can call the database and store the output in Dataset. Then pass the dataset to following function to download report as excel file protected void ExportExcel( DataSet dataSet) { string filename = Report ; XLWorkbook workBook = new XLWorkbook(); try { workBook.Worksheets.Add(dataSet.Tables[0] Exported_Data ); if (workBook.Worksheets.Count > 0) { Response.Clear(); Response.Buffer = true ; Response.Charset = string .Empty; Response.ContentType = application/vnd.openxmlformats-officedocument.spreadsheetml.sheet ; Response.AddHeader( content-disposition  attachment;filename=  + HttpUtility.UrlEncode(filename)); using (MemoryStream MyMemoryStream = new MemoryStream()) { workBook.SaveAs(MyMemoryStream); MyMemoryStream.WriteTo(Response.OutputStream); HttpContext.Current.Response.Flush(); HttpContext.Current.Response.SuppressContent = true ; HttpContext.Current.ApplicationInstance.CompleteRequest(); HttpContext.Current.Response.Close(); } } } catch (Exception ex) { return ; } } You need to install Closed XML before running this code. Use nuget command : Install-Package ClosedXML -Version 0.88.0 and include using ClosedXML.Excel; in your .cs file Hope this helps