Home Library Learn Samples Downloads Support Community Forums
Microsoft Developer Network >
> 
> 
> 
Importing data from Excel to SQL database table

Locked Importing data from Excel to SQL database table

  • Tuesday, October 14, 2008 7:06 AM
     
      Has Code
    Sign In to Vote
    0
    Hi my friends,
    I am developing an application. This application has SQL 2005 database. I want to enable the user to import data from Excel file to a SQL database table . I used this code, it worked fine but the data are not saved to the sql database when I click (save). Can you modify the code to enable data to be saved:

    Private Sub loadDataFromExcel()

    Dim excelFile As String = "c:\new.xls" 'replace with valid path - file

    Dim excelSheet As String = "[Sheet$]" 'replace with valid Sheet Name

    Dim sql As String = "SELECT * FROM " & excelSheet ' add WHERE and ORDER if required

    Dim tableName As String = "student"

    Try

    Using cn As New OleDb.OleDbConnection

    ' HDR = YES if first row contain column name else NO

    ' Excel 8.0 / 9.0 ... depending on your Excel version

    cn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;" & _

    "data source=" & excelFile & ";" & _

    "Extended Properties=""Excel 8.0;HDR=YES"""

    Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(sql, cn)

    cn.Open()

    da.Fill(Me.MytestDataSet.student)



    cn.Close()

    End Using

    Catch ex As Exception

    MessageBox.Show(ex.Message)

    End Try

    'TODO proccess data to add / update other tables

    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click

    loadDataFromExcel()

    End Sub



    Thanks a lot

    • Edited by mishsad Tuesday, October 14, 2008 7:16 AM now better
    •  
       

    All Replies

    © 2013 Microsoft. All rights reserved.