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?

13

3 Answers

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();
}

7

Looks like you're passing the getCommand to the SqlDataAdapter constructor before you've assigned it the actual SQL command. First innitialize the getCommand string, and only then construct the SqlDataAdapter object.

2

Your CommandText is passed to the SqlDataAdapter by Value, not by reference, so changing your variable getCommand after the adapter has been created will not affect the data adapter. A really simple demonstration is that this:

static void Main(string[] args)
{ string r = "Test"; using (var adapter = new SqlDataAdapter(r, new SqlConnection(""))) { r = "Test2"; Console.WriteLine(adapter.SelectCommand.CommandText); }
}

Outputs

Test

Not

Test2

You'd need to expicitly change your adapter's SelectCommand.CommandText:

holdone.SelectCommand.CommandText = getCommand;

For what it's worth reusing your objects like this is not helpful, it barely even saves code. .NET uses connection pooling, so using multiple SQL Connections/Adapters does not necessarily mean multiple pipes to the database. You should also use parameterised queries:

string sql = "select *redacted* from " + tableref + " where *redacted* = @InvoiceParam";
using (var adapter = new SqlDataAdapter(sql, DBConnection.DBConnection.connectionString))
{ adapter.SelectCommand.Parameters.AddWithValue("@InvoiceParam", invRef); adapter.Fill(holdall); invoiceTable = holdall.Tables[0];
}

1

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.