Problem with update when updating all rows of a table through dataset and saving back to d

Hi,

I have an application where I'm filling a dataset with values from a table. This table has no primary key. Then I iterate through each row of the dataset and I compute the value of one of the columns and then update that value in the dataset row. The problem I'm having is that when the database gets updated by the SqlDataAdapter.Update() method, the same value shows up under that column for all rows. I think my Update Command is not correct since I'm not specifying a where clause and hence it is using just the value lastly computed in the dataset to update the entire database. But I do not know how to specify a where clause for an update statement when I'm actually updating every row in the dataset. Basically I do not have an update parameter since all rows are meant to be updated. Any suggestions?

SqlCommand snUpdate = conn.CreateCommand();

snUpdate.CommandType =CommandType.Text;

snUpdate.CommandText ="Update TestTable set shipdate = @.shipdate";

snUpdate.Parameters.Add("@.shipdate",SqlDbType.Char, 10,"shipdate");

string jdate ="";

for (int i = 0; i < ds.Tables[0].Rows.Count - 1; i++)

{

jdate = ds.Tables[0].Rows[i]["shipdate"].ToString();

ds.Tables[0].Rows[i]["shipdate"] = convertToNormalDate(jdate);

}

da.Update(ds,"Table1");

conn.Close();

-Thanks

a quick once over and i have a few questions--what all in your TestTable? Also in all lines of code you refer to your table as Tables[0] you should always stick to one way. Your right about your Update, so when we see what's in your TestTable we'll be able to help you out a little bit more.

|||

Thanks for your response. My table contains 2 fields SKU numbers and Ship Dates. I changed my update statement as follows and it worked.

snUpdate.CommandText ="Update TestTable set shipdate = @.shipdate where skunum=@.skunum";

snUpdate.Parameters.Add("@.shipdate",SqlDbType.Char, 10,"shipdate");

snUpdate.Parameters.Add("@.skunum",SqlDbType.Int, 4,"skunum");

snUpdate.Parameters["@.skunum"].SourceVersion =DataRowVersion.Original;

-Thanks

|||So is skunum a unique value? If not, then it'll update all rows with that skunum. If it is, then why isn't that your primary key?|||The skunum is not unique. The table does have some duplicated data which I don't have to worry about if they get updated with the same value. This is just an old table whose data will be transformed into a different table and used in an app after my updates are done. Thanks.