Updating datasource from dataset fails

[paulhudson]

I am trying to update a database from a dataset but keep getting the following error:

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'

on the line 3rd from the bottom of this post = oDataAdapter.Update(tpDataSet, "tbTeamPlayer")

 

The page presents a list of team players from a specific match. The opportunity is given that the list of names can be

altered. Players can be deleted, new ones added and existing players modified.

 

Presenting the list works fine using this sub whenever the page is first called:

 

Public sub BindData
Dim oConnect As New OleDbConnection(ConfigurationSettings.AppSettings("connString"))
 	Dim oDataAdapter As New OleDbDataAdapter("spTeamByMatchDataID", oConnect)
oDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
oDataAdapter.SelectCommand.Parameters.Add("@ParamID", MatchDataID)
Dim UpdateCMD As OleDbCommand = New OleDbCommand("UpdateTeamPlayer", oConnect)
oDataAdapter.UpdateCommand = UpdateCMD
Dim InsertCMD As OleDbCommand = New OleDbCommand("InsertTeamPlayer", oConnect)
oDataAdapter.InsertCommand = InsertCMD
Dim DeleteCMD As OleDbCommand = New OleDbCommand("DeleteTeamPlayer", oConnect)
oDataAdapter.DeleteCommand = DeleteCMD	

tpDataSet = New Dataset() 
oDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
Dim dtTeamPlayers As DataTable = new DataTable("tbTeamPlayer")
oDataAdapter.Fill(tpDataSet, "tbTeamPlayer")
Dim PrimaryKeyColumns(0) As DataColumn
PrimaryKeyColumns(0)= dtTeamPlayers.Columns("TeamPlayerID")
dtTeamPlayers.PrimaryKey = PrimaryKeyColumns
dgr1.DataSource = tpDataSet.Tables("tbTeamPlayer")
dgr1.DataBind()
Session("tpDataSet") = tpDataSet
Session("dtTeamPlayers") = tpDataSet.Tables("tbTeamPlayer")
End Sub

 

The datagrid that presents the data has no problems. It contains a button column for deleting the row and the usual

EditCommandColumn. When selecting the edit button it enters the edit mode presenting dropdownlists etc. Changing the data

works fine. On selecting the update button the datagrid is binded with the new data displayed. If cancelling then the original

data is displayed.

 

The problem comes when trying to update to the source database. I know I have probably got a fundamental problem here.

Here is the sub that attempts to update the database:

 

Sub SubmitTeam(sender As Object, e As EventArgs)
tpDataSet = Session("tpDataSet")
dtTeamPlayers = Session("dtTeamPlayers")

'Validation to ensure player number, name and positions are not duplicated

'now to create the commands to update the database
Dim oConnect As New OleDbConnection(ConfigurationSettings.AppSettings("connString"))
 	Dim oDataAdapter As New OleDbDataAdapter("spTeamByMatchDataID", oConnect)
oDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
oDataAdapter.SelectCommand.Parameters.Add("@ParamID", MatchDataID)

Dim UpdateCMD As OleDbCommand = New OleDbCommand("UpdateTeamPlayer", oConnect)
oDataAdapter.UpdateCommand = UpdateCMD
Dim InsertCMD As OleDbCommand = New OleDbCommand("InsertTeamPlayer", oConnect)
oDataAdapter.InsertCommand = InsertCMD
Dim DeleteCMD As OleDbCommand = New OleDbCommand("DeleteTeamPlayer", oConnect)
oDataAdapter.DeleteCommand = DeleteCMD	

Dim CurrModRow As DataRow
Dim ModifiedRow() As DataRow = dtTeamPlayers.Select(Nothing,Nothing,DataViewRowState.ModifiedCurrent)
If Not (ModifiedRow.Length < 1 ) Then

oDataAdapter.UpdateCommand.Parameters.Add("@TeamPlayerID", OleDbType.Integer, "TeamPlayerID")
oDataAdapter.UpdateCommand.Parameters.Add("@PlayerID", OleDbType.Integer, "PlayerID")
oDataAdapter.UpdateCommand.Parameters.Add("@TPositionID", OleDbType.Integer, "TPositionID")
oDataAdapter.UpdateCommand.Parameters.Add("@PNumber", OleDbType.Integer, "PNumber")
oDataAdapter.UpdateCommand.Parameters.Add("@MatchDataID", OleDbType.Integer, "MatchDataID")
oDataAdapter.UpdateCommand.Parameters.Add("@Goals", OleDbType.Integer, "Goals")

End If
Dim CurrNewRow As DataRow
Dim NewRow() As DataRow = dtTeamPlayers.Select(Nothing,Nothing,DataViewRowState.Added)
If Not (NewRow.Length < 1 ) Then

oDataAdapter.InsertCommand.Parameters.Add("@TeamPlayerID", OleDbType.Integer, "TeamPlayerID")
oDataAdapter.InsertCommand.Parameters.Add("@PlayerID", OleDbType.Integer, "PlayerID")
oDataAdapter.InsertCommand.Parameters.Add("@TPositionID", OleDbType.Integer, "TPositionID")
oDataAdapter.InsertCommand.Parameters.Add("@PNumber", OleDbType.Integer, "PNumber")
oDataAdapter.InsertCommand.Parameters.Add("@MatchDataID", OleDbType.Integer, "MatchDataID")
oDataAdapter.InsertCommand.Parameters.Add("@Goals", OleDbType.Integer, "Goals")

End If
Dim CurrDelRow As DataRow
Dim DeletedRow() As DataRow = dtTeamPlayers.Select(Nothing,Nothing,DataViewRowState.Deleted)
If Not (DeletedRow.Length < 1 ) Then

Dim oParam As OleDbParameter = oDataAdapter.DeleteCommand.Parameters.Add("@TeamPlayerId", "TeamPlayerID")
oParam.SourceVersion = DataRowVersion.Original

End If

oDataAdapter.Update(tpDataSet, "tbTeamPlayer")
End Sub

 

Any help would be greatly appreciated!

 

Thanks Paul