asked on

Run same select statement inside of loop

I am trying to use a database value as a lock. I am having the a select statement run and see if the lock flag is set. I want it to keep querying the database until it is unlocked. I get a compile error: "Variable 'dtack' hides a variable in an enclosing block". The same for 'daack'. Is there a cleaner way to accomplish this?

************************

Dim dtack As New DataTable
Dim daack As New SqlClient.SqlDataAdapter("SELECT * FROM TestTable", ackconn)
daack.Fill(dtack)

While dtack.Rows(0)("VARLOCK") = "1"

Dim dtack As New DataTable
Dim daack As New SqlClient.SqlDataAdapter("SELECT * FROM TestTable", ackconn)
daack.Fill(dtack)

End While

SOLUTION

ZeonFlash

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

billymcqueary

ASKER

I have to assign a unique, sequential acknowledgement number for every order we recieve through our online ordering system. This variable VARLOCK is set to 1 while someone is getting and incrementing the number. That loop should only run for a second or two at the most, and that is in rare cases. It will only happen when two orders are sent at almost exactly the same time. Do you think there might be a better way?

Thanks!

ctm5

Since you anticipate that it will only be a second or two until the lock is cleared, you could also add a brief pause inside the loop -- might save a trip or two to the database:

While dtack.Rows(0)("VARLOCK") = "1"
Thread.Sleep(500) <---half a second
daack.Fill(dtack)
End While

ctm5

billymcqueary

ASKER

It does not seem like running the

daack.Fill(dtack)

command actually polls the database again. I tried manually changing the values in the database while the loop was running, and it did not seem to affect it. Do I need to do something different to actually get info from the database every time? It's like it reloads dtack with the same data.

Thanks

ASKER CERTIFIED SOLUTION

ctm5

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

billymcqueary

ASKER

That puts me back where I started with the error: "Variable 'dtack' hides a variable in an enclosing block". That's how I first attempted to do it. Any other guesses?

ctm5

Yes, I meant to include that you must remove the Dim statements outside of the loop.

ctm5

billymcqueary

ASKER

Is there any way to run a Select Statement without having to use DIM to create a new variable every time? Can I just use the same data adapter again with a different statement. How do I refresh or execute the command for the data adapter without making a new one?

Thanks

billymcqueary

ASKER

OK, removing the DIM statements from outside of the While loop fixed it. Thank you. I'm not sure why you can't do that, but at least I know now.

ctm5

You can remove those Dim statements because you have them inside the loop. It's all a matter of scope. The scope for the loop is just the loop. The scope for the code you originally had was that entire block of code (including the loop). A variable name can be used only once per scope.

Dim myFirst As Something
Dim mySecond As Something
Dim myThird As Something

While This
Dim myFourth As Something <-- OK
Dim myFirst As Something <-- NOT OK --> myFirst is still in scope
mySecond = myThird <-- OK --> these two are still in scope

End While

myFourth = my Fourth * 2 <-- NOT OK --> myFourth is out of scope
myThird = mySecond *2 <-- OK --> these two are still in scope

ctm5