[RESOLVED] [2005] Trouble with DataTable Update

[MechEng]

I am having trouble (I believe) with a TableAdapter Update... or may also be incorrectly accessing and modifying a DataTable. I am not sure.

I hava a TableAdapter is attached to a form which is filled during the form_load sub with:

Me.Tbl_MatchDataTableAdapter.Fill(Me.ScoreboardDataSet.tbl_MatchData)

Here is where I am screwing up… (I think)
I am accessing the DataTable directly with the following select command so that I can change the column data for “GameID” for a collection of rows.

Dim str_SQL as string = "GameID Like " & "'" & str_TestGameID & "'"
Dim j As Integer = 0

Dim rows As DataRow() = Me.ScoreboardDataSet.tbl_MatchData.Select(str_SQL)

For Each Mrow As DataRow In rows
  ‘check that the rows are updating
  j = CInt(Mrow("MatchNumber"))
  Console.WriteLine(j & ", " & str_NewGameID)

  Mrow("GameID") = str_NewGameID ‘update the GameID field with the new ID
  Console.WriteLine(Mrow("GameID"))
Next

The DataTable “tbl_MatchData” is updating. When trying to update the database with the new data I get the error: “Too many fields defined.” The TableAdapter.Update command works, because I use it on other forms. On this form, I only have the TableAdapter attached. I do not have any controls bound so I am not using a BindingSource (I am not sure if that matters to this discussion though).

Private Sub SaveAll()

   Me.Validate()

   Me.Tbl_GameDataBindingSource.EndEdit()
   Me.Tbl_GameDataTableAdapter.Update(Me.ScoreboardDataSet.tbl_GameData)

   Me.Tbl_DefaultDataBindingSource.EndEdit()
   Me.Tbl_DefaultDataTableAdapter.Update(Me.ScoreboardDataSet.tbl_DefaultData)

   Me.Tbl_MatchDataTableAdapter.Update(Me.ScoreboardDataSet.tbl_MatchData)  <== Error here

End Sub

Any guidance to this VB-Nobb is appreciated in advance.

[jmcilhinney]

Firstly, the reason you use typed DataSets is to avoid the sort of code you've written in that second snippet. You should be using your own typed DataRow type:

For Each row As ScoreboardDataSet.tbl_MathDataRow In Me.ScoreboardDataSet.tbl_MatchData.Select(String.Format("GameID LIKE '{0}'", str_TestGameID))
    Console.WriteLine(row.MatchNumber & ", " & str_NewGameID)
    row.GameID = str_NewGameID
    Console.WriteLine(row.GameID)
Next row

Also, I find it odd that you're using strings for your ID values. Is there a reason you're not using integers?

As to your issue, I've never seen that error message before. Can you post the entire stack trace for the exception?

[MechEng]

-I just figured it out. I am a moron. I can not use this TableAdapter.Update command… I have more than 99 columns of data and I am using ODB connection. You would think after spending 2 days writing the update command by hand I would remember that.

I need a break and a beer. Thank for the infor JM.

********
Anyway, thanks for the quick reply, and I understand your comments.

Also, I find it odd that you're using strings for your ID values. Is there a reason you're not using integers?

I am using strings for the IDs because there are many users who will use this application and there has not been any standardization of Team IDs or Game IDs, so I created a Game ID that included two Team Abbreviations and the date of the Game. This way, data can be shared with other users in a programmatic way. Note that the GameID is not the primary key. I am using an auto-index integer for the control of the data. –best I could do.

As to your issue, I've never seen that error message before. Can you post the entire stack trace for the exception?

Well, no point now that I understand the what I have done.

thanks