DataAdapter not performing update

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

Guest

Any idea why the database table is not being updated with the contents of the excel file loaded into the dataset? Any help is appreciated!

// load database table, shell only
string cnString = "DSN=XXX D5200";
OdbcConnection myConnection = new OdbcConnection(cnString);
OdbcDataAdapter da = new OdbcDataAdapter("select * from DB where 1=2;", myConnection);
DataSet ds = new DataSet();
da.Fill(ds, "ccginput");

// load excel file into dataset
string strCnExcel = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + fPath + ";" +
"Extended Properties=Excel 8.0;";
OleDbConnection objConn = new OleDbConnection(strCnExcel);
objConn.Open();
OleDbCommand objCmdSelect =new OleDbCommand("SELECT * FROM [sheet1$]", objConn);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
objAdapter1.SelectCommand = objCmdSelect;
objAdapter1.Fill(ds, "ccginput");

// update database with contents of dataset
da.AcceptChangesDuringFill=true;
da.Update(ds,"ccginput");
 
Chris,
1) You will probably get better answers in
microsoft.public.dotnet.framework.adonet.
2) Set AcceptChangesDuringFill to false when filling from the Excel file
otherwise
the RowStates will all be unchanged and there will be nothing to
update.
3) You aren't defining Delete, Insert, or Update commands for the
DataAdapter anywhere
in this code so there are no command to insert, etc into the Table.

Ron Allen
Chris Fink said:
Any idea why the database table is not being updated with the contents of
Click to expand...
the excel file loaded into the dataset? Any help is appreciated!
// load database table, shell only
string cnString = "DSN=XXX D5200";
OdbcConnection myConnection = new OdbcConnection(cnString);
OdbcDataAdapter da = new OdbcDataAdapter("select * from DB where 1=2;", myConnection);
DataSet ds = new DataSet();
da.Fill(ds, "ccginput");

// load excel file into dataset
string strCnExcel = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + fPath + ";" +
"Extended Properties=Excel 8.0;";
OleDbConnection objConn = new OleDbConnection(strCnExcel);
objConn.Open();
OleDbCommand objCmdSelect =new OleDbCommand("SELECT * FROM [sheet1$]", objConn);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
objAdapter1.SelectCommand = objCmdSelect;
objAdapter1.Fill(ds, "ccginput");

// update database with contents of dataset
da.AcceptChangesDuringFill=true;
da.Update(ds,"ccginput");
Click to expand...
 
Chris said:
Any idea why the database table is not being updated with the contents of
the excel file loaded into the dataset? Any help is appreciated!

// load database table, shell only
string cnString = "DSN=XXX D5200";
OdbcConnection myConnection = new OdbcConnection(cnString);
OdbcDataAdapter da = new OdbcDataAdapter("select * from DB where 1=2;",
myConnection); DataSet ds = new DataSet();
da.Fill(ds, "ccginput");

// load excel file into dataset
string strCnExcel = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + fPath + ";" +
"Extended Properties=Excel 8.0;";
OleDbConnection objConn = new OleDbConnection(strCnExcel);
objConn.Open();
OleDbCommand objCmdSelect =new OleDbCommand("SELECT * FROM [sheet1$]",
objConn); OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
objAdapter1.SelectCommand = objCmdSelect;
objAdapter1.Fill(ds, "ccginput");

// update database with contents of dataset
da.AcceptChangesDuringFill=true;
da.Update(ds,"ccginput");
Click to expand...

Seems like what you're doing is first filling ds with contents from a table,
then you redefine ds and load data from a spreadsheet and then suddenly you
expect the spreadsheet data to update the sql table ?

I think what you need is two Datasets and then you want to insert rows from
one to the other.
 
Back
Top