Unable to insert row to a table using OleDBAdapter

Question

OleDbConnection _connection = new OleDbConnection();
        StringBuilder ConnectionString = new StringBuilder("");
        ConnectionString.Append(@"Provider=Microsoft.Jet.OLEDB.4.0;");
        ConnectionString.Append(@"Extended Properties=Paradox 5.x;");
        ConnectionString.Append(@"Data Source=C:\Clients\Rail\Wheelsets;");
        _connection.ConnectionString = ConnectionString.ToString(); 
        _connection.Open();
        OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM Vehicles;", _connection);
        DataSet dsRetrievedData = new DataSet();
        da.Fill(dsRetrievedData);   
        OleDbCommandBuilder builder = new OleDbCommandBuilder(da);
        da.InsertCommand = builder.GetInsertCommand();
        ////Insert new row
        DataRow rowNew = dsRetrievedData.Tables[0].NewRow();
        rowNew[dsRetrievedData.Tables[0].Columns[0].ColumnName] = "978";
        rowNew[dsRetrievedData.Tables[0].Columns[1].ColumnName] = "222";
        rowNew[dsRetrievedData.Tables[0].Columns[4].ColumnName] = "999";
        rowNew[dsRetrievedData.Tables[0].Columns[5].ColumnName] = "999";
        rowNew[dsRetrievedData.Tables[0].Columns[6].ColumnName] = "999";
        dsRetrievedData.Tables[0].Rows.Add(rowNew);
        dsRetrievedData.Tables[0].AcceptChanges();
        dsRetrievedData.AcceptChanges();
        int result = da.Update(dsRetrievedData);

thats the code i use. as you can see i have a paradox table. and some how result = 0 at end of it all. any ideas what is my mistake?

thanks upfront.

-=Noam=-

score 0 · Answer 1 · answered Dec 13 '10 at 11:00

0

What is your InsertCommand?

Also try after removing these line

dsRetrievedData.Tables[0].AcceptChanges();
dsRetrievedData.AcceptChanges();

if you call AcceptChanges all changes in the datatable is accepted so there is no rows which is changed so there is nothing to update

answered Dec 13 '10 at 11:00

TalentTuner

17,262
5
38
63

the insert command: INSERT INTO Vehicles (Vehicle Number, Vehicle Mfg, Distance Traveled, Operating Hours, Truck #1 Serial Number, Truck #2 Serial Number, Truck #3 Serial Number) VALUES (?, ?, ?, ?, ?, ?, ?) and after removing this 2 lines i get a syntax exception. – Bjorkson Dec 13 '10 at 11:07
Syntax error in INSERT INTO statement. Not very informative. I've asked befor and i got replays about special characters forparadox table need to be quted with ("") but when i tried with sql comman the i got other problem: http://stackoverflow.com/questions/4366988/problem-with-insert-query-to-paradox-table-using-c – Bjorkson Dec 13 '10 at 11:12
Truck #1 Serial Number, Truck #2 Serial Number, Truck #3 Serial Number , are these column names in your file? – TalentTuner Dec 13 '10 at 11:14
unfortunately thats the names and i cannot change them..... there are other applications using this table on the machine. – Bjorkson Dec 13 '10 at 11:17
ok, try to verify that columnname is the problem so create a replica of your file and rename these columns to something meaningful and use this file and then try to add and verify that rows is inserted in the file or not by opening the file and manually verify it. – TalentTuner Dec 13 '10 at 11:20
Im quit sure it is since in my trys to overcome it SQL wise i mannaged to insert to the table if i didnt insert into this columns. and after i extracted the column name in order to set it into query i got an unknown field exception. – Bjorkson Dec 13 '10 at 11:31

score 0 · Answer 2 · answered Dec 13 '10 at 11:02

Remove call to AcceptChanges() :

dsRetrievedData.Tables[0].AcceptChanges();
dsRetrievedData.AcceptChanges();

According to MSDN:

Commits all the changes made to this DataSet since it was loaded or since the last time AcceptChanges was called.

Which means, it marks newly added row as not new.

Unable to insert row to a table using OleDBAdapter

2 Answers2