import excel to sql db table

I am trying to write data from an excel spread sheet to a SQL Database. I have been able to connect to the Excel Spreadsheet and read the data but I am unable to get the data to insert into the SQL DB table.

the current code is as follows

any help most appreciated.

    Dim plmExcelCon As System.Data.OleDb.OleDbConnection
    Dim开发者_如何学Go ldExcelDS As System.Data.DataSet
    Dim cmdLoadExcel As System.Data.OleDb.OleDbDataAdapter
    Dim PrmPathExcelFile As String
    PrmPathExcelFile = txtImportFileLocation.Text.ToString

    plmExcelCon = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + PrmPathExcelFile + ";Extended Properties=Excel 12.0;")
    cmdLoadExcel = New System.Data.OleDb.OleDbDataAdapter("select * from [" + txtImportSheetName.Text + "$]", plmExcelCon)
    ldExcelDS = New System.Data.DataSet
    cmdLoadExcel.Fill(ldExcelDS)

    dgvImportData.DataSource = ldExcelDS.Tables(0)

    plmExcelCon.Close()


   cmdINSERT.Parameters("@[SQL COLUMN NAME]").Value = [Not sure how to set value from datagrid view]


  cnLD.Open()
  cmdINSERT.ExecuteNonQuery()
  cnLD.Close()

---

I was tasked with pulling some data from Excel, modifying it, and putting it into a SQL database as well. Below is something similar to what I did:

    Dim MyConnection As New System.Data.OleDb.OleDbConnection

    Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
    MyConnection = New System.Data.OleDb.OleDbConnection ("provider=Microsoft.Jet.OLEDB.4.0; data source='" + fileLocation + " ';Extended Properties=Excel 8.0;")

    ' Select the data from the 'Samples' sheet of the workbook.
    MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Samples$]", MyConnection)
    Dim ds as new DataSet
    MyCommand.Fill(ds)

    MyConnection.Close()

This is pretty much what you have already, and you said that works to pull the data from Excel. From there I would just iterate through the DataSet and add it to the database this way:

    Dim dt as DataTable = ds.Tables(0)
    For each row as DataRow in dt.Rows
        dim value1 as String = row("columnName")
        dim value2 as Double = row("columnName2")

        'Write a function that connects to the database with your parameters
        insertIntoDatabase(value1, value2)
    Next

Hopefully that is the bridge you needed (acessing a DataTable's data). Good luck!

---

I'm not sure if the code you have is able to work. But I think there is a better way to go about this.

When I have done stuff like this I have used ACCESS as a data container - it is possible to link ACCESS to both EXCEL data and to ODBC sources. If you have created the required links in ACCESS it is then a simple matter to run an INSERT INTO SQL query

But I think you could probably do it without ACCESS - as you can make VBA think EXCEL is an ODBC source - so you can probably make all the connections direct from VBA.

There are documents on the microsoft support site that explain the syntax and methods.

---

I have finally got it to work with the following code

    Dim plmExcelCon As New System.Data.OleDb.OleDbConnection
    Dim cmdLoadExcel As System.Data.OleDb.OleDbDataAdapter
    Dim PrmPathExcelFile As String

    PrmPathExcelFile = txtImportFileLocation.Text.ToString

    plmExcelCon = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + PrmPathExcelFile + ";Extended Properties=Excel 12.0;")

    cmdLoadExcel = New System.Data.OleDb.OleDbDataAdapter("select * from [" + txtImportSheetName.Text + "$]", plmExcelCon)

    Dim dt As System.Data.DataTable

    dt = New System.Data.DataTable

    cmdLoadExcel.Fill(dt)

    plmExcelCon.Close()

    For Each row As DataRow In dt.Rows
        Dim impEID As String = row(txtImportEID.Text)
        Dim impVID As String = row(txtImportVID.Text)

    Try

            cmdINSERT.Parameters("@pldLifeDataEID").Value = impEID
            cmdINSERT.Parameters("@pldLifeDataVID").Value = impVID
            cmdINSERT.Parameters("@pldLifeDataDateEntry").Value = Date.Now

            cnLD.Open()
            cmdINSERT.ExecuteNonQuery()
            cnLD.Close()

        Catch ex As Exception
            MessageBox.Show(ErrorToString)
        End Try
    Next

thanks for all your help