?
Solved

Run same select statement inside of loop

Posted on 2006-06-02
10
Medium Priority
?
363 Views
Last Modified: 2010-04-23
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
Comment
Question by:billymcqueary
  • 5
  • 4
10 Comments
 
LVL 17

Assisted Solution

by:ZeonFlash
ZeonFlash earned 200 total points
ID: 16818051
       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
 

Author Comment

by:billymcqueary
ID: 16818177
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
 
LVL 6

Expert Comment

by:ctm5
ID: 16818255
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:billymcqueary
ID: 16818331
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
 
LVL 6

Accepted Solution

by:
ctm5 earned 1800 total points
ID: 16818419
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
 

Author Comment

by:billymcqueary
ID: 16818631
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
 
LVL 6

Expert Comment

by:ctm5
ID: 16818658
Yes, I meant to include that you must remove the Dim statements outside of the loop.

ctm5
0
 

Author Comment

by:billymcqueary
ID: 16818688
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
 

Author Comment

by:billymcqueary
ID: 16818764
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
 
LVL 6

Expert Comment

by:ctm5
ID: 16819201
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question