• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 716
  • Last Modified:

UpdateCommand DataSet problem

Hi Guys,

Got a little problem, no doubt a no brainer to most of you. I have a routine where I get some data from a database and fill a dataSet. I want to update some fields in the dataSet before using it. The problem is that I'm not sure how to define the updateCommand and currently get the following error.

Update requires a valid UpdateCommand when passed DataRow collection with modified rows.

Any suggestions most welcomed.

Regards,
Kevin


string selectStatement;
int      numRows;
int      i;
            
selectStatement = "select top 5 date_updated, car_repaired, car_type, car_age where car_owner = @owner_id";
            
sqlDataApAngelina = new System.Data.SqlClient.SqlDataAdapter(selectStatement, sqlConAngelina);

sqlDataApAngelina.SelectCommand.Parameters.Add("@car_owner", SqlDbType.Int);
sqlDataApAngelina.SelectCommand.Parameters["@car_owner"].Value = carOwner;

sqlConAngelina.Open();
sqlDataApAngelina.Fill(dataSetCarService, "SERVICING");
sqlConAngelina.Close();

numRows = dataSetModeration.Tables["SERVICING"].Rows.Count;

for(i=0;i<numRows;i++ )
{
   dataSetModeration.Tables["SERVICING"].Rows[i]["date_updated"] = DateTime.Now.ToString();
   dataSetModeration.Tables["SERVICING"].Rows[i]["car_repaired"] = true;
}

sqlConAngelina.Open();
sqlDataApAngelina.Update(dataSetCarService, "SERVICING");
sqlConAngelina.Close();

sqlDataApAngelina.Dispose();
0
tnook
Asked:
tnook
  • 4
  • 3
1 Solution
 
der_jthCommented:
Try doing this before the update:

new SqlCommandBuilder(sqlDataApAngelina);

SqlCommandBuilder constructs the Update/InsertCommands for you. The syntax for SCB is a bit surprising; you don't have to do anything else but construct it. It attaches itself to listen to data adapter's events and then creates the proper SQL commands on the fly. Read the help for SqlCommandBuilder to see the restrictions on its use. It won't work with more bizarre column/table/join combinations.
0
 
tnookAuthor Commented:
Hi,

Thanks for the tip. Alas I have multiple tables and am looking for how to manually add an UpdateCommand.

Kevin
0
 
der_jthCommented:
Create a DB command object with a parameterized query. Add the parameters, and set their SourceColumn and SourceVersion properties to whatever you want (for example, set SourceColumn to "car_id" and SourceVersion to DataRowVersion.Original to pull the car_id from the unmodified row; equivalently f.e. "car_repaired" and DataRowVersion.Current will return the modified repair date.

Since you already seem to be familiar with DB params, I guess you can do it from here - the param objects have a constructor for setting SourceColumn specifically. See the help topic "Using Parameters with a DataAdapter" for further help, and if you still have a problem, ask for more. Hope this helps :-)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
tnookAuthor Commented:
Hi,

I tried to modify the code as follows but get a "Object reference not set to an instance of an object." error at the line marked below. Is the approach sound or am I barking up the wrong tree. Since I'm being a thick pest I've upped the points for the question as compensation.

Help much appreciated.

Kevin

string selectStatement;
string updateStatement;
int      numRows;
int      i;
         
selectStatement = "select top 5 date_updated, car_repaired, car_type, car_age where car_owner = @owner_id";
         
sqlDataAdapter1 = new System.Data.SqlClient.SqlDataAdapter(selectStatement, sqlConnection);

sqlDataAdapter1.SelectCommand.Parameters.Add("@car_owner", SqlDbType.Int);
sqlDataAdapter1.SelectCommand.Parameters["@car_owner"].Value = carOwner;

sqlConnection.Open();
sqlDataAdapter1.Fill(dataSetCarService, "SERVICING");
sqlConnection.Close();

sqlDataAdapter1.Dispose();

numRows = dataSet1.Tables["SERVICING"].Rows.Count;

for(i=0;i<numRows;i++ )
{
   dataSet1.Tables["SERVICING"].Rows[i]["date_updated"] = DateTime.Now.ToString();
   dataSet1.Tables["SERVICING"].Rows[i]["car_repaired"] = true;
   dataSet1.Tables["SERVICING"].Rows[i]["serviced_by"] = mechanic_id;
}

updateStatement = "update MEMBER set date_updated = @dateRepaired, car_repaired = @carRepaired, serviced_by = @mechanic_id";

sqlDataAdapter1 = new System.Data.SqlClient.SqlDataAdapter(updateStatement, sqlConnection);

sqlDataAdapter1.UpdateCommand.Parameters.Add("@dateRepaired", SqlDbType.DateTime);                                             <<<----------------- Error occurs here
sqlDataAdapter1.UpdateCommand.Parameters["@dateRepaired"].SourceColumn = "date_updated";
sqlDataAdapter1.UpdateCommand.Parameters["@dateRepaired"].SourceVersion = DataRowVersion.Current;

sqlDataAdapter1.UpdateCommand.Parameters.Add("@mechanic_id", SqlDbType.Int);
sqlDataAdapter1.UpdateCommand.Parameters["@mechanic_id"].SourceColumn = "serviced_by";
sqlDataAdapter1.UpdateCommand.Parameters["@mechanic_id"].SourceVersion = DataRowVersion.Current;

sqlDataAdapter1.UpdateCommand.Parameters.Add("@carRepaired", SqlDbType.Bit);
sqlDataAdapter1.UpdateCommand.Parameters["@carRepaired"].SourceColumn = "car_repaired";
sqlDataAdapter1.UpdateCommand.Parameters["@carRepaired"].SourceVersion = DataRowVersion.Current;

sqlConnection.Open();
sqlDataAdapter1.Update(dataSet1, "SERVICING");
sqlConnection.Close();

sqlDataAdapter1.Dispose();
0
 
tnookAuthor Commented:
Sorry the line above:

sqlDataAdapter1.Fill(dataSetCarService, "SERVICING");

should read:

sqlDataAdapter1.Fill(dataSet1, "SERVICING");

It was a typo.

However the problem still persists.

Kevin
0
 
der_jthCommented:
Create the UpdateCommand first (sqlDataAdapter1.UpdateCommand = new SqlCommand(updateStatement, sqlConnection). Don't pass the updateStatement to the SqlDataAdapter's constructor; the constructor takes the _select_ statement as its param, not the update one.

With the nullreferenceexception ("Object reference not set...") it's always useful to start examining the statement causing it from left to right. If you're in VS.net debugger, just point the row with the mouse cursor once the execution has stopped because of the error, and you'll see tooltips reflecting the value of each of the objects. For this instance, you'd probably get "{System.Data.SqlClient.SqlDataAdapter}" for sqlDataAdapter1, and "<null>" for UpdateCommand - this indicates the Updatecommand reference needs to be set to a valid object (SqlCommand) before you can set its properties - Parameters in this case.

If you're not in VS.net, you can use for example message boxes:

MessageBox.Show("sqlDataAdapter1 is set: " + (sqlDataAdapter1 != null));
MessageBox.Show("sqlDataAdapter1.UpdateCommand is set: " + (sqlDataAdapter1.UpdateCommand != null));

(in this case, you'd probably get "sqlDataAdapter1 is set: true" and "sqlDataAdapter.UpdateCommand is set: false")
0
 
tnookAuthor Commented:
Hi der_jth,

Thanks for your help, it works fine now!!!!

Much appreciated,
Kevin
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now