Last post Feb 23, 2012 03:23 AM by hans_v
Feb 22, 2012 10:46 AM|iveseenya|LINK
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.
Feb 22, 2012 11:38 AM|bbcompent1|LINK
I'm suspecting your player table looks like this (avoid spaces in table names) - I would redesign the DB like this:
ID (autonum primary key) | Player | final_table_appear | won | points
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.
Feb 22, 2012 12:12 PM|iveseenya|LINK
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.
Feb 22, 2012 12:15 PM|bbcompent1|LINK
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.
Feb 22, 2012 12:18 PM|iveseenya|LINK
Sorry but I dont understand what you mean. With the JOIN being present I cant generate stored procedures.
Feb 22, 2012 12:22 PM|bbcompent1|LINK
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;
Feb 22, 2012 12:23 PM|bbcompent1|LINK
The other option is to upgrade your access db to sql server using the Upsizing Wizard. Then you can take advantage of Stored Procs.
Feb 23, 2012 03:23 AM|hans_v|LINK
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")
Using trans = conn.BeginTransaction
Using cmd As New OleDbCommand("INSERT ......", conn, trans)
Using cmd As New OleDbCommand("UPDATE ......", conn, trans)
Using cmd As New OleDbCommand("DELETE ......", conn, trans)
Catch ex As OleDbException
'an error occured when executing a command, so rollback commands that where already executed
Catch ex As OleDbException
'An error occured when opening the connection