Export to csv has Object reference not set to an instance of an object

Question

I'm trying to export from SQL Server a csv file from a query.

I'm getting the error Object reference not set to an instance of an object on the line

foreach (DataColumn dc in dt.Columns)
{
}

Here is the code i am running for this.

private DataTable GetData()
{
    SqlConnection sqlCon = new SqlConnection("...");
    SqlCommand sqlCmd = new SqlCommand();
    sqlCmd.CommandText = "SELECT top 10 * from products.products";
    sqlCmd.Connection = sqlCon;

    sqlCon.Open();
    sqlCmd.ExecuteScalar();
    sqlCon.Close();
    return dt;
}

protected void Button3_Click(object sender, EventArgs e)
{
    DataTable dt = GetData();

    string attachment = "attachment; filename=DataTable.xls";

    Response.ClearContent();
    Response.AddHeader("content-disposition", attachment);
    Response.ContentType = "application/vnd.ms-excel";

    string tab = "";
    foreach (DataColumn dc in dt.Columns)
    {
        Response.Write(tab + dc.ColumnName);
        tab = "\t";
    }

    Response.Write("\n");

    foreach (DataRow dr in dt.Rows)
    {
        tab = "";

        for (int i = 0; i < dt.Columns.Count; i++)
        {
            Response.Write(tab + dr[i].ToString());
            tab = "\t";
        }

        Response.Write("\n");
    }

    Response.End();
}

I'm not sure what to do.

It compile without errors? **DataTable GetData()** does not have dt declaration. ExecuteScalar return a... scalar not table ;) — EKOlog, Jun 27 '14 at 21:10
possible duplicate of [What is a NullReferenceException and how do I fix it?](http://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) — eddie_cat, Jun 27 '14 at 21:21

score 3 · Answer 1 · answered Jun 27 '14 at 21:10

3

You have to Fill your DataTable with something:

private DataTable GetData()
{
    DataTable dt = new DataTable();

    SqlConnection sqlCon = new SqlConnection("...");
    SqlCommand sqlCmd = new SqlCommand();
    sqlCmd.CommandText = "SELECT top 10 * from products.products";
    sqlCmd.Connection = sqlCon;

    sqlCon.Open();
    dt.Load(sqlCmd.ExecuteReader());
    sqlCon.Close();

    return dt;
}

answered Jun 27 '14 at 21:10

Fals

6,813
4
23
43

I get an error on dt.Load(sqlCmd.ExecuteReader()); - Object reference not set to an instance of an object. – user3784679 Jun 27 '14 at 21:13
DataTable dt = new DataTable() I did miss it in the method :). See above! – Fals Jun 27 '14 at 21:14
Thank you so much! :) I will select as answer in 2 minutes when it lets me. – user3784679 Jun 27 '14 at 21:16

score 0 · Answer 2 · answered Jun 27 '14 at 21:41

Try this:

private DataTable GetData()
{
DataTable dt;
        string connectionString = ConfigurationManager.ConnectionStrings["yourConnection"].ConnectionString;
        //
        // In a using statement, acquire the SqlConnection as a resource.
        //
        using (SqlConnection con = new SqlConnection(connectionString))
        {
            //
            // Open the SqlConnection.
            //
            con.Open();
            //
            // The following code uses an SqlCommand based on the SqlConnection.
            //
            using (SqlCommand command = new SqlCommand("SELECT top 10 * from products.products", con))
            {
                //use SqlDataAdapter to fill the dataTable
                using (SqlDataAdapter a = new SqlDataAdapter(command))
                {
                    dt = new DataTable();
                    a.Fill(dt);
                }

            }
        } 
        return dt;
    }

Export to csv has Object reference not set to an instance of an object

2 Answers2