• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 366
  • Last Modified:

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



0
billymcqueary
Asked:
billymcqueary
  • 5
  • 4
2 Solutions
 
ZeonFlashCommented:
       While dtack.Rows(0)("VARLOCK") = "1"
            daack.Fill(dtack)
        End While

Are you sure you want to do it that way though?  You're really going to be thrashing your database by doing this...
0
 
billymcquearyAuthor Commented:
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!
0
 
ctm5Commented:
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

0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
billymcquearyAuthor Commented:
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
0
 
ctm5Commented:
Yes, you need to run the select statement again.

Dim myLock As String = "1"

While myLock = "1"

       Dim dtack As New DataTable
        Dim daack As New SqlClient.SqlDataAdapter("SELECT * FROM TestTable", ackconn)
        daack.Fill(dtack)
        myLock = dtack.Rows(0)("VARLOCK")
        Thread.Sleep(500)

End While

ctm5

0
 
billymcquearyAuthor Commented:
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?

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

ctm5
0
 
billymcquearyAuthor Commented:
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
0
 
billymcquearyAuthor Commented:
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.
0
 
ctm5Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now