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