DataAdapter Update Method

Question

Hi Guys I'm trying to use the Update method of my DataAdapter. As i understnad it i fill a dataset with my DataAdapter make the changes to my Dataset then i can call the update method of the DataAdapter and .Net will determin the changes and perform the update. I get an exception asking for an update or insert command object for the DataAdapter. Surley if i have to write my own Commands for updates and Inserts etc the Update method of the DataAdapter is pointless? Here is the code: string ConnString = PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=C:\C#\db.mdb; OleDbConnection conn = new OleDbConnection(ConnString); OleDbCommand cmd = new OleDbCommand(SELECT * FROM tblData conn); OleDbDataAdapter adapter = new OleDbDataAdapter(); adapter.SelectCommand = cmd; DataSet ds = new DataSet(); DataTable dt = new DataTable(tbl); ds.Tables.Add(dt); try { conn.Open(); adapter.Fill(ds.Tables[tbl]); } catch(System.Data.OleDb.OleDbException ex) { Console.WriteLine(ex.Message); } catch(Exception exx) { Console.WriteLine(exx.Message); } finally { conn.Close(); } System.Data.DataRow NewRow = ds.Tables[tbl].NewRow(); NewRow[dbName] = Peter; NewRow[dbAge] = 25; ds.Tables[tbl].Rows.Add(NewRow); adapter.Update(dt); Console.WriteLine(Update Successful); Any help would be much appriceated. Thanks Guys Chris

p.jones · Answer

MikeR is correct: Dim CmdBlder As OleDbCommandBuilder = _ New OleDbCommandBuilder(AppAdapter) Where AppAdapter is the data adapter.

miker · Answer

AcceptChages() method belongs to DataSet/DataTable/DataRow classes and has almost nothing to do with the DataAdapter. What you need is OleDbCommandBuilder class.

plot · Answer

Thanks for the reply. I knew about providiong command objects for the dataAdapter. I read that ADO.NET will figure out what to change and gennerate the SQL itself by compareing the changes in the dataset from the original one it stores in memory. Providing that is that you don't call .AcceptChanges() cause that will update the dataset that it compares to. Update() is usless if i have to provide my own command Objects for Select Update and Delete. Chris

goce micevski · Answer

Data adapter can make insert and update as well as select from db but as you specify SelectCommand you'll need also to specify insert and update command cos dataAdapter will not know which of the fields in the dataset to insert/update and in which table.. So you'll need to create insert/update command Example: insertCommand = new SqlCommand(name od the stored procedureconnnection); insertCommand.CommandType = CommandType.StoredProcedure; SqlParameterCollection sqlParams = insertCommand.Parameters; sqlParams.Add(new SqlParameter(param1 SqlDbType.NVarChar255)); sqlParams[param1].SourceColumn = here you tell column name of the table you will pass to dataadapter for insert/update; sqlParams.Add(new SqlParameter(param2 SqlDbType.NVarChar100)); sqlParams[param2].SourceColumn = column name; if you don't use store procedures you can write as text in the commandName line string storedProcedure = insert in sometable (field1field2field3) values(@param1@param2@param3); example: insertCommand = new SqlCommand(storedProcedureconnnection); symbol @ with text after is considered as sqlParameter; So this will solve the problem not using stored procedures. After creating insert command create update command in the same way. after that Dataadapter.insertcommand = createdInsertCommand; Dataadapter.updateCommand= createdUpdateCommand; DAtaadapter.Update(someTable); Uou can create delete command as well(like insert and update) and add to dataadapter(dataadapter.deletecommand = yourDeleteCommand) Note: Rows in DataTable have columnState(insertedaddeddeletedunchanged....) So for every row in datatable that has rowstate = inserted dataadapter will call insert command; if rowstate = update then deletecommand .... Hope this help