Uploading Excel to SQL Server

[primagic]

I have the following code to upload an excel spreadsheet to sql table.

The code runs with no errors but the data is not populating the table.

Any ideas?

Thanks

Private Sub btnChooseFile_Click(sender As System.Object, e As System.EventArgs) Handles btnChooseFile.Click

        Dim myStream As Stream = Nothing
        Dim OpenFileDialog As New OpenFileDialog()

        OpenFileDialog.Title = "Please select a file"


        OpenFileDialog.InitialDirectory = "c:\"
        OpenFileDialog.Filter = "Excel 2003 files (*.xls)|*.xls|Excel Files (*.xlsx)|*.xlsx"
        OpenFileDialog.FilterIndex = 2
        OpenFileDialog.RestoreDirectory = True



        If OpenFileDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
            Try
                myStream = OpenFileDialog.OpenFile()
                If (myStream IsNot Nothing) Then

                    txtFilename.Text = OpenFileDialog.FileName
                    Dim strFilename As String
                    strFilename = OpenFileDialog.FileName


                    Dim excelConnectionString As String = String.Format("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strFilename & ";Extended Properties=""Excel 12.0;HDR=YES;IMEX=1;""")

                    Dim connection As New OleDbConnection()

                    connection.ConnectionString = excelConnectionString
                    Dim command As New OleDbCommand("select * from [Report Detail 1$]", connection)

                    connection.Open()

                    ' Create DbDataReader to Data Worksheet
                    Dim dr As DbDataReader = command.ExecuteReader()

                    ' SQL Server Connection String
                    Dim sqlConnectionString As String = "DATA SOURCE=??1;User ID=???;Password=???;Initial Catalog=?;Persist Security Info=false;"


                    ' Bulk Copy to SQL Server
                    Dim bulkInsert As New SqlBulkCopy(sqlConnectionString)
                    bulkInsert.DestinationTableName = "Payments"
                    bulkInsert.WriteToServer(dr)

                    MsgBox("Success")



                End If
            Catch Ex As Exception
                MessageBox.Show("Cannot read file from disk. Original error: " & Ex.Message)
            Finally
                ' Check this again, since we need to make sure we didn't throw an exception on open. 
                If (myStream IsNot Nothing) Then
                    myStream.Close()
                End If
            End Try
        End If

    End Sub

 

[keayfjgn]

Try the windows forums this isn't asp.net

 

[primagic]

Ok.

Well it is asp.net windows forms coding. So depends how you want to look at it?

 

[jbenson001]

Well it is asp.net windows forms coding

I don't understand what that means.

ASP.NET is what is used to create web pages, using the .NET framework.
VB or C#.NET is what is used to create a windows app, service or console app. These languages also use the .NET framework.

Although both use the .NET frame work to create what they need to create, there are many differences between them (web vs windows) development.
Although your code here would be similar in a website or web app, your code is definitely for a windows app which is the point that the poster was trying to make.

 

[keayfjgn]

Well it is asp.net windows forms coding. So depends how you want to look at it?

I thought you were writing a windows form application. If this really is an asp.net application it won't work when you deploy it.

 

[gk53]

did you check datareader after
Dim dr As DbDataReader = command.ExecuteReader()
line?
and make sure file in correct format