Updating access file

benglish72

Hi, sorry if this has already been asked, but I've had a quick look and not seen anything similar. I don't have time to research at the moment and work never waits... Basically, I make changed to rows in a dataset, and I want to update the access file that the data comes from. I've tried the DataSet.GetChanges, but when I call the OleDbDataAdapter.Update() is chucks a wobbly and says I need an update string. But I don't know the field names, nor do I want to. It already knows the difference, else the GetChanges would've failed, how to I get to update the changed rows without me having to construct an SQL update statement? I have tried to construct it, but it's a bit difficult when you don't know the field names, etc. Thanks for any help. Brian. "Ergo huffabo et puffabo et tuam domum inflabo" ait magnus malus lupus.

cmarmr

OleDbDataAdapter da = new OleDbDataAdapter("selectstatement",cn); OleDbCommandBuilder cb = new OleDbCommandBuilder(da); you need to create a OleDbCommandBuilder and it will create and update string for you chad

benglish72

Hi, here's some code, it basically removes unwanted characters that may be in string fields in a table, then tries to save the changes to the table. I've tried a few different variations, but none seem to work. Either I get an exception or it seems to work, but the file is unchanged. Thanks. statusBar.Text = "Cleaning "+sDataFile+"..."; if(m_dataSet!=null) { m_connection.Open(); Int32 iCount = m_dataSet.Tables["table"].Rows.Count; for(Int32 i=0;i>; if(sChanged.CompareTo(sItem)!=0) { iChanges++; statusBar.Text = "Cleaning "+sDataFile+" - Number of Changes: "+iChanges.ToString(); m_dataSet.Tables["table"].Rows[i].ItemArray[iIndex]=sChanged; } } } m_dataSet.Tables[sTable].Rows[i].EndEdit(); } } if(m_dataSet.HasChanges(DataRowState.Modified)==true) { DataSet dataChanged = m_dataSet.GetChanges(); m_dataAdapter.Update(dataChanged,sTable); m_dataSet.AcceptChanges(); } m_connection.Close(); } } "Ergo huffabo et puffabo et tuam domum inflabo" ait magnus malus lupus.

Heath Stewart

There's many things wrong with this code. First of all - don't open and close the connection yourself (especially while editing the DataSet - it's a disconnected recordset to no connection is needed; this is not ADO); the OleDbDataAdapter opens and closes the connection reliably. Second, OleDbDataAdapter.Update will call DataSet.AcceptChanges() internally - you do not need to call this yourself. Third, as the other poster said, in order to update your database with the changed data in a DataSet, you need to have the corresponding properties of an OleDbDataAdapter - UpdateCommand, InsertCommand, and DeleteCommand - assigned appropriately. So, if you have records that have been changed, the UpdateCommand is needed. If the DataSet ultimately contains rows that were added or deleted, you need the InsertCommand and DeleteCommand properties assigned, respectively. For simple queries (whatever is assigned to the SelectCommand) you can use the OleDbCommandBuilder as the other poster said. How do you expect the OleDbDataAdapter to update the database if it doesn't know how to update the database?

Microsoft MVP, Visual C# My Articles