deva.v 0 Unverified User

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