[RESOLVED] [2005] Question about the DbDataAdapter Update Method

[MechEng]

Hi all.

I have a question about the DbDataAdapter Update Method. I am using the code below to updata a ACCESS 2003 database. I am doing this because of the number of columns in the database. The INSERT command works fine, but the UPDATE command will only update the current bindingsource row position (current). The effect is, I can INSERT a row and change some data then INSERT another row and change some data there. At this point, I run the code below to UPDATE the database and only the second row's data is passed to the MS database. None of the first rows data is saved.

Do I need to loop through the datatable somehow to get all the changes, or do I have a bigger issue to deal with?

Thanks for your help.

  Dim StrCon As String = Connectionstring() 'Get connection string
        ' Console.WriteLine(StrCon)

        Dim connection As OleDb.OleDbConnection = New OleDb.OleDbConnection(StrCon)

        Dim dt As DataTable = Me.ScoreboardDataSet.tbl_MatchData
        Dim dataAdapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter()
        Dim parameter As OleDb.OleDbParameter
        Dim command As OleDb.OleDbCommand

        'Generate Insertcommand statement for tbl_GreenMatchData
        command = New OleDb.OleDbCommand("INSERT INTO tbl_MatchData (GameID) " & "VALUES (@GameID)", connection)
        command.Parameters.Add("@GameID", OleDb.OleDbType.VarChar, 200, "GameID")
        dataAdapter.InsertCommand = command

        ' Create the DeleteCommand for tbl_GreenMatchData
        command = New OleDb.OleDbCommand("DELETE * FROM tbl_MatchData WHERE MatchNumber = @MatchNumber", connection)
        parameter = command.Parameters.Add("MatchNumber", OleDb.OleDbType.Integer, 3, "@MatchNumber")
        parameter.SourceVersion = DataRowVersion.Original
        dataAdapter.DeleteCommand = command

        'Generate the first UpdateCommand for tbl_MatchData
        Dim updateSQL As String = "UPDATE tbl_MatchData SET " & _
        "GameID = @GameID, " & _
        "GWrestlerID = @GWrestlerID, " & _
        "GWeight = @GWeight, " & _
        "GTD1 = @GTD1, " & _
        .
        .
        .
       "GreenStallIndex = @GreenStallIndex, " & _
        "MatchScoreLocked = @MatchScoreLocked WHERE MatchNumber = @MatchNumber"

        command = New OleDb.OleDbCommand(updateSQL, connection)

        command.Parameters.Add("@GameID", OleDb.OleDbType.VarChar, 200, "GameID")
        command.Parameters.Add("@GWrestlerID", OleDb.OleDbType.VarChar, 200, "GWrestlerID")
        command.Parameters.Add("@GWeight", OleDb.OleDbType.SmallInt, 5, "GWeight")
        command.Parameters.Add("@GTD1", OleDb.OleDbType.Integer, 3, "GTD1")
       .
       .
       .
        command.Parameters.Add("@GreenStallIndex", OleDb.OleDbType.Integer, 3, "GreenStallIndex")
        command.Parameters.Add("@MatchScoreLocked", OleDb.OleDbType.Boolean, 1, "MatchScoreLocked")

        parameter = command.Parameters.Add("@MatchNumber", OleDb.OleDbType.Integer, 3, "MatchNumber")
        parameter.SourceVersion = DataRowVersion.Current
        dataAdapter.UpdateCommand = command

        'Update Tbl_MatchData in database
        dataAdapter.AcceptChangesDuringUpdate = False

        Dim returnvalue As Integer = dataAdapter.Update(dt)


        ‘****************************************
        'Generate the second UpdateCommand for the balance of data to tbl_MatchData
        updateSQL = "UPDATE tbl_MatchData SET " & _
        "RWrestlerID = @RWrestlerID, " & _
        "RWeight = @RWeight, " & _
        "RTD1 = @RTD1, " & _
        .
        .
        .
        "GRideoutpts = @GRideoutpts, " & _
        "RRideoutpts = @RRideoutpts WHERE MatchNumber = @MatchNumber"

        command = New OleDb.OleDbCommand(updateSQL, connection)

        command.Parameters.Add("@RWrestlerID", OleDb.OleDbType.VarChar, 200, "RWrestlerID")
        command.Parameters.Add("@RWeight", OleDb.OleDbType.SmallInt, 5, "RWeight")
        command.Parameters.Add("@RTD1", OleDb.OleDbType.Integer, 3, "RTD1")
        .
        .
        .  
        command.Parameters.Add("@GRideoutpts", OleDb.OleDbType.VarChar, 10, "GRideoutpts")
        command.Parameters.Add("@RRideoutpts", OleDb.OleDbType.VarChar, 10, "RRideoutpts")

        parameter = command.Parameters.Add("@MatchNumber", OleDb.OleDbType.Integer, 3, "MatchNumber")

        parameter.SourceVersion = DataRowVersion.Current
        dataAdapter.UpdateCommand = command

        'Update Tbl_RedMatchData in database
        dataAdapter.AcceptChangesDuringUpdate = False

        returnvalue = dataAdapter.Update(dt)

        dt.AcceptChanges()
        connection.Close()
        dt.Dispose()
        Console.WriteLine("Update command exected")

[jmcilhinney]

Why are you using a DataAdapter at all? You've already got a TableAdapter so just use that. If the InsertCommand, Update Command and DeleteCommand are not automatically generated or are not exactly as you want then just edit them in the DataSet designer.

[MechEng]

I am connected to a ACCESS database and the table I am trying to update has ~170 columns. The command builder UPDATE command will not handle more than 99 columns of data at a time. To bypass this I generated the DataAdapter using the UPDATE and INSERT commands to update the Database table.

Is this wrong…Is there a better way? …because this sure has been a tough row for me to hoe (pun intended). Any help is most appreciated.

[jmcilhinney]

I didn't say anything about using a CommandBuilder. You're using a typed DataSet so you've already got a TableAdapter for each DataTable. You should be using that to Fill and Update your tables. If you need to edit them manually you can. That's what the DataSet designer is for.

[MechEng]

Ok, you know something that I don’t. All the reading (and messing around with the Data Designer) I have done indicates that there is a 99 column limit to the TableAdapter Update command. With regards to manually editing the UPDATE command, I can not see how that will help… all the information is in the UPDATE command and appears correct, but it will not work with more than 99 columns... Unless there is something really simple that I am missing. I would post the UPDATE command, but it exceeds the 10,000 character limit of this forum.

There are plenty of articles around (including in this forum) that indicate that it can not be done. Can you point me to an article or example that shows how to break this barrier?

Thanks

[jmcilhinney]

It seems more likely now that you know something that I don't. I wasn't aware of that 99 column limit. I thought you were saying it just applied to CommandBuilders. I'm not really sure why they would have applied a limit like that to DataAdapters specifically but if that's the way it is then that's the way it is. I'll do a bit more research myself to either confirm or deny that situation.

Assuming it is the case, then I guess an INSERT followed by an UPDATE may well be the only way to go. I think your problem likely coes down to RowState values. When you add a DataRow to a DataTable its RowState is set to Added. When you call Update on a DataAdapter it will first find all the rows whose RowState is Deleted and execute the DeleteCommand on them. It will then find all the rows whose RowState is Added and execute the InsertCommand on them. Finally it will find all the rows whose RowState is Modified and execute the UpdateCommand on them.

When you add a DataRow to a DataTable its RowState is Added. If you call Update and pass that DataTable the rowe will be inserted into the database. If you don't call AcceptChanges then the RowState remains Added. If you call Update again the row will be inserted again.

If you want to insert the row and then update you have to call Update once, then call AcceptChanges, then call SetModified on the row, then call Update again. SetModified sets the RowState to modified, so the row is ready to be updated. You have to call AcceptChanges first though because the RowState must be Unchanged before you call SetModified.

[MechEng]

You were right on target! The INSERT command was running first and having all the fun. I move a few lines of code around so that the UPDATE command would run each time a new row was inserted and then called AcceptChanges. From that point forward any changes made to the DataTable sets the Rowstate to Modified and the changes are captured. Seams to work fine- I need to do a lot more testing though before I call victory over this battle, but I feel the war has been advanced.

Thanks for taking the time- I will keep this thread open for just a bit incase you run across a workaround while doing your “confirm or deny” search. I am very interested in hearing the outcome of that.

Cheers