import excel to sql db table
- by droyce
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 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()
