locked
Help with multiple tables and edit, insert and delete RRS feed

  • Question

  • User7709524 posted

    Hello,

    I have one database with two tables.

    One table has: player, final table appearances, won and points

    The other table has: ID and position.

    I am doing a poker league so each week the position of the player changes based on the number of points they have. Could anyone advise me the best way to do this as I am finding it hard to get my head around how I am going to do it. I also need to be able to use the edit and delete functions of the table so it can be updated weekly.


    Thanks,

    Paul.

    Wednesday, February 22, 2012 10:46 AM

Answers

  • User-1199946673 posted

    I think you might be able to stack the update/delete commands and just separate them by semi-colon if you use the Access Data Source controls.

    Access doesn't support batched statements. What you can do is start a transaction, execute multiple commands and then commit the transaction (or rollback if an error occurs somewhere)...

            Using conn As New OleDbConnection("yourconnectionstring")
                Try
                    conn.Open()
                    Using trans = conn.BeginTransaction
                        Try
                            Using cmd As New OleDbCommand("INSERT ......", conn, trans)
                                cmd.Parameters.AddWithValue("param1", "somevalue")
                                cmd.ExecuteNonQuery()
                            End Using
                            Using cmd As New OleDbCommand("UPDATE ......", conn, trans)
                                cmd.Parameters.AddWithValue("param1", "somevalue")
                                cmd.ExecuteNonQuery()
                            End Using
                            Using cmd As New OleDbCommand("DELETE ......", conn, trans)
                                cmd.Parameters.AddWithValue("param1", "somevalue")
                                cmd.ExecuteNonQuery()
                            End Using
                            trans.Commit()
                        Catch ex As OleDbException
                            'an error occured when executing a command, so rollback commands that where already executed
                            trans.Rollback()
                        End Try
                    End Using
                Catch ex As OleDbException
                    'An error occured when opening the connection
                End Try
            End Using
    
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 23, 2012 3:23 AM

All replies

  • User-718146471 posted

    I'm suspecting your player table looks like this (avoid spaces in table names) - I would redesign the DB like this:

    Players:

    ID (autonum primary key) | Player | final_table_appear | won | points

    Ranking:

    RecID (autonum primary key) | PlayerID | Position

    Players.ID joined Ranking.PlayerID

    I doubt that your Players table will change weekly but then again maybe the  final_table_appear, won, points columns might change. What I would say is make use of stored procedures (providng you aren't using MS Access) and then you can update both tables in one shot. Then you simply call the stored procedure name from your web control.

    Wednesday, February 22, 2012 11:38 AM
  • User7709524 posted

    Thanks for the reply.

    Each the players table will update, obviously not the ID or the players name but the final table appear, won and points will do. I am also using MS Access at the moment so the stored procedures dont work with a JOIN present. What would you suggest I did to get around it? Not use MS Access? if so please could you give me some advice.


    Paul

    Wednesday, February 22, 2012 12:12 PM
  • User-718146471 posted

    I think you might be able to stack the update/delete commands and just separate them by semi-colon if you use the Access Data Source controls. A better option is using code behind queries to do the work.

    Wednesday, February 22, 2012 12:15 PM
  • User7709524 posted

    Sorry but I dont understand what you mean. With the JOIN being present I cant generate stored procedures.

    Wednesday, February 22, 2012 12:18 PM
  • User-718146471 posted

    I don't think that Access supports Stored procedures. So what you want to do is when updating the two tables, use two statements in one command like this:

    Update TABLE1 SET value1 = @value1, value2 = @value2, value3 = @value3; UPDATE TABLE2 SET value2 = @value2;
    Wednesday, February 22, 2012 12:22 PM
  • User-718146471 posted

    The other option is to upgrade your access db to sql server using the Upsizing Wizard. Then you can take advantage of Stored Procs.

    Wednesday, February 22, 2012 12:23 PM
  • User-1199946673 posted

    I think you might be able to stack the update/delete commands and just separate them by semi-colon if you use the Access Data Source controls.

    Access doesn't support batched statements. What you can do is start a transaction, execute multiple commands and then commit the transaction (or rollback if an error occurs somewhere)...

            Using conn As New OleDbConnection("yourconnectionstring")
                Try
                    conn.Open()
                    Using trans = conn.BeginTransaction
                        Try
                            Using cmd As New OleDbCommand("INSERT ......", conn, trans)
                                cmd.Parameters.AddWithValue("param1", "somevalue")
                                cmd.ExecuteNonQuery()
                            End Using
                            Using cmd As New OleDbCommand("UPDATE ......", conn, trans)
                                cmd.Parameters.AddWithValue("param1", "somevalue")
                                cmd.ExecuteNonQuery()
                            End Using
                            Using cmd As New OleDbCommand("DELETE ......", conn, trans)
                                cmd.Parameters.AddWithValue("param1", "somevalue")
                                cmd.ExecuteNonQuery()
                            End Using
                            trans.Commit()
                        Catch ex As OleDbException
                            'an error occured when executing a command, so rollback commands that where already executed
                            trans.Rollback()
                        End Try
                    End Using
                Catch ex As OleDbException
                    'An error occured when opening the connection
                End Try
            End Using
    
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 23, 2012 3:23 AM