Updating datasource from dataset

[paulhudson]

Using the script below I create an editable datagrid. In edit mode all changes are successfully made to the dataset and are displayed on reloading page.

The problem occurs when I attempt to write changes back to the datasource using the update method on the last line of the subroutine Update_team:

oDataAdapter.Update(tpDataSet, "tbTeamPlayer")

Error:

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

Now as I understand I can use the Fill method in order to refresh the dataset before using the update. No suprise that when I put this as first line in the sub update_team the changes are lost. This is the line I added:

oDataAdapter.Fill(tpDataSet, "tbTeamPlayer")

I am doing something wrong but I cannot figure it out. The msdn pages have made it no clearer for me!

	Public oConnect As New OleDbConnection(ConfigurationSettings.AppSettings("connString"))
  	Public oDataAdapter As New OleDbDataAdapter("spTeamByMatchDataID", oConnect)
	Public UpdateCMD As OleDbCommand = New OleDbCommand("UpdateTeamPlayer", oConnect)
	Public InsertCMD As OleDbCommand = New OleDbCommand("InsertTeamPlayer", oConnect)
	Public DeleteCMD As OleDbCommand = New OleDbCommand("DeleteTeamPlayer", oConnect)
	Public PrimaryKeyColumns(0) As DataColumn

SUB Page_load
	oDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
	oDataAdapter.SelectCommand.Parameters.Add("@ParamID", MatchDataID)
	oDataAdapter.UpdateCommand = UpdateCMD
	oDataAdapter.InsertCommand = InsertCMD
	oDataAdapter.DeleteCommand = DeleteCMD	

	If Not Page.IsPostback Then
		BindData
	Else
		tpDataSet = Session("tpDataSet")
		dtTeamPlayers = Session("dtTeamPlayers")
		PrimaryKeyColumns(0)= dtTeamPlayers.Columns("TeamPlayerID")
		dtTeamPlayers.PrimaryKey = PrimaryKeyColumns
	End If
End Sub

Public sub BindData
	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


Sub Update_team
	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", "TeamPlayerID")
	oDataAdapter.UpdateCommand.Parameters.Add("@PlayerID", "PlayerID")
	oDataAdapter.UpdateCommand.Parameters.Add("@TPositionID", "TPositionID")
	oDataAdapter.UpdateCommand.Parameters.Add("@PNumber", "PNumber")
	oDataAdapter.UpdateCommand.Parameters.Add("@MatchDataID", "MatchDataID")
	oDataAdapter.UpdateCommand.Parameters.Add("@Goals", "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 appreciated. Thanks

 

[SecondToNone]

wrong forum...

Please post it in ASP.NET forum

-SecondToNone

 

[neilharris]

It looks like you haven't created any sort of SQL statement for the update command to use, which is why it is complaining that it cannot find any of the SQL reserved words that it is expecting.

 

[paulhudson]

Sorry about using the wrong forum! Can I move this post?

neilharris - I was under the impression that I could use stored procedures:

Public oDataAdapter As New OleDbDataAdapter("spTeamByMatchDataID", oConnect)
Public UpdateCMD As OleDbCommand = New OleDbCommand("UpdateTeamPlayer", oConnect)
oDataAdapter.UpdateCommand = UpdateCMD

And then to provide the parameters as expected by the UpdateTeamPlayer procedure in db. Is this wrong? Must the updatecommand have an actual sql statement?

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", "TeamPlayerID")
oDataAdapter.UpdateCommand.Parameters.Add("@PlayerID", "PlayerID")
oDataAdapter.UpdateCommand.Parameters.Add("@TPositionID", "TPositionID")
oDataAdapter.UpdateCommand.Parameters.Add("@PNumber", "PNumber")
oDataAdapter.UpdateCommand.Parameters.Add("@MatchDataID", "MatchDataID")
oDataAdapter.UpdateCommand.Parameters.Add("@Goals", "Goals")

End If

 

[neilharris]

You may be correct in which case you would need to set the commandtype properties on each of the other commands.

The default is CommandType.Text which obviousley won't work calling a stored procedure.