×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Uploading Excel to SQL Server

Uploading Excel to SQL Server

Uploading Excel to SQL Server

(OP)
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

CODE

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 

RE: Uploading Excel to SQL Server

Try the windows forums this isn't asp.net

RE: Uploading Excel to SQL Server

(OP)
Ok.

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

RE: Uploading Excel to SQL Server

Quote:

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.

RE: Uploading Excel to SQL Server

Quote (primagic)

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.

RE: Uploading Excel to SQL Server

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close