OleDb DataSet Retrieving Data from Excel Placing into a SQLTable

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My Question is this, I'm having trouble getting the OleDbDataAdapter to Point
to another connection string and pull the data out of the DataSet I created
from the Excel Spread Sheet and Place it up in a SQL table. I'm Sorry for the
mess of Code but I've been at this all Day now. If you have any Ideas I could
surely use the help.
Thank You,
Terry
string File = ofdExcel.FileName;
string sConn = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source="+File+@";" +
"Extended Properties=Excel 8.0;";
OleDbConnection DC =new OleDbConnection(sConn);
DC.Open();
OleDbCommand OC =new OleDbCommand("SELECT * FROM [Sheet1$]"",DC);
OleDbDataAdapter OA =new OleDbDataAdapter();
OA.SelectCommand=OC;
DataSet DS =new DataSet();
DataTable custTable = DS.Tables.Add("Global");
OA.Fill(DS,"Global");

//--> Clone the old DataSet and create new pointer to DS2
string Oconn = @"Integrated Security=SSPI;Packet Size=4096;Data
Source=""TVIT18\TVIT18"";Tag with column collation when
possible=False;Initial Catalog=IA;Use Procedure for Prepare=1;Auto
Translate=True;Persist Security
Info=False;Provider=""SQLOLEDB.1"";Workstation ID=TVIT13;Use Encryption for
Data=False";
OleDbConnection CO2 =new OleDbConnection(Oconn);
DataSet DS2 = new DataSet();
DS2 = DS.Clone(); //-->Clone DataSet
OleDbCommand OC2 = new OleDbCommand();
OleDbDataAdapter OA2 =new OleDbDataAdapter();
OA2.SelectCommand=OC2;
 
Perhaps what you need to do is create a DataAdapter on your SQL Server
connection, and call update on it, passing your DataSet as a parameter.

I have my reservations whether that would work however, since the DataSet
stores internal changes to it's data relative to the original Data Source.
So eventually, maybe you'll have to loop through the DataSet & add the rows
manually to your SQL Server tables :(

Angel
O:]
 
Back
Top