Hi i am trying to write dataset into excel using oledb connection. I get an error "The microsoft access database engine could not find the object 'testTable' Make sure that name and path is correct". I understand that dataset is not in the excel thats y i get an error but how to give the correct path name to the table created in dataset.
Dim excelConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""E:\Test1.xls"";Extended Properties=""Excel 12.0;ReadOnly=FALSE;"""
Dim conn As New OleDbConnection(excelConn)
Try
Dim dsTest As New DataSet()
conn.Open()
Dim dt As New DataTable("testTable")
Dim c1 As New DataColumn
c1.ColumnName = "C1"
Dim c2 As New DataColumn
c2.ColumnName = "C2"
dt.Columns.Add(c1)
dt.Columns.Add(c2)
dsTest.Tables.Add(dt)
Dim dr As DataRow
dr = dsTest.Tables(0).NewRow
dr(0) = "hai"
dr(1) = "hello"
dsTest.Tables(0).Rows.Add(dr)
'Dim da As OleDbDataAdapter("Create table [Sheet1$] [C1,C2] ",conn)
Dim cmd As New OleDbCommand
cmd = conn.CreateCommand
cmd.CommandType = CommandType.Text
cmd.CommandText = "drop table [Sheet1$]"
cmd.ExecuteNonQuery()
'cmd.CommandText = "Create table [sheet1$] like testTable "
cmd.CommandText = "select * into [sheet1$] from " + dsTest.Tables(0).TableName + " where 1=2"
cmd.ExecuteNonQuery()
' cmd.CommandText = "insert into [Sheet1$] select * from testTable"
'cmd.CommandText = "Insert into [Sheet1$] (c1,c2) values ('5','10')"
cmd.ExecuteNonQuery()
Catch ex As Exception
Console.WriteLine(ex.Message)
Finally
conn.close()
End Try