CommandText property not initialised

Question

I seem to be getting this problem only after I've changed my working code to use a dataview as opposed to textboxes to display a single row of data.

I have the following:

static SqlConnection dbConnection = new SqlConnection
                       (DBConnection.DBConnection.connectionString);        
    SqlDataAdapter holdone = new SqlDataAdapter(getCommand, dbConnection);      

    DataSet holdall = new DataSet();
    DataSet updateall = new DataSet();
    DataTable invoiceTable = new DataTable();

    DataView invoiceView = new DataView();

which is used by

public void GetOne(/* connectionString, string invref, string tableref*/)
    {

getCommand = "select *redacted* from " + tableref +  
                                      "where *redacted* = " + invref;

        using (SqlConnection dbConnection = new SqlConnection
                               (DBConnection.DBConnection.connectionString))
        {
                dbConnection.Open();

            holdone.Fill(holdall);

            invoiceTable = holdall.Tables[0];

            dbConnection.Close();


        }

        DataRowView rowView = invoiceView.AddNew();

            rowView["*redacted*"] = invoiceTable;

            rowView.EndEdit();
    }

The error reports holdone.fill(holdall) as the offending line, however I am unsure as to why, as I'm not using SQLCommand as a parameter, rather a parameter of SQLDataAdapter.

I'm struggling to see where I've gone wrong?

Answer 1

The problem is that you do set the SqlDataAdapter's select command string to the current value of getCommand in this line:

SqlDataAdapter holdone = new SqlDataAdapter(getCommand, dbConnection); 

However, as strings are not really pointers, changing getCommand afterwords will not change the select command of the SqlDataAdapter.

What you'd need to do is:

public void GetOne(/* connectionString, string invref, string tableref*/)
{

    getCommand = "select *redacted* from " + tableref + "where *redacted* = " + invref;
    using (SqlConnection dbConnection = new SqlConnection(DBConnection.DBConnection.connectionString))
    {
        dbConnection.Open();

        holdone.SelectCommand = new SqlCommand(getCommand, dbConnection);
        holdone.Fill(holdall);

        invoiceTable = holdall.Tables[0];
        //dbConnection.Close(); // This line is unnecessary, as the connection will be closed by `using`
    }

    DataRowView rowView = invoiceView.AddNew();
    rowView["*redacted*"] = invoiceTable;
    rowView.EndEdit();
}