Help With Progress bar

[Mike Bishop]

I have the following code that works fine, it opens and excel file and uploads it into SQL.
these excel files can have over 120 thousand records.

I would like to put a progress bar on the upload so the end user knows how long the upload will take. The progree bar its self isn't the issue.

The issue is putting a counter in my code so i know what record is being done.

if anyone can help that would be great, thank you in advance
vb

Dim strSql As String = ""
        strSql = "INSERT INTO data (Date, Time, Gauge, Product, Length, M1, M2, M3, M4, M5) VALUES (@Date, @Time, @Gauge, @Product, @Length, @M1, @M2, @M3, @M4, @M5)"

        'SQL Server Connection String   
        Dim cn As New SqlClient.SqlConnection
        cn.ConnectionString = connectionstring '"Data Source=MyDB;Initial Catalog=DB123;Integrated Security=True"

        'Dim cmd As New SqlClient.SqlCommand
        ' cmd.Connection = cn
        'cmd.CommandType = CommandType.Text
        'cmd.CommandText = strSql

        cn.Open()

        'Connection String to Excel Workbook   
        Dim excelConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ExcelFileV & ";Extended Properties=""Excel 12.0;HDR=YES;"""

        ' Create Connection to Excel Workbook   
        Using connection As New System.Data.OleDb.OleDbConnection(excelConnectionString)

            'List columns you need from the Excel file   
            Dim command As New System.Data.OleDb.OleDbCommand("Select [Date],[Time],[Gauge],[Product],[Length],[M1],[M2],[M3],[M4],[M5]  FROM [" & ExcelSheetV & "]", connection)
            connection.Open()

            ' Create DbDataReader to Data Worksheet   
            Using dr As System.Data.OleDb.OleDbDataReader = command.ExecuteReader()

                If dr.HasRows() Then
                    While dr.Read()
                        Dim cmd As New SqlClient.SqlCommand
                        cmd.Connection = cn
                        cmd.CommandType = CommandType.Text
                        cmd.CommandText = strSql

                        cmd.Parameters.Add("@Date", SqlDbType.VarChar).Value = Convert.ToString(dr.Item("Date"))
                        cmd.Parameters.Add("@Time", SqlDbType.DateTime).Value = (dr.Item("Time"))
                        cmd.Parameters.Add("@Gauge", SqlDbType.VarChar).Value = Convert.ToString(dr.Item("Gauge"))
                        cmd.Parameters.Add("@Product", SqlDbType.VarChar).Value = Convert.ToString(dr.Item("Product"))
                        cmd.Parameters.Add("@Length", SqlDbType.VarChar).Value = Convert.ToString(dr.Item("Length"))
                        cmd.Parameters.Add("@M1", SqlDbType.Decimal).Value = (dr.Item("M1"))
                        cmd.Parameters.Add("@M2", SqlDbType.Decimal).Value = (dr.Item("M2"))
                        cmd.Parameters.Add("@M3", SqlDbType.Decimal).Value = (dr.Item("M3"))
                        cmd.Parameters.Add("@M4", SqlDbType.Decimal).Value = (dr.Item("M4"))
                        cmd.Parameters.Add("@M5", SqlDbType.Decimal).Value = (dr.Item("M5"))
                        cmd.ExecuteScalar()
                    End While
                End If

            End Using

        End Using

        cn.Close()
        cn = Nothing

[adam_k]

The counter part is easy, declare a var and after the cmd.ExecuteScalar increment your var by 1 (record processed).

[Mike Bishop]

cheers thanks for your help, wonder if you can also help with another issue i am having. when running the code about my application looks like it has stopped responding but it hasn't its just running in the back ground. Is there anything i can do to make my application come back to life so that i can show my progress bar?

[Mike Bishop]

ps i have added the following code to my application

cmd.Parameters.Add("@M1", SqlDbType.Decimal).Value = (dr.Item("M1"))
                        cmd.Parameters.Add("@M2", SqlDbType.Decimal).Value = (dr.Item("M2"))
                        cmd.Parameters.Add("@M3", SqlDbType.Decimal).Value = (dr.Item("M3"))
                        cmd.Parameters.Add("@M4", SqlDbType.Decimal).Value = (dr.Item("M4"))
                        cmd.Parameters.Add("@M5", SqlDbType.Decimal).Value = (dr.Item("M5"))
                        cmd.ExecuteScalar()
                        Me.LBrecCount.Text = _sqlcount + 1



                    End While
                End If

            End Using

        End Using

but due to the above post i cant see my label to see the record number