Web Form with List Boxes and Export Button !!

Tiger Singh

9y1.1k.NET

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.

18 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ankit Sharma9y ago
Accepted 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 Sharma9y ago
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 Singh9y ago
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 Sharma9y ago
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 Singh9y ago
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 Sharma9y ago
Hi Tiger,

Glad that i can be of your help.

Feel free to post your questions in this forum.

Cheers!!!
Tiger Singh9y ago
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 Singh9y ago
Hi Ankit,

getting error as attached. might be some syntax error. pls advice.
Ankit Sharma9y ago
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 Singh9y ago
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 Sharma9y ago
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 Singh9y ago
Hi Ankit ,

any idea why i am getting this error? and how to add my list box values tothe query ?
Ankit Sharma9y ago
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 Singh9y ago
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 Singh9y ago
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 Sharma9y ago
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 Singh9y ago
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 Sharma9y ago
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