Updating database produces error

[RedHunter]

When I try to update my database I get the following error

"Update requires a valid InsertCommand when passed DataRow collection with new rows"

which seems to suggest that I dont have an insert command in place - but I do.

This is a snippet of my code"

'get data row to pass accross
       Dim tblSystem As DataTable
       Dim drCurrent As DataRow

       tblSystem = DesignData.Tables("Systems")
       drCurrent = tblSystem.Rows(0)

       'reopen connection to master table 
       Dim conn As OleDbConnection = DesignConnection()
       conn.Open()
       Dim sql As String = "Select * from Systems"
       'create data adaptor
       Dim daSystem As New OleDbDataAdapter
       daSystem = CreateDataAdapter(conn, sql)

       daSystem.Update(tblSystem)

       Exit Sub
ErrorHandler:
       Console.WriteLine(ErrorToString)
       Resume Next
   End Sub
   
   Friend Function CreateDataAdapter(ByVal cn As System.Data.OleDb.OleDbConnection, ByVal sql As String) As System.Data.OleDb.OleDbDataAdapter
       ' Create the DataAdapter
       Dim da As New System.Data.OleDb.OleDbDataAdapter(sql, cn)
       ' create a CommandBuilder
       Dim cb As New System.Data.OleDb.OleDbCommandBuilder(da)
       ' derive the delete/insert/update commands
       da.MissingSchemaAction = MissingSchemaAction.AddWithKey
       da.DeleteCommand = cb.GetDeleteCommand()
       da.InsertCommand = cb.GetInsertCommand()
       da.UpdateCommand = cb.GetUpdateCommand()
       ' dispose the CommandBuilder and return the result
       cb.Dispose()
       Return da
   End Function

the command builder has created an insert command here it is-

 

INSERT INTO Systems (DesignID, ActiveBlocks, AirInTemp, AirOutTemp, AirMassFlow, CSmargin, CTmargin, DutyClean, DutyFoul, Exsurf, FaceVel, FinCount, FlowRegime, FluidInTemp, FluidFlowRate, FluidOutTemp, FluidPd, FSmargin, FTmargin, InstalledBlocks, Insurf, MarginClean, MarginFoul, PassVal, RowTubes, RowVal, SurfaceRatio, SystemAirPd, TubeCount, TubeCSA, TubesPerPass, TubesPerRow, TubeVelocity, WallRes) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

 

there are similar but much bigger commands for Update and Delete

 

so why the error?

[bprashanth]

"Update requires a valid InsertCommand when passed DataRow collection with new rows"

I hope you have an Insert Command at place, just that the command is not valid.

INSERT INTO Systems (DesignID, ActiveBlocks, AirInTemp, AirOutTemp, AirMassFlow, CSmargin, CTmargin, DutyClean, DutyFoul, Exsurf, FaceVel, FinCount, FlowRegime, FluidInTemp, FluidFlowRate, FluidOutTemp, FluidPd, FSmargin, FTmargin, InstalledBlocks, Insurf, MarginClean, MarginFoul, PassVal, RowTubes, RowVal, SurfaceRatio, SystemAirPd, TubeCount, TubeCSA, TubesPerPass, TubesPerRow, TubeVelocity, WallRes) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

Looking at the command,

1. Check if DesignID is a required field in the table, may be you are not specifying the value (or you should not specify - like autogenerate)

2. Verify if all the field names are correct