Question how to retrieve row, update and lock from another user?

[servox]

Hi all

I have setup a database with 15 bingo numbers per row (see image), I'm trying to search for the first ticket number which is in a column is called number and then retrieve bingo numbers and update the printed column from no to yes to stop it being read again.

the problem that I'm having is if there is more than one computer connected to the database, some times the same ticket number is read on both machines and prints the same numbers and ticket number. Which I don't want to happen. works fine if only one

I have tried us the XLOCK, ROWLOCK, UPDLOCK, HOLDLOCK without any luck

so hopefully someone could suggest a better way of doing this

here my code


OpenSQLdatabase() '---this opens the database


Dim myCommand As SqlCommand
Dim dr As SqlDataReader

Try
myCommand = New SqlCommand("SELECT * FROM Bingo WHERE PRINTED='no'", conn) '--- this will get the first ticket number (ORDER DESC for last number)
'myCommand = New SqlCommand("SELECT * FROM Bingo WITH (UPDLOCK, HOLDLOCK) WHERE PRINTED='no'", conn)
'executing the command and assigning it to connection
dr = myCommand.ExecuteReader(CommandBehavior.SingleRow) '-- this has been changed for single row read
'dr = myCommand.ExecuteReader() '-- this will search all rows

While dr.Read()
'reading from the datareader

BingoNumbers(0) = dr(0)
BingoNumbers(1) = dr(1)
BingoNumbers(2) = dr(2)
BingoNumbers(3) = dr(3)
BingoNumbers(4) = dr(4)
BingoNumbers(5) = dr(5)
BingoNumbers(6) = dr(6)
BingoNumbers(7) = dr(7)
BingoNumbers(8) = dr(8)
BingoNumbers(9) = dr(9)
BingoNumbers(10) = dr(10)
BingoNumbers(11) = dr(11)
BingoNumbers(12) = dr(12)
BingoNumbers(13) = dr(13)
BingoNumbers(14) = dr(14)


intTicketNumer = dr(15) '-- used for printing ticket number

End While

dr.Close()
conn.Close()


'--- now update as printed to stop it being read again
OpenSQLdatabase()
sql = "UPDATE Bingo SET Printed='yes' WHERE number=@number"
cmd = New SqlCommand(sql, conn)
cmd.Parameters.AddWithValue("@number", intTicketNumer)
ExecuteNonQueryMethod()



PrintBingoTicket()




thanks in advance

 

[servox]

I have now found a different way of doing this, so this is how I did it just incase someone else is having a similar problem


step 1 - update the amount of tickets needed. this code will update 10 rows for 10 tickets
change the UPDATE TOP(2) for two rows

OpenSQLdatabase()

sql = "UPDATE TOP (10) bingo SET printed='waiting', SystemID=@SystemID WHERE printed ='no'"
cmd = New SqlCommand(sql, conn)
cmd.Parameters.AddWithValue("@SystemID", Environment.MachineName.ToString)
ExecuteNonQueryMethod() '---executes an SQL statement on the database = see PUBLIC subs


step 2 - now search for the updated tickets using waiting and systemID
this will only find the tickets from this computer that updated the rows

OpenSQLdatabase()

Dim myCommand As SqlCommand
Dim dr As SqlDataReader

Try

myCommand = New SqlCommand("SELECT * FROM Bingo WHERE printed='waiting' and SystemID=@SystemID", conn)
myCommand.Parameters.AddWithValue("@SystemID", Environment.MachineName.ToString)


'executing the command and assigning it to connection
dr = myCommand.ExecuteReader()

While dr.Read()
'reading from the datareader

testArrayList.Add(dr(0))
testArrayList.Add(dr(1))
testArrayList.Add(dr(2))
testArrayList.Add(dr(3))
testArrayList.Add(dr(4))
testArrayList.Add(dr(5))
testArrayList.Add(dr(6))
testArrayList.Add(dr(7))
testArrayList.Add(dr(8))
testArrayList.Add(dr(9))
testArrayList.Add(dr(10))
testArrayList.Add(dr(11))
testArrayList.Add(dr(12))
testArrayList.Add(dr(13))
testArrayList.Add(dr(14))

intTicketNumber = dr(15)

testArrayList.Sort() '--- sorts numbers from low to high

PrintBingoTicket()

testArrayList.Clear() '--- clears the arraylist

End While


dr.Close()
conn.Close()


Catch e As Exception
MessageBox.Show(ex.ToString)
End Try


Step 3 - update the tickets from waiting to yes.

OpenSQLdatabase()
sql = "UPDATE bingo SET printed='yes' WHERE printed='waiting' and SystemID=@SystemID"
cmd = New SqlCommand(sql, conn)
cmd.Parameters.AddWithValue("@SystemID", Environment.MachineName.ToString)
ExecuteNonQueryMethod() '---executes an SQL statement on the database = see PUBLIC subs



so this now stops any duplicate tickets being printed.

If anyone has a better method then please let me know