none
ADO.NET Access 97 write error RRS feed

  • Question

  • Hi guys,

    I've been pulling my hair out for a couple of days now trying to get my .net 2.0 app to write to an Access 97 database.

    I have written an app that compares 2 databases and builds up a dataset of changes on a per table basis.  This seems to work fine until I try to write the changes back to the db.

    DataSet changedData = masterDataSet.GetChanges();
    if (changedData != null)
    {
      masterDataAdapter.Update(changedData); // Error here
    }

    I get this error:

    "System.Data.OleDb.OleDbException: No value given for one or more required parameters.\r\n   at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)\r\n   at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)\r\n   at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)\r\n   at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)\r\n   at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)\r\n   at System.Data.Common.DbDataAdapter.Update(DataSet dataSet)\r\n   at SynchronizeDB.SyncDatabase.SaveDB() in......

    Now I know the OleDbCommandBuilder is sucky as hell but I have to use it.  I do not know how many tables I will be updating - it could change (currently there's maybe 20) and they are all different.  I can get around the SQL syntax errors by coding up something to put the [] in.  Anywho, I hard coded what I think is the correct SQL to get by the syntax errors for the time being in my test app to:

     new OleDbCommand("UPDATE Student SET [ServiceNumber]=? AND [Surname]=?  AND [Initials]=? AND [Rank]=? AND [TradeId]=? AND [Unit]=? AND [Timestamp]=? WHERE [ID]=?", masterDbConnection);
     new OleDbCommand("INSERT INTO [Student] ([ServiceNumber], [Surname], [Initials], [Rank], [TradeId], [Unit], [Timestamp]) VALUES (?, ?, ?, ?, ?, ?, ?)", masterDbConnection);

    This gets my past the syntax errors... but it still doesn't work and I get the error from above.

    What I can't get around is the fact that the Adapter.Update seems to not be relating the data to the SQL statement when it's executed.  I know I can manually add the parameters and effectively hard code the rows in the changedData DataSet tables to the DB columns.  Something like this:

    command.Parameters.Add("Id", OleDbType.Numeric, 0).Value = row[0];
    command.Parameters.Add("ServiceNumber", OleDbType.Numeric, 0).Value = row[1];
    command.Parameters.Add("Surname", OleDbType.Char, 50).Value = row[2];
    command.Parameters.Add("Initials", OleDbType.Char, 5).Value = row[3];
    command.Parameters.Add("Rank", OleDbType.Char, 50).Value = row[4];
    command.Parameters.Add("TradeId", OleDbType.Numeric, 0).Value = row[5];
    command.Parameters.Add("Unit", OleDbType.Char, 50).Value = rowDevil;
    command.Parameters.Add("[Timestamp]", OleDbType.Numeric, 0).Value = row[7];


    But that would be messy as hell, require a bucket load of code and mean my app would need to be rebuilt whenever the database had tables added or removed.

    Has anyone got an idea of how to get auto-generated update and insert commands to work with Access 97?  Or can you see anything blatently wrong with what I'm trying to do?

    Many thanks,

    Richard.
    Friday, September 7, 2007 4:33 PM

Answers

  • In your INSERT statement the VALUES clause only contains placeholders for seven parameters.

     

    Monday, September 10, 2007 9:54 PM

All replies

  • Moved this thread to the .NET Fx Data Access and Storage Forum.

    Friday, September 7, 2007 4:54 PM
  •  

    Sorry to be a pain... has nobody got any clues as to why the update() is crashing my app and not inserting it's data?

     

    Many thanks,

     

    Richard

    Monday, September 10, 2007 5:23 PM
  • In your INSERT statement the VALUES clause only contains placeholders for seven parameters.

     

    Monday, September 10, 2007 9:54 PM
  • Hi there,

    Thanks for the response.  That was an error cutting and pasting from some old code.  The problem still persists.

    It would appear that the data simply is not being put in place of the ?'s when update is called.  I have no idea why.  I have valid statements for the commands. 

    Could this be a problem with ADO.NET 2.0 and JET 4.0 not being compatible for DB writes via OleDBDataAdapter.Update() without adding parameters?

    I tried this with an Access 2003 DB and get the same problem.

    This is driving me nuts!  it really should be so simple...


    Friday, September 14, 2007 9:11 AM
  • So just to confirm, you are still receiving the error? BTW, I usually avoid using reserved words for column names as they tend to cause problems in code if you're not careful. In your example Timestamp is a reserved word.

     

    Friday, September 14, 2007 11:46 AM