Re: ado.net - filling new msaccess table with data in a dataset

I will provide a simple example below using VB.NET and C#. I have created
two columns in Excel *** (Col1 and Col2) with data. I created a table in
Access with the same column types. Replace with appropriate Excel *** name
and Access DB name

Using C#
=================================================================
using System.Data.OleDb;

OleDbConnection conn,conn1;
OleDbCommand cmd;
OleDbDataAdapter adapter1;
DataSet dataset1;
conn=new OleDbConnection();
conn1=new OleDbConnection();
try
{
conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data
source= <Excel *** Name>;"+ @"Extended Properties=""Excel 8.0;HDR=Yes;""";
conn1.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;"+@"Data
Source=<Access Database Name>;";
conn.Open();
conn1.Open();
cmd=new OleDbCommand("Select * from TestTable1",conn);
adapter1 = new OleDbDataAdapter();
adapter1.SelectCommand = cmd;
dataset1= new DataSet();
adapter1.Fill(dataset1);
cmd.Connection=conn1;
foreach(DataRow dr in dataset1.Tables[0].Rows)
{
cmd.CommandText="insert into SampleTable(fld1,fld2)
values("+Convert.ToInt32(dr["Col1"].ToString())+",'"+dr["Col2"].ToString()+"')";
cmd.ExecuteNonQuery();
}
}
catch(OleDbException ex)
{
Console.WriteLine("Failed to connect" + ex.ToString());
}
finally
{
conn.Close();
conn1.Close();
}
=================================================================

Using VB.NET
=================================================================
Imports System.Data.OleDb
Dim cn As New OleDbConnection
Dim cn1 As New OleDbConnection
Dim adapter As New OleDbDataAdapter
Dim dtset As New DataSet
Dim cmd As New OleDbCommand
Dim dr As DataRow

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data source=
<Excel *** Name>;" + "Extended Properties=""Excel 8.0;HDR=Yes;"""
cn1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data
Source=<Access DB Name>;"

cn.Open()
cn1.Open()

cmd.Connection = cn
cmd.CommandText = "Select * from TestTable1"
adapter.SelectCommand = cmd
adapter.Fill(dtset)
cmd.Connection = cn1
For Each dr In dtset.Tables(0).Rows
cmd.CommandText = "insert into SampleTable(fld1,fld2) values(" +
(dr("Col1")).ToString() + ",'" + dr("Col2").ToString() + "')"
cmd.ExecuteNonQuery()
Next
cmd = Nothing
cn.Close()
cn1.Close()
=================================================================


-------------------------------------------------------------
This posting is provided "AS IS", with no
warranties, and confers no rights.Please
do not send email directly to this alias.
This alias is for newsgroup purposes only
-------------------------------------------------------------

"rudy" <rmarma@xxxxxxxxx> wrote in message
news:1128604242.552620.169860@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> how do I read an excel *** into a dataset and then write that data to
> an empty table in a mdb
> Thanks Rudy
>


.