Execute Query taking priority over animation

05-31-2006, 10:10 AM

Gotch_ur_number Gotch_ur_number is offline

Newcomer

Join Date: Jan 2005

Posts: 23

Execute Query taking priority over animation

Hi,

I've have a VB application that copies files amongst other things.

When the button is clicked, it triggers a stored procedure that copies these files. For some reason, an embedded animation in the form does not play until after the stored procedure is triggered, despite it being placed in the code before the cmd.ExecuteNonQuery() command:

Code:

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

     pctAni.Visible = True '--> The 'copying files animation

    'The 'copying files' animation does not kick in until after the Execute (), specifically cmd.ExecuteNonQuery(), has completed.

    Execute()

    End Sub

    Private Sub Execute()

    Dim cnn As New SqlClient.SqlConnection
    Dim cmd As New SqlClient.SqlCommand

    My.Settings("cnnString") = "Data Source=vw04crmtest;Initial Catalog=master;User ID=sa"

    cnn.ConnectionString = My.Settings("cnnString")
        With cmd
            .CommandType = CommandType.StoredProcedure
            .CommandText = "sp_backup_processes"
            .Connection = cnn
            .CommandTimeout = 300

    End With

    cnn.Open()
    cmd.ExecuteNonQuery()
    cnn.Close()


    MsgBox("Processes Backed Up")

    'pctAni.Visible = False  - Commented out for testing
  

    End Sub

Any ideas why the Execute command is taking priority over the animation, despite the animation be placed before it?

Thanks

05-31-2006, 10:21 AM

loquin

Google Hound

Retired Moderator
* Guru *

Join Date: Nov 2001

Location: Arizona, USA

Posts: 12,378

BY default, ADO database activities are synchronous in nature. This means that when the ADO command is executed, processing in the current thread halts until control is returned. The synchronous model is easier to program, but, as you've experienced, it does have drawbacks.

Now, in classic VB, you would need to declare the objects "WithEvents" in order to use them asynchronously.

For dotNet, ref the following DevX article.

__________________
Lou
"I have my standards. They may be low, but I have them!" ~ Bette Middler
"It's a book about a Spanish guy called Manual. You should read it." ~ Dilbert
"To understand recursion, you must first understand recursion." ~ unknown

Last edited by loquin; 05-31-2006 at 10:32 AM.

05-31-2006, 02:01 PM

Gotch_ur_number Gotch_ur_number is offline

Newcomer

Join Date: Jan 2005

Posts: 23

Quote:

Originally Posted by loquin

Thank you, Lou. Now I know.

06-01-2006, 05:27 AM

Gotch_ur_number Gotch_ur_number is offline

Newcomer

Join Date: Jan 2005

Posts: 23

Back again.

The code has now been modified to be asynchronous. But I am still having the same problem of the animation not playing till after. This is the first time I'm using this method, the problem may not be difficult to spot.

Code:

Dim arCopy As IAsyncResult 'Using the asynchronous method

        cnn.ConnectionString = My.Settings("cnnString")

    My.Settings("cnnString") = "Data Source=vw04crmtest;Asynchronous Processing=true;Initial Catalog=master;User ID=sa"


        With cmd
            .CommandType = CommandType.StoredProcedure
            .CommandText = "sp_backup_processes"
            .Connection = cnn
            .CommandTimeout = 300
        End With

        cnn.Open()

        arCopy = cmd.BeginExecuteNonQuery() 'Using begin and end

        While Not arCopy.IsCompleted
            pctAnimation.Visible = True 'THE ANIMATION
        End While

        cmd.EndExecuteNonQuery(arCopy)

        cnn.Close()

        MsgBox("Processes Backed Up")

        pctAni.Visible = False



    End Sub

06-01-2006, 08:01 AM

wayneph

Web Junkie

Retired Moderator
* Expert *

Join Date: Apr 2004

Location: D/FW, Texas, USA

Posts: 8,393

Why don't you start the animation before you call the backup procedure?

__________________
-- wayne, MSSM Retired
> SELECT * FROM users WHERE clue > 0
0 rows returned

06-01-2006, 08:04 AM

Gotch_ur_number Gotch_ur_number is offline

Newcomer

Join Date: Jan 2005

Posts: 23

Quote:

Originally Posted by wayneph

Why don't you start the animation before you call the backup procedure?

I did originally, (see second line in code of first post). It doesn't matter where it is in the button click event, the backup procedure keeps taking priority.

06-01-2006, 08:14 AM

wayneph

Web Junkie

Retired Moderator
* Expert *

Join Date: Apr 2004

Location: D/FW, Texas, USA

Posts: 8,393

OK, completely missed that.

How do you have your animation set up? Is it just an animated gif in a picture box, or do you have code somewhere that is supposed to run and update images?

__________________
-- wayne, MSSM Retired
> SELECT * FROM users WHERE clue > 0
0 rows returned

06-01-2006, 08:28 AM

Gotch_ur_number Gotch_ur_number is offline

Newcomer

Join Date: Jan 2005

Posts: 23

Quote:

Originally Posted by wayneph

OK, completely missed that.

How do you have your animation set up? Is it just an animated gif in a picture box, or do you have code somewhere that is supposed to run and update images?

It's a simple 'file copying' animated gif in a picture box. By default it's invisible. When the backup button is clicked...

animation.visable = true

Back everything up then

animation.visible = false.

06-01-2006, 01:03 PM

Gotch_ur_number Gotch_ur_number is offline

Newcomer

Join Date: Jan 2005

Posts: 23

I am copying by triggering a stored procedure on sql server that copies files using cmdshell (as well as other things to the database). I think the problem may have somehing to do with using this method, as the I am able to change the form colour while still waiting for a response from the database.

#10

06-01-2006, 04:17 PM

wayneph

Web Junkie

Retired Moderator
* Expert *

Join Date: Apr 2004

Location: D/FW, Texas, USA

Posts: 8,393

If you're able to change the color, then I'm wondering if the problem is somewhere else. I do mostly WebForms, so I'm a little out of place here.

Does the picture box have a property that you need to set to allowed animations? Is it displaying, but just not moving?

Is the gif set up with a finite number of loops? Not all .gifs are set up to repeat for ever. So it repeats 3 or 4 times while it's hidden. You start the process, and then it displays static...

Have you tried calling Me.Invalidate() after you set the .gif to visible? That will force the Form to redraw itself.

Just a couple ideas to try. Not sure what's going on.

I doubt it has anything to do with the way you're copying files. Although have you tried doing it using the System.IO namespace instead of cmdshell from SQL Server? It seems like you should be able to do this without SQL being involved at all.

__________________
-- wayne, MSSM Retired
> SELECT * FROM users WHERE clue > 0
0 rows returned

#11

06-02-2006, 08:14 AM

Gotch_ur_number Gotch_ur_number is offline

Newcomer

Join Date: Jan 2005

Posts: 23

Hi Wayne, I tried all your suggestions above but with no luck. I'm using SQL Server because (I'm a dba!) and after copying the files, the stored procedures triggers sql server jobs such as transactional log backups.

But...

I have almost got it working using the background worker componant...

When the button is clicked...

Code:

       Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        animation.visible = true ----> This works! and animates while copying the directory

        background = New BackgroundWorker

        background.RunWorkerAsync()

     End Sub

    Private Sub background_DoWork(ByVal sender As Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles background.DoWork

        cnn.ConnectionString= "Data Source=(local);Asynchronous Processing=true;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=misc;User ID=sa"
     
        With cmd
            .CommandType = CommandType.StoredProcedure
            .CommandText = "usp_backup_processes"
            .Connection = cnn
            .CommandTimeout = 300
        End With

        cnn.Open()
        cmd.ExecuteNonQuery()
        cnn.Close()

        animation.Visible = False 

        MsgBox("Processes Backed Up")


    End Sub


    Private Sub BackgroundWorker1_RunWorkerCompleted(ByVal sender As System.Object, ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles BackgroundWorker1.RunWorkerCompleted

        animation.Visible = False

    End Sub

With the RunWorkerCompleted sub, the animations visibility should be reset to false, but it isn't. Any ideas?

Thanks, again.

Last edited by Gotch_ur_number; 06-02-2006 at 08:30 AM.

#12

06-02-2006, 08:59 AM

Gotch_ur_number Gotch_ur_number is offline

Newcomer

Join Date: Jan 2005

Posts: 23

Finaly working.

The problem was calling 'Background' instead of backgroundworker1. Tired eyes.

The final code for a simple asynchronous thread is as follows:

Code:

Imports System.ComponentModel

Public Class frmMain



    Dim cnn As New SqlClient.SqlConnection
    Dim cmd As New SqlClient.SqlCommand


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Me.Cursor = Cursors.WaitCursor
        Button1.Enabled = False
        lblWait.Visible = True
        animation.Visible = True



        BackgroundWorker1.RunWorkerAsync()

    End Sub

    Private Sub BackgroundWorker1_RunWorkerCompleted(ByVal sender As System.Object, ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles BackgroundWorker1.RunWorkerCompleted
        Me.lblWait.Visible = False
        Me.animation.Visible = False
        Me.Button1.Enabled = True
        Me.Cursor = Cursors.Default
    End Sub


    Private Sub BackgroundWorker1_DoWork(ByVal sender As System.Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork
        My.Settings("cnnString") = "Data Source=(local);Asynchronous Processing=true;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=misc;User ID=sa"
        cnn.ConnectionString = My.Settings("cnnString")

        With cmd
            .CommandType = CommandType.StoredProcedure
            .CommandText = "usp_backup_processes"
            .Connection = cnn
            .CommandTimeout = 300
        End With

        cnn.Open()
        cmd.ExecuteNonQuery()
        cnn.Close()

        MsgBox("Processes Backed Up")
    End Sub
End Class