Updating database with dataTable (or dataSet)

Wed, 17 Jun 2009 08:18:04 Z

Hello, I need some help on the updating databse. I could use sqlCommandBuilder but because my query includes 2 tables I have to create my own code for update data back to database.

The code below gets the values, depending on the sql query into dataTabe (or dataSet - can I ask here what is the difference between these two, what to use?) and then the vaules get changed through a textBox where user addes some values and new values are again the the dataTable (and dataSet too).

So from here on my goal is to put these new values back into the same place in the database, were they were before. I`ll need some help on this stage.

                DataSet dataSet = new DataSet();
                SqlDataAdapter dataAdapter = new SqlDataAdapter("SELECT IDComponent, CurrentCycle FROM Components, Aircrafts WHERE Aircrafts.IDAircraft = '" + IDAvijona + "' " +
                                                                "AND Aircrafts.IDAircraft = Components.IDAircraftFK", povezava);
                dataAdapter.Fill(dataSet, "Components");
                DataTable dataTable = dataSet.Tables["Components"];
                //dataSet.Tables[0].TableName = "Cycles";
                foreach (DataRow dataRow in dataSet.Tables["Components"].Rows) 
                {
                    if (dataRow["CurrentCycle"].Equals(System.DBNull.Value))
                    {
                        //Če je v bazi vrednost NULL gre koda tukaj skozi - ne zgodi se nič!
                        dataRow["CurrentCycle"] = DBNull.Value;
                    }
                    else
                    {
                        dataRow["CurrentCycle"] = Convert.ToInt32(dataRow["CurrentCycle"]) + Convert.ToInt32(textBoxCycles.Text);
                    }
                }
                
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    if (dataTable.Columns["CurrentCycle"] == null)
                    {

                    }
                    else
                    {
                        DataRow dataRow2 = dataTable.Rows[i];
                        SqlCommand cmd2 = new SqlCommand("UPDATE Components SET CurrentCycle = @currentCycle WHERE IDComponent = @idComponent", povezava);
                        cmd2.Parameters.Add(new System.Data.SqlClient.SqlParameter("@currentCycle", dataRow2["CurrentCycle"]));
                        SqlParameter param2 = cmd2.Parameters.Add("@idComponent", SqlDbType.Int);
                        cmd2.Parameters["@idComponent"].Value = (int)dataRow2["CurrentCycle"];
                        param2.SourceVersion = DataRowVersion.Current;
                        dataAdapter.UpdateCommand = cmd2;
                        dataAdapter.Update(dataSet, "Components");
                    }
                }

This is my code I did, but a code for updating into the database is not correct, I get an error: "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records".

Can I ask someone for a bit of a help, please. I am trying to work this out for 3 days, but so far still didn`t find the way through.

PS: I forgot to say that "CurrentCycle" is the changing value. In the UPDATE query I pulled out the IDComponent too (this is the component`s ID), which goes together with the CurrentCycle. So I though I need the ID to put the particular value back to the database, otherwise how can the program know where to put it? I am right?

Sometimes I can get out the te database hundreds for CurrentCycle values, and they are not in a row ( I mean one has Id 23, another 24, 3rd 30, 4th 34, and so on...)

I am a rookie at C#, so please don`t be mad if my questions goona sound stupid...

Updating database with dataTable (or dataSet)

Wed, 17 Jun 2009 09:59:52 Z

I got it:

                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    if (dataTable.Columns["CurrentCycle"] == null) //HERE IS THE PROBLEM NOW!
                    {
                        continue;
                    }
                    else
                    {
                        DataRow dataRow2 = dataTable.Rows[i];
                        SqlCommand cmd2 = new SqlCommand("UPDATE Components SET CurrentCycle = @currentCycle WHERE IDComponent = @idComponent", povezava);
                        cmd2.Parameters.Add(new System.Data.SqlClient.SqlParameter("@currentCycle", SqlDbType.Int)); //, (int)dataRow2["CurrentCycle"]));
                        cmd2.Parameters["@currentCycle"].Value = (int)dataRow2["CurrentCycle"];
                        SqlParameter param2 = cmd2.Parameters.Add("@idComponent", SqlDbType.Int);
                        cmd2.Parameters["@idComponent"].Value = (int)dataRow2["IDComponent"];
                        //param2.SourceVersion = DataRowVersion.Current;
                        dataAdapter.UpdateCommand = cmd2;
                        dataAdapter.Update(dataSet, "Components");
                    }
                }

I have only one problem. I need to specify if the row of a CurrentCycle is empty. If it is empty (there is no value) it must not go through the code else.

OR maybe a better solution:

How to get into dataTable a NULL value if there is a null value in a database?

So if there is a null value in a databse, dataTable will get a null value too, and with updating the null value will go again into database?

This is the code I have while dataRow read database:

                foreach (DataRow dataRow in dataSet.Tables["Components"].Rows) 
                {
                    if (dataRow["CurrentCycle"].Equals(System.DBNull.Value))
                    {
                        //The code to set the value in a dataTable to NULL!!                        
                    }

I am a rookie at C#, so please don`t be mad if my questions goona sound stupid...

Updating database with dataTable (or dataSet)

Wed, 17 Jun 2009 12:35:45 Z

This forum is for SQL Data Services, a Microsoft's Cloud database service offering. You will have a better chance of getting a fast answer in one of the
.net(C#) specific forum. please go to forum home and raise it in ADO.Net forum http://social.msdn.microsoft.com/Forums/en-US/categories

Thanks
Kapil