Hi, I have a problem to update a row in the SQL table. I will attach here the specific code of updating:
public void UpdateRowInDataBase(DataRow rowToUpdate)
{
OpenConnection();
string tableName = GetDatabaseEmployeesDataSet().TableEmployee.ToString();
SqlDataAdapter adapter = new SqlDataAdapter();
byte status = Convert.ToByte(rowToUpdate[0]);
string GivenName = rowToUpdate.ItemArray[1].ToString();
string FamilyName = rowToUpdate.ItemArray[2].ToString();
string ID = rowToUpdate.ItemArray[3].ToString();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "?????????????????";
//add our parameters to our command object
cmd.Parameters.Add("@Status", SqlDbType.Bit).Value = DBNull.Value;
cmd.Parameters.AddWithValue("@GivenName", GivenName);
cmd.Parameters.AddWithValue("@FamilyName", FamilyName);
cmd.Parameters.AddWithValue("@ID", ID);
cmd.Connection = conn;
adapter.UpdateCommand = cmd;
cmd.ExecuteNonQuery();
CloseConnection();
}
My problem is in the updating statement format if someone can tell me the exact update statement according to my function. Please help me write cmd.CommandText = " ???????????? " - what I have to write instead of the ?????????? Thank you - Edited byW.A.S Saturday, July 11, 2009 12:06 PM
- Edited byW.A.S Saturday, July 11, 2009 12:09 PM
- Edited byW.A.S Saturday, July 11, 2009 12:07 PM
-
| | W.A.S Saturday, July 11, 2009 12:04 PM | I assume that you had an added row, and did the insert, but then since you didn't AcceptChanges, the next time around it thought you needed to insert again. So, yeah, you should always AcceptChanges after you have updated the database ... so, what did you mean "how can I do that"?
Would you like a suggestion for an easier way to add those parameters and construct your DataAccess?
public void UpdateRowInDataBase(DataRow rowToUpdate)
{
OpenConnection();
SqlDataAdapter adapter = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand();
//add our parameters to our command object
cmd.Parameters.AddWithValue("@Status", rowToUpdate[0]);
cmd.Parameters.AddWithValue("@GivenName", rowToUpdate[1]);
cmd.Parameters.AddWithValue("@FamilyName", rowToUpdate[2]);
cmd.Parameters.AddWithValue("@ID", rowToUpdate[3]);
string UpdateCommand = "UPDATE Employees SET Status = @Status, GivenName = @GivenName, FamilyName = @FamilyName WHERE ID = @ID";
string InsertCommand = "INSERT Employees (Status, GivenName, FamilyName, ID) SELECT @Status, @GivenName, @FamilyName, @ID";
if (rowToUpdate.RowState == DataRowState.Added)
cmd.CommandText = InsertCommand;
else
cmd.CommandText = UpateCommand;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
CloseConnection();
}
~~Bonnie Berent [C# MVP] - Marked As Answer byW.A.S Sunday, July 12, 2009 11:13 AM
-
| | BonnieB Saturday, July 11, 2009 11:26 PM | Answers please!!! :-) | | W.A.S Saturday, July 11, 2009 2:36 PM | Check this: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.updatecommand.aspx Your command approxymately :
cmd.CommandText = "UPDATE Employees SET Status = @Status, GivenName = @GivenName, FamilyName = @FamilyName, ID = @ID";
| | RobertHun Saturday, July 11, 2009 5:59 PM | Thank you .. I got another problem now .
Violation of PRIMARY KEY constraint 'PK_TableEmployee'. Cannot insert duplicate key in object 'dbo.TableEmployee'.
The statement has been terminated.
Actually, as you can see - I am not changing the ID , I am just trying to modify the other records of the employee according to the ID. Why does this happen?/ Thank you Wael | | W.A.S Saturday, July 11, 2009 8:16 PM | I guess I have to use AcceptChanges and set the row state to modified in order to resolve that problem
but How can I do that ?? | | W.A.S Saturday, July 11, 2009 9:29 PM | I assume that you had an added row, and did the insert, but then since you didn't AcceptChanges, the next time around it thought you needed to insert again. So, yeah, you should always AcceptChanges after you have updated the database ... so, what did you mean "how can I do that"?
Would you like a suggestion for an easier way to add those parameters and construct your DataAccess?
public void UpdateRowInDataBase(DataRow rowToUpdate)
{
OpenConnection();
SqlDataAdapter adapter = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand();
//add our parameters to our command object
cmd.Parameters.AddWithValue("@Status", rowToUpdate[0]);
cmd.Parameters.AddWithValue("@GivenName", rowToUpdate[1]);
cmd.Parameters.AddWithValue("@FamilyName", rowToUpdate[2]);
cmd.Parameters.AddWithValue("@ID", rowToUpdate[3]);
string UpdateCommand = "UPDATE Employees SET Status = @Status, GivenName = @GivenName, FamilyName = @FamilyName WHERE ID = @ID";
string InsertCommand = "INSERT Employees (Status, GivenName, FamilyName, ID) SELECT @Status, @GivenName, @FamilyName, @ID";
if (rowToUpdate.RowState == DataRowState.Added)
cmd.CommandText = InsertCommand;
else
cmd.CommandText = UpateCommand;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
CloseConnection();
}
~~Bonnie Berent [C# MVP] - Marked As Answer byW.A.S Sunday, July 12, 2009 11:13 AM
-
| | BonnieB Saturday, July 11, 2009 11:26 PM | Thank you so much Can you explain to me again why I need both:
string UpdateCommand = "UPDATE Employees SET Status = @Status, GivenName = @GivenName, FamilyName = @FamilyName WHERE ID = @ID";
string InsertCommand = "INSERT Employees (Status, GivenName, FamilyName, ID) SELECT @Status, @GivenName, @FamilyName, @ID";
if (rowToUpdate.RowState == DataRowState.Added)
cmd.CommandText = InsertCommand;
else
cmd.CommandText = UpateCommand;
| | W.A.S Sunday, July 12, 2009 12:29 AM | Also, consider that I am modifying a row, and changing the ID of this row to the same ID of an employee that already in the database... My system will accept it , and now I have to employees with the same ID :-( | | W.A.S Sunday, July 12, 2009 12:36 AM | Because you mentioned that you had a PrimaryKey constraint error (Violation of PRIMARY KEY constraint 'PK_TableEmployee'. Cannot insert duplicate key in object 'dbo.TableEmployee'.
The statement has been terminated), I assumed that you had a problem with Inserts vs Updates and I also assumed that ID was your PrimaryKey, hence the reason forneeding both an Update statement and an Insert statement... I guess that was an erroneous assumption? ... or I'm still not quite following you.
If ID is indeed a Primary Key, then you shouldn't allow the user tochange it. If ID is something that must be input by the user, then it shouldn't be a Primary Key, you should use meaningless numbers (IDENTITY columns in SQL Server) or GUIDs forPrimary Keys.
~~Bonnie Berent [C# MVP] | | BonnieB Sunday, July 12, 2009 4:49 AM | Thank you
Lets consider that I still want to give the user the opportunity to change the ID in Modification process. (ID is Primary Key)
How can I check that the new modified ID is already in the database.
Actually, in the Insert Query, the SQL throws AN EXCEPTION about that.
Thank you
| | W.A.S Sunday, July 12, 2009 11:13 AM | This is one reasonwhy it's a bad databasedesign to allow a meaningful, user-definable field to be PK. I hope you do not also have Foriegn Keys that depend on this PK, because when that PK changes, it will really screw up your Foriegn Keys.
Here's another big problem I see with this design ... how do you knowwhen a user changes an existing ID to another existing ID? IOW, the user is on the row with ID = 'A', they want to change it to ID = 'Z', but there is already an ID of 'Z' in the database. You're not adding a new row, so your update statement has no way of knowingthat you are updating row 'A' and will go and update the wrong row 'Z'. The way around this dilemma is to save the old value of the ID field somehow so that your update statement can use WHERE ID = @OldID.
But assuming that you cannot change the database design, what you can do when you're adding a new row, is first send a Query to the database first and see if that ID already exists. Since I'm not sure when you're actually sending that row to the UpdateRowInDataBase() method, I'm not sure where to suggest that you do this check, but basically you could use an ExecuteScalar to check for the existence of the ID. Here's a snippet:
cmd.Parameters.AddWithValue("@ID", ID);
cmd.CommandText = "SELECT COUNT(*) FROM Employees WHERE ID = @ID"
int count = (int)cmd.ExecuteScalar();
~~Bonnie Berent [C# MVP] | | BonnieB Sunday, July 12, 2009 3:00 PM | Thank you happy for discussing that with youso I can understand more. You said
This is one reason why it's a bad database design to allow a meaningful, user-definable field to be PK<br/>
I hope you do not also have Foreign Keys that depend on this PK, because when that PK changes, it <br/>
will really screw up your Foreign Keys.
yes I am using a meaningful value as the PK. I am using the employee - It is nout an inex , but like passport ID :-) I thought to use an index which illustrates the row's index in the table, but I had a problem to reorder this indexes when I delete few rows from the database. Assume that we have the following table: Index GivenName FamilyName ID -------------------------------------------------------------------------------------- 1 Wael Salman 033117681 2 Eli Salman 033117682 3 A Salman 033117683 4 B Salman 033117684 5 C Salman 033117685 6 D Salman 033117686 7 E Salman 033117687 8 F Salman 033117688 -------------------------------------------------------------------------------------- Assume that the PK is the index, how can ensure rearranging the index after deleting for example rows (2,5,7). After deleting these 3 rows we will have 5 rows , and we need to ensure that we have the following indexes (1,2,3,4,5,6). SQL must rearrange the PK automatically I guess. About my database design. id o not have any foreign keys. Simply I have one table with fe colums and one primary Key. I have a registration for to insert teh employee details (Name , ID , Salry, ... ) (Winform which has textboxes , other objects).
- Clicking on a button ("Add"), will add the employee details to teh DataGridView Table , and commit it to teh SQL table.
- After adding it, and we want to modify the details, we just double click on that row in order to modify and update the record, and then commit the change sback to database.
Hope you can suggest solutions. Wael | | W.A.S Sunday, July 12, 2009 11:00 PM | Well, I don't know why you'd want to rearrange based on what you're calling the Index ... if you're talking about the database, you don't rearrange the data there. You can order it when you retrieve the data ... I typically will have a sortorder column to allow me to SELECT * FROM BobTable ORDER BY sortorder.
But, don't you see my point about changing the ID? How do you update your database? I assume you have an UPDATE statement similar to this:
"UPDATE Employees SET Status = @Status, GivenName = @GivenName, FamilyName = @FamilyName WHERE ID = @ID"
What happens when the user changes ID from 033117681 to 033117686 ?
Both of those ID's already exist and your UPDATE statement will end up updating the wrong one.
My suggestion stands: assuming the column you called Index is an IDENTITY column, then there you have yourPK. Will that work for you?
~~Bonnie Berent [C# MVP] | | BonnieB Tuesday, July 14, 2009 3:15 AM | Thank you
yes I see your point
I use Update like this
"UPDATE Employees SET Status = @Status, GivenName = @GivenName, FamilyName = @FamilyName WHERE ID = @ID"
and I have asked you about this point few days ago also .
I will check the IDENTITY Column issue and back to you :-) | | W.A.S Tuesday, July 14, 2009 10:27 AM | Hi BonnieB ,
Hope you are ok and doing fine :-)
Finally I had time to go on with my C# problems.
I have added an Identity Column to my table , which has auto-increment attribute, and I still have some problems.
- I am using int Identity = (int)cmd.ExecuteScalar();// (First Columns value) The Identity Value to retrieve the Identity value, but surprisingly , even I delete all the rows from table (by delete query in the table designer), and then add a new first row to the DataGridView , I notice that the number does not start from 1 ( which is the seed), but still remember the last value befroe deleting.
- The Identity was set as PrimaryKey, but the user can still insert 2 duplicated clients detials , that have teh same ID number. How can I disabble that now??
Here is teh code that I use:
public void CommitNewRowToDatabase(DataRow newAddedRow)
{
OpenConnection();
SqlDataAdapter adapter = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand();
//add our parameters to our command object
cmd.Parameters.AddWithValue("@Status", newAddedRow[1]);
cmd.Parameters.AddWithValue("@GivenName", newAddedRow[2]);
cmd.Parameters.AddWithValue("@FamilyName", newAddedRow[3]);
cmd.Parameters.AddWithValue("@ID", newAddedRow[4]);
//string UpdateCommand = "UPDATE TableEmployee SET Status = @Status, GivenName = @GivenName, FamilyName = @FamilyName WHERE ID = @ID";
String UpdateCommand = "UPDATE TableEmployee SET Status = @Status, GivenName = @GivenName, FamilyName = @FamilyName ,ID = @ID";
//String InsertCommand = "INSERT INTO TableEmployee Values (@Status , @GivenName , @FamilyName , @ID)";
String InsertCommand = "INSERT INTO TableEmployee Values (@Status , @GivenName , @FamilyName , @ID) SELECT CAST(scope_identity() AS int) WHERE ID = @ID";
if (newAddedRow.RowState == DataRowState.Detached)
cmd.CommandText = InsertCommand;
else
cmd.CommandText = UpdateCommand;
cmd.Connection = conn;
int Identity = (int)cmd.ExecuteScalar();// (First Colums value) The Identity Value
cmd.ExecuteNonQuery();
CloseConnection();
}
Have a look also on : String InsertCommand = "INSERT INTO TableEmployee Values (@Status , @GivenName , @FamilyName , @ID) SELECT CAST(scope_identity() AS int) WHERE ID = @ID"; It thows an exception about the ID, e = {"Invalid column name 'ID'."} tHANK YOU
| | W.A.S Wednesday, July 22, 2009 2:48 PM | You have your UPDATE and your INSERT backwards. Your UPDATE should use
WHERE ID = @ID
You have the WHERE statement in the INSERT where it makes no sense to have it (probably why you get the error).
~~Bonnie Berent [C# MVP] | | BonnieB Wednesday, August 05, 2009 4:30 AM |
|
index > Windows Forms Data Controls and Databinding > Modify a DataGridView row , and update the SQL table - datatbase