V
vbguy2019
Guest
Hi I am trying to use "insert into table" in SQL Server. I am trying to read the dataset from excel and then insert those records into SQL Server table and am getting error. Below is the code I tried. I am not sure how to get over this error. What I am trying to do is "Insert into table (C1, C2, C3) Select C1, C2, C3 from" and here my data source needs to be the data I obtained from excel.
The issue is that I have tons of columns in the excel sheet and thousands of rows. I tried doing a bulkcopy but then there is some data in excel which I will be massaging prior to the insert - hence I am trying to use this insert into command.
Gotten into a bind and need an urgent out. Any help would be appreciated.
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim dataSet As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
Dim strSQL As String
Dim strSQL2 As String
MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\My Folder\Test File.xlsx;Extended Properties=""Excel 12.0 Xml;Allow Formula=false;HDR=YES""")
strSQL = "Select 'test file.xl' as FileName1, FN, LN FROM [my sHeet1$]"
MyCommand = New System.Data.OleDb.OleDbDataAdapter(strSQL, MyConnection)
dataSet = New System.Data.DataSet
MyCommand.Fill(dataSet)
DataGridView1.DataSource = dataSet.Tables(0)
Dim connectionString As String = "Server=MyServer;Database=MyDB;User=MeUser;Password=MyPassword;"
Dim connection As New SqlConnection(connectionString)
Dim command As New SqlCommand
connection.Open()
command.Connection = connection
> This statement below is where I am getting an error.
strSQL2 = "Insert into dbo.MyTest ( DestinationFileName1, DestinationFirstName, DestinationLastName) Select FileName1, FN, LN from " & dataSet.Tables(0)
> This statement above is where I am getting an error. - Error BC30452 Operator '&' is not defined for types 'String' and 'DataTable'.
command.CommandText = strSQL2
command.ExecuteNonQuery()
Continue reading...
The issue is that I have tons of columns in the excel sheet and thousands of rows. I tried doing a bulkcopy but then there is some data in excel which I will be massaging prior to the insert - hence I am trying to use this insert into command.
Gotten into a bind and need an urgent out. Any help would be appreciated.
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim dataSet As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
Dim strSQL As String
Dim strSQL2 As String
MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\My Folder\Test File.xlsx;Extended Properties=""Excel 12.0 Xml;Allow Formula=false;HDR=YES""")
strSQL = "Select 'test file.xl' as FileName1, FN, LN FROM [my sHeet1$]"
MyCommand = New System.Data.OleDb.OleDbDataAdapter(strSQL, MyConnection)
dataSet = New System.Data.DataSet
MyCommand.Fill(dataSet)
DataGridView1.DataSource = dataSet.Tables(0)
Dim connectionString As String = "Server=MyServer;Database=MyDB;User=MeUser;Password=MyPassword;"
Dim connection As New SqlConnection(connectionString)
Dim command As New SqlCommand
connection.Open()
command.Connection = connection
> This statement below is where I am getting an error.
strSQL2 = "Insert into dbo.MyTest ( DestinationFileName1, DestinationFirstName, DestinationLastName) Select FileName1, FN, LN from " & dataSet.Tables(0)
> This statement above is where I am getting an error. - Error BC30452 Operator '&' is not defined for types 'String' and 'DataTable'.
command.CommandText = strSQL2
command.ExecuteNonQuery()
Continue reading...