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();
tnookAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tnookAuthor Commented:
Hi der_jth,

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

Much appreciated,
Kevin
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.