[Solved] ASP.Net DataSet Fill Error: Parameter index is out of range

comunidadmexicanaroma
 
on Oct 01, 2021 04:27 AM
317 Views

Hi all,

I need your help in this problem I got with DataTable and DataSet for export my table on Excel XLSX format.

Here's the error: Parameter index is out of range.

On this line: da.Fill(ds);

If try the sql query on MySQL all working correctly.

Can you help me?

My code below

protected void btnExcelxlsx_Click(object sender, ImageClickEventArgs e)
{
        DataSet ds = new DataSet();
        DataTable dt = new DataTable();
        MySqlDataAdapter da;
 
        sql = @String.Format(" SELECT * FROM doTable; ");
 
        using (MySqlConnection con =
           new MySqlConnection(ConfigurationManager.ConnectionStrings["cn"].ConnectionString))
        {
            using (MySqlCommand cmd =
                new MySqlCommand(sql, con))
            {
                da = new MySqlDataAdapter(cmd);
                da.Fill(ds);
                 
                con.Open();
                cmd.CommandTimeout = 2147483;
 
                cmd.ExecuteNonQuery();
                con.Close();
                dt = ds.Tables[0];
                ExportDataSetToExcel(ds);
            }
        }
}
 
public static void ExportDataSetToExcel(DataSet ds)
{
    string AppLocation = @"C:\Inetpub\wwwroot\ExcelFiles\";
    string guid = Guid.NewGuid().ToString().ToUpper().Replace("-", "_");
 
    HttpContext.Current.Response.Cookies["guid"].Value = guid.ToString();
    HttpContext.Current.Response.Cookies["guid"].Expires = DateTime.Now.AddMinutes(3);
 
    string filepath = AppLocation + "_" + HttpContext.Current.Response.Cookies["guid"].Value + ".xlsx";
 
    using (XLWorkbook wb = new XLWorkbook())
    {
        for (int i = 0; i < ds.Tables.Count; i++)
        {
            wb.Worksheets.Add(ds.Tables[i], ds.Tables[i].TableName);
        }
        wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        wb.Style.Font.Bold = true;
        wb.SaveAs(filepath);
        wb.Dispose();
    }
 
    Thread.Sleep(3000);
    HttpContext.Current.Response.Clear();
    HttpContext.Current.Response.Buffer = true;
    HttpContext.Current.Response.ContentType = "application/force-download";
    HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment; filename=" +
    HttpContext.Current.Response.Cookies["guid"].Value + ".xlsx");
 
    HttpContext.Current.Response.TransmitFile(@"C:\Inetpub\wwwroot\ExcelFiles\" +
    HttpContext.Current.Response.Cookies["guid"].Value + ".xlsx");
 
    HttpCookie cookie = new HttpCookie("ExcelDownloadFlag")
    {
        Value = "Flag",
        Expires = DateTime.Now.AddDays(1)
    };
 
    HttpContext.Current.Response.AppendCookie(cookie);
    HttpContext.Current.Response.End();
}

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Oct 01, 2021 04:29 AM

Hi comunidadmexi...,

Refer modified code.

protected void btnExcelxlsx_Click(object sender, ImageClickEventArgs e)
{
    DataSet ds = new DataSet();
    using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["cn"].ConnectionString))
    {
        using (MySqlCommand cmd = new MySqlCommand("SELECT * FROM doTable;", con))
        {
            cmd.CommandTimeout = 2147483;
            MySqlDataAdapter da = new MySqlDataAdapter(cmd);
            da.Fill(ds);
            ExportDataSetToExcel(ds);
        }
    }
}