Update() database from DataSet

[aecordoba]

Hi, there! I can't Update a table in database after a row is deleted in DataSet. I delete a row, as following: DataRow[] usersRows; usersRows = ContactsDataSet.Users.Select("UserName = '" + userName + "'"); foreach (DataRow userRow in usersRows) userRow.Delete(); and I tried to update the database: DataSetTableAdapters.UsersTableAdapter usersTableAdapter = new DataSetTableAdapters.UsersTableAdapter(); usersTableAdapter.Update(contactsDataSet.Users); But the data aren't updated. Can someone help me? Thank you in advance.

-- Adrián Córdoba

[Paul Conrad]

aecordoba wrote:

usersRows = ContactsDataSet.Users.Select("UserName = '" + userName + "'");

OT: This is dangerous for sql injection attacks...

[Giorgi Dalakishvili]

First: make sure that the rows are really deleted from the datatable. Second: What is the deletecommand for the dataadapter?

[PIEBALDconsult]

You need to set the Commands.

[PIEBALDconsult]

Don't assume the value came from an untrusted source.

[Paul Conrad]

PIEBALDconsult wrote:

Don't assume the value came from an untrusted source.

It's safer to assume it is untrusted...

[PIEBALDconsult]

No it isn't.

[Paul Conrad]

PIEBALDconsult wrote:

No it isn't.

Why not? There are plenty of idiots out there...

[aecordoba]

The DeleteCommand was automaticaly generated by Visual Studio 2005. I think the problem is the Deleted status of the rows is in DataSet. I'm sorry. I'm newbie.

-- Adrián Córdoba

[aecordoba]

Can you tell me how can I select the rows to delete? I'm sorry. I'm newbie.

-- Adrián Córdoba

[aecordoba]

The commands were automaticaly generated by Visual Studio 2005 in the moment of insert the DataSet.

-- Adrián Córdoba

[PIEBALDconsult]

But they're in the original TableAdapter. When you do DataSetTableAdapters.UsersTableAdapter usersTableAdapter = new DataSetTableAdapters.UsersTableAdapter(); you instantiate a new/uninitialized TableAdapter.

[Dave Kreskowiak]

It's never a good idea to trust ANY data comming into your app, even if it IS from a trusted source. The data can be corrupted by the time it reaches your validation code.

Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
     2006, 2007

[DarthDub]

First off do you have your update command made yet? I'll I know how to do with DataAdapters is the sql part of them, but if thats what your doing here is an example. It should be kinda the same type of thing. SqlConnection CNN = new SqlConnection("Data Source=(local);Initial Catalog=Northwind;Integrated Security=true"); SqlDataAdapter DA = new SqlDataAdapter(); DataSet DS = new DataSet(); SqlCommand cmdSelect = CNN.CreateCommand(); cmdSelect.CommandText = "Select CustomerID, ContactName, CompanyName From Customers"; // create an Update command SqlCommand cmdUpdate = CNN.CreateCommand(); cmdUpdate.CommandText = "Update Customers SET CompanyName=@CompanyName, ContactName=@ContactName WHERE CustomerID=@CustomerID"; cmdUpdate.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName"); cmdUpdate.Parameters.Add("@ContactName", SqlDbType.NVarChar, 30, "ContactName"); cmdUpdate.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID"); cmdUpdate.Parameters["@CustomerID"].SourceVersion = DataRowVersion.Original; // create an Insert command SqlCommand cmdInsert = CNN.CreateCommand(); cmdInsert.CommandText = "Insert into customers (customerid, companyname, contactname) Values (@customerid, @companyname, @contactname)"; cmdInsert.Parameters.Add("@customerid", SqlDbType.NChar, 5, "customerid"); cmdInsert.Parameters.Add("@companyname", SqlDbType.NVarChar, 40, "companyname"); cmdInsert.Parameters.Add("@contactname", SqlDbType.NVarChar, 30, "contactname"); cmdInsert.Parameters["@customerid"].SourceVersion = DataRowVersion.Original; // create a Delete command SqlCommand cmdDelete = CNN.CreateCommand(); cmdDelete.CommandText = "Delete from customers Where customerid = @customerid"; cmdDelete.Parameters.Add("@customerid", SqlDbType.NChar, 5, "customerid"); cmdDelete.Parameters["@customerid"].SourceVersion = DataRowVersion.Original; DA.SelectCommand = cmdSelect; DA.UpdateCommand = cmdUpdate; DA.InsertCommand = cmdInsert; DA.DeleteCommand = cmdDelete; DA.Fill(DS, "Customers"); //then do whatever you want to with modifying it. //And then just do update like this

[PIEBALDconsult]

But why assume in this case that the data is "coming into" the application? He could very well be using string userName=System.Security.Principal.WindowsIdentity.GetCurrent().Name or otherwise validating the value.

[aecordoba]

PIEBALDconsult wrote:

You need to set the Commands.

You are right. Visual Studio 2005 didn't generate the DeleteCommand, seemingly because the Users table in database hasn't a Primary Key. I added a Primary Key to the table, and when I added this table to the DataSet the DeleteCommand was automaticaly generated. Thank you very much.

-- Adrián Córdoba

[Dave Kreskowiak]

True. It doesn't make it 100% valid though. What if the app was run using RunAs, but without the new users profile? Suddenly, the profile path doesn't point to where the userId says it should. Making an assumption like that can also cause problems.

Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
     2006, 2007

[PIEBALDconsult]

Well, my point is that using a parameter doesn't guarantee safety, only validation of the string will do that. Even if his snippet said ... "SELECT * FROM Y WHERE X=" + textbox1.Text ..." ... I'd still not assume that proper validation wasn't performed outside the snippet.

[Dave Kreskowiak]

It would appear that you missed this part of my previous post: ... The data can be corrupted by the time it reaches your validation code.

Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
     2006, 2007

[PIEBALDconsult]

That's why we have validation code. Now, if the data gets corrupted after the validation code...