vb.net, BackGroundWorker, DataGridView, ToolstripProgressBar

I'm using the following website as a guide to fill a datagridview using backgroundworker, then show the progress in the ToolStripProgressBar1.ProgressBar.  
http://www.vbdotnetforums.com/windows-forms/25999-load-datagridview-via-backgroundworker-progressbar-feedback.html

I haven't gotten past this error: Must declare the scalar variable "@Variable".  I'm sure it's not my last error, (I haven't even gotten to the progressbar piece yet), but I need to get past this problem in order to continue. Perhaps I am not passing in the Variable correctly?  
Public Class frmTest
    Private WithEvents BGW As BackgroundWorker = New BackgroundWorker
 
    Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
        btnCancel.Enabled = True
        btnSubmit.Enabled = False
        BGW.WorkerSupportsCancellation = True
        BGW.RunWorkerAsync(Variable)
    End Sub
 
    Private Sub BGW_DoWork(ByVal sender As Object, ByVal e As DoWorkEventArgs) Handles BGW.DoWork
        Dim Variable = e.Argument.ToString
        Dim connection As New SqlConnection(connectionStr)
        Dim selectStatement As String _
               = "Select * From dbTable Where Column=@Variable"
        Dim selectCommand As New SqlCommand(selectStatement, connection)
        selectCommand.Parameters.AddWithValue("@Variable", Variable)
        connection.Open()
        Dim myDataAdapter As New SqlDataAdapter(selectStatement, connectionStr)
        Dim ds As New DataSet
        myDataAdapter.Fill(ds)
        e.Result = ds
    End Sub
 
    Private Sub BGW_RunWorkerCompleted(ByVal sender As Object, ByVal e As RunWorkerCompletedEventArgs) Handles BGW.RunWorkerCompleted
        btnCancel.Enabled = False
        btnSubmit.Enabled = True
        DataGridView1.DataSource = e.Result
        With DataGridView1
            '....code here
        End With
    End Sub
 
    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        btnCancel.Enabled = False
        btnSubmit.Enabled = True
        BGW.CancelAsync()
    End Sub

Open in new window

bgernonAsked:
Who is Participating?
 
bgernonAuthor Commented:
I ditched the dataset and went back to using a DataTable.  It works great now!  

Private Sub BGW_DoWork(ByVal sender As Object, ByVal e As DoWorkEventArgs) Handles BGW.DoWork
        Dim Variable = e.Argument.ToString
        Dim table As New DataTable
        Dim connection As New SqlConnection(connectionStr)
        Dim selectStatement As String _
               = "Select * From dbTable Where Column= '" & Variable & '" "
        Dim selectCommand As New SqlCommand(selectStatement, connection)        
       connection.Open()
        Dim myDataReader As SqlDataReader = selectCommand.ExecuteReader        
        table.Load(reader)
        connection.Close
        e.Result = table
    End Sub

Just need to get the ProgressBar piece running.
0
 
Wayne Taylor (webtubbs)Commented:
What are you trying to pass to the BackGroundWorker? "Variable" also needs to be declared in the btnSubmit_Click routine.

Wayne
0
 
DabasCommented:
I agree with Wayne

To start with, and so you understand his suggestion, just try to add this line before line 5

I also strongly suggest you use

Option Explicit On

as the first statement of every code file to avoid this type of errors
Dim Variable as Integer = 5

Open in new window

0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
bgernonAuthor Commented:
Oops!  I did a copy, paste and missed the line that declares the variable.  It is in the btnSubmit_Click routine and it is before line 5.  It is a value entered by the user.

Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
        Dim Variable As String = txtVariable.Text
        btnCancel.Enabled = True
        btnSubmit.Enabled = False
        BGW.WorkerSupportsCancellation = True
        BGW.RunWorkerAsync(Variable)
    End Sub


0
 
DabasCommented:
Now that you are passing the variable, do you still get the same error?
0
 
bgernonAuthor Commented:
I was passing the variable.  I just neglected to add it to the code snippet I submitted.  Yes, I still get the error.
0
 
DabasCommented:
I suggest you replace rows 14 to 17 in your code with this snippet

I am assuming that the Column named Column is of type string, otherwise remove the single quotes.

Dabas
Dim selectStatement As String _
               = "Select * From dbTable Where Column='" + Variable + "'"
Dim selectCommand As New SqlCommand(selectStatement, connection)
'         selectCommand.Parameters.AddWithValue("@Variable", Variable) 'Lets try without the parameter
        connection.Open()

Open in new window

0
 
bgernonAuthor Commented:
Tried that earlier.  My datagrid never loaded.  I must have gotten to BGW_RunWorkerCompleted because my Cancel button was disabled (btnCancel.Enabled = False).  I have a feeling, that Variable has no value, so it is returning an empty dataset.
0
 
DabasCommented:
Why don't you set a breakpoint at your line 8 and at your line 12 and verify that is the case?
0
 
DabasCommented:
Also, since you now do not have a 'scalar @Variable' declared, what is the complete error message now?
0
 
bgernonAuthor Commented:
I had done that with @Variable, had not thought to do that when using Variable the way you suggested.  Yes, it now has a value and my dataset has records.  However, I now have a new error:

!TargetInvocationException was unhandled
Exception has been thrown by the target of invocation.

0
 
DabasCommented:
Please help us help you by providing full information.


Which line caused the error?
Please post the full stacktrace
0
 
bgernonAuthor Commented:
The error appeared when I ran the code in debug.  I believe the line causing the problem is
DataGridView1.DataSource = e.Result

When I step through the code, F10 on that line causes the error to appear.
0
 
DabasCommented:
I am glad it is working.

I suggest you close this question, and if you need help with the ProgressBar, open a related question to give other experts a chance to help you out

Dabas
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.

All Courses

From novice to tech pro — start learning today.