Good u have done good job but slight mistakes there are 1. U must have to add parameter to ur update command 2. Don't accept changes before ur . Adapter.Update(DataSet) state ment. It will cause to all changed rows to force to unchanged state and ur adapter.update method dont seem any changes to update.
On Jan 15, 8:35 am, Benj Nunez <[email protected]> wrote: > Hello experts, > > I've been trying to commit my changes in the dataset to the database > (sql server 2000), but still the updates don't seem to work. The goal > is to retrieve some records, remove the whitespace using trim then > save the changes back into the database. Here is what my code > currently looks like: > > public void doDirectEditTest2() > { > String sqlQuery = "SELECT ID, OLD_GSIS_NO, > ECARD_PLUS_NO FROM > TBL_MF" + > " WHERE LEN(OLD_GSIS_NO) > 11"; > > readDatabaseConfigEx(); > SqlConnection conn = new > SqlConnection(@ConnectionString); > > try > { > conn.Open(); > > SqlDataAdapter adapter = new > SqlDataAdapter(sqlQuery, conn); > > // adapter.UpdateCommand.CommandText = "UPDATE > TBL_MF"+ > // " SET ECARD_PLUS_NO = " + @ECARDID + > // " WHERE OLD_GSIS_NO = " + @OLDGSISID; > > DataSet dataset = new DataSet(); > > //SqlCommandBuilder builder = new > SqlCommandBuilder(adapter); > //adapter.MissingSchemaAction = > MissingSchemaAction.AddWithKey; // > no go > adapter.Fill(dataset); > > //perform edits to the dataset: Removes spaces > if (dataset.Tables[0].Rows.Count > 0) > { > foreach (DataRow Row in > dataset.Tables[0].Rows) > { > Row.BeginEdit(); > string RowOLDGSISNO = > Row["OLD_GSIS_NO"].ToString(); > string RowECARDPLUSNO = > Row["ECARD_PLUS_NO"].ToString(); > > RowOLDGSISNO = (RowOLDGSISNO > + "").Trim(); > RowECARDPLUSNO = > (RowECARDPLUSNO + "").Trim(); > > Row["OLD_GSIS_NO"] = > RowOLDGSISNO; > Row["ECARD_PLUS_NO"] = > RowECARDPLUSNO; > Row.EndEdit(); > //Row.AcceptChanges(); > } // of foreach > dataset.AcceptChanges(); > } // of if > > SqlCommandBuilder builder = new > SqlCommandBuilder(adapter); > adapter.Update(dataset); > } > catch (Exception ex) > { > Console.Out.WriteLine("Error: {0}", > ex.Message); > } > finally > { > conn.Close(); > } > > } // of doDirectEdit() > > I'm not sure if the CommandBuilder class is the one causing me a lot > of trouble, because I'm expecting that when you use this class, it > will generate the appropriate sql UPDATE for you. But I could be > wrong. Any ideas/tips? Please help!
