Data Changes not being saved from DataAdapter

R

RSH

I have a simple application that is filling a dataset, importing several
rows, building an Insert Statement, then attempting to commit the changes.
No exceptions are being raised but the data is never saving and I can't
figure out why not.

This is the method that fills the dataset:
public void PopulateDataset(string strSql)

{

try

{

OleDbCommand _ObjCmd = new OleDbCommand(strSql,
(OleDbConnection)Connection);

m_LDataAdapter = new OleDbDataAdapter(_ObjCmd);

if (Connection.State == ConnectionState.Closed)

{

Connection.Open();

}

m_Dataset = new DataSet();

m_LDataAdapter.Fill(m_Dataset, "tbl");

m_DatasetInitialized = true;

BuildInsertCommand();

}

catch (Exception ex)

{

MessageBox.Show(ex.ToString());

}

}



This method imports the row into the dataset:

public void AddRow(DataRow row)

{

if (m_DatasetInitialized == true)

{

m_Dataset.Tables[0].ImportRow(row);

}

}

This Method builds the Insert Command:

private void BuildInsertCommand()

{

if (m_DatasetInitialized == true)

{

OleDbParameter sqlParam;

OleDbCommand sqlUpdateCommand = new OleDbCommand("INSERT INTO [sheet1$]
([Group],[Category],[SearchPhrase],[CaseNumber],[TimeReceived],[SubmittedBy],[ClientNumber],[ClientName],[Status],[DateClosed],[Product],[AssignedToTech],[Detail])
VALUES(@Group,@Category,@SearchPhrase,@CaseNumber,@TimeReceived,@SubmittedBy,@ClientNumber,@ClientName,@Status,@DateClosed,@Product,@AssignedToTech,@Detail)",
(OleDbConnection)m_ObjConn);

sqlParam = new OleDbParameter("@Group", OleDbType.VarChar, 100, "Group");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

sqlParam = new OleDbParameter("@Category", OleDbType.VarChar, 100,
"Category");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

sqlParam = new OleDbParameter("@SearchPhrase", OleDbType.VarChar, 100,
"SearchPhrase");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

sqlParam = new OleDbParameter("@SearchPhrase", OleDbType.VarChar, 100,
"SearchPhrase");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

sqlParam = new OleDbParameter("@CaseNumber", OleDbType.VarChar, 100,
"CaseNumber");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

sqlParam = new OleDbParameter("@TimeReceived", OleDbType.VarChar, 100,
"TimeReceived");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

sqlParam = new OleDbParameter("@SubmittedBy", OleDbType.VarChar, 100,
"SubmittedBy");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

sqlParam = new OleDbParameter("@ClientNumber", OleDbType.VarChar, 100,
"ClientNumber");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

sqlParam = new OleDbParameter("@ClientName", OleDbType.VarChar, 250,
"ClientName");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

sqlParam = new OleDbParameter("@Status", OleDbType.VarChar, 250, "Status");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

sqlParam = new OleDbParameter("@DateClosed", OleDbType.VarChar, 250,
"DateClosed");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

sqlParam = new OleDbParameter("@Product", OleDbType.VarChar, 250,
"Product");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

sqlParam = new OleDbParameter("@AssignedToTech", OleDbType.VarChar, 250,
"AssignedToTech");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

sqlParam = new OleDbParameter("@Detail", OleDbType.VarChar, 1250, "Detail");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

m_LDataAdapter.InsertCommand = sqlUpdateCommand;

}

}



And this method attempts to save the data:

public void SaveChanges()

{

m_Dataset.AcceptChanges();

m_LDataAdapter.Update(m_Dataset, "tbl");

m_ObjConn.Close();

}



When I run the code in debugger I see the rows that were imported into the
dataset. I see that the insert Command was been added to the data adapter.
What am I missing?



Thanks so much for the help!

Ron
 
N

Nicholas Paldino [.NET/C# MVP]

RSH,

From the documentation for ImportRow:

Copies a DataRow into a DataTable, preserving any property settings, as well
as original and current values.

This means that the RowState is copied as well. If the RowState of the
row in the DataTable that is being copied from is unchanged, then it will be
unchanged in the new recordset.

If you copy the values over, it will create a new row that will be
processed by the data adapter.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

RSH said:
I have a simple application that is filling a dataset, importing several
rows, building an Insert Statement, then attempting to commit the changes.
No exceptions are being raised but the data is never saving and I can't
figure out why not.

This is the method that fills the dataset:
public void PopulateDataset(string strSql)

{

try

{

OleDbCommand _ObjCmd = new OleDbCommand(strSql,
(OleDbConnection)Connection);

m_LDataAdapter = new OleDbDataAdapter(_ObjCmd);

if (Connection.State == ConnectionState.Closed)

{

Connection.Open();

}

m_Dataset = new DataSet();

m_LDataAdapter.Fill(m_Dataset, "tbl");

m_DatasetInitialized = true;

BuildInsertCommand();

}

catch (Exception ex)

{

MessageBox.Show(ex.ToString());

}

}



This method imports the row into the dataset:

public void AddRow(DataRow row)

{

if (m_DatasetInitialized == true)

{

m_Dataset.Tables[0].ImportRow(row);

}

}

This Method builds the Insert Command:

private void BuildInsertCommand()

{

if (m_DatasetInitialized == true)

{

OleDbParameter sqlParam;

OleDbCommand sqlUpdateCommand = new OleDbCommand("INSERT INTO [sheet1$]
([Group],[Category],[SearchPhrase],[CaseNumber],[TimeReceived],[SubmittedBy],[ClientNumber],[ClientName],[Status],[DateClosed],[Product],[AssignedToTech],[Detail])
VALUES(@Group,@Category,@SearchPhrase,@CaseNumber,@TimeReceived,@SubmittedBy,@ClientNumber,@ClientName,@Status,@DateClosed,@Product,@AssignedToTech,@Detail)",
(OleDbConnection)m_ObjConn);

sqlParam = new OleDbParameter("@Group", OleDbType.VarChar, 100, "Group");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

sqlParam = new OleDbParameter("@Category", OleDbType.VarChar, 100,
"Category");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

sqlParam = new OleDbParameter("@SearchPhrase", OleDbType.VarChar, 100,
"SearchPhrase");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

sqlParam = new OleDbParameter("@SearchPhrase", OleDbType.VarChar, 100,
"SearchPhrase");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

sqlParam = new OleDbParameter("@CaseNumber", OleDbType.VarChar, 100,
"CaseNumber");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

sqlParam = new OleDbParameter("@TimeReceived", OleDbType.VarChar, 100,
"TimeReceived");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

sqlParam = new OleDbParameter("@SubmittedBy", OleDbType.VarChar, 100,
"SubmittedBy");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

sqlParam = new OleDbParameter("@ClientNumber", OleDbType.VarChar, 100,
"ClientNumber");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

sqlParam = new OleDbParameter("@ClientName", OleDbType.VarChar, 250,
"ClientName");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

sqlParam = new OleDbParameter("@Status", OleDbType.VarChar, 250,
"Status");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

sqlParam = new OleDbParameter("@DateClosed", OleDbType.VarChar, 250,
"DateClosed");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

sqlParam = new OleDbParameter("@Product", OleDbType.VarChar, 250,
"Product");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

sqlParam = new OleDbParameter("@AssignedToTech", OleDbType.VarChar, 250,
"AssignedToTech");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

sqlParam = new OleDbParameter("@Detail", OleDbType.VarChar, 1250,
"Detail");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

m_LDataAdapter.InsertCommand = sqlUpdateCommand;

}

}



And this method attempts to save the data:

public void SaveChanges()

{

m_Dataset.AcceptChanges();

m_LDataAdapter.Update(m_Dataset, "tbl");

m_ObjConn.Close();

}



When I run the code in debugger I see the rows that were imported into the
dataset. I see that the insert Command was been added to the data
adapter. What am I missing?



Thanks so much for the help!

Ron
Click to expand...
 
R

RSH

Nicholas,

You are correct...this was indeed the problem.

thank You!
Ron

Nicholas Paldino said:
RSH,

From the documentation for ImportRow:

Copies a DataRow into a DataTable, preserving any property settings, as
well as original and current values.

This means that the RowState is copied as well. If the RowState of the
row in the DataTable that is being copied from is unchanged, then it will
be unchanged in the new recordset.

If you copy the values over, it will create a new row that will be
processed by the data adapter.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

RSH said:
I have a simple application that is filling a dataset, importing several
rows, building an Insert Statement, then attempting to commit the changes.
No exceptions are being raised but the data is never saving and I can't
figure out why not.

This is the method that fills the dataset:
public void PopulateDataset(string strSql)

{

try

{

OleDbCommand _ObjCmd = new OleDbCommand(strSql,
(OleDbConnection)Connection);

m_LDataAdapter = new OleDbDataAdapter(_ObjCmd);

if (Connection.State == ConnectionState.Closed)

{

Connection.Open();

}

m_Dataset = new DataSet();

m_LDataAdapter.Fill(m_Dataset, "tbl");

m_DatasetInitialized = true;

BuildInsertCommand();

}

catch (Exception ex)

{

MessageBox.Show(ex.ToString());

}

}



This method imports the row into the dataset:

public void AddRow(DataRow row)

{

if (m_DatasetInitialized == true)

{

m_Dataset.Tables[0].ImportRow(row);

}

}

This Method builds the Insert Command:

private void BuildInsertCommand()

{

if (m_DatasetInitialized == true)

{

OleDbParameter sqlParam;

OleDbCommand sqlUpdateCommand = new OleDbCommand("INSERT INTO [sheet1$]
([Group],[Category],[SearchPhrase],[CaseNumber],[TimeReceived],[SubmittedBy],[ClientNumber],[ClientName],[Status],[DateClosed],[Product],[AssignedToTech],[Detail])
VALUES(@Group,@Category,@SearchPhrase,@CaseNumber,@TimeReceived,@SubmittedBy,@ClientNumber,@ClientName,@Status,@DateClosed,@Product,@AssignedToTech,@Detail)",
(OleDbConnection)m_ObjConn);

sqlParam = new OleDbParameter("@Group", OleDbType.VarChar, 100, "Group");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

sqlParam = new OleDbParameter("@Category", OleDbType.VarChar, 100,
"Category");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

sqlParam = new OleDbParameter("@SearchPhrase", OleDbType.VarChar, 100,
"SearchPhrase");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

sqlParam = new OleDbParameter("@SearchPhrase", OleDbType.VarChar, 100,
"SearchPhrase");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

sqlParam = new OleDbParameter("@CaseNumber", OleDbType.VarChar, 100,
"CaseNumber");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

sqlParam = new OleDbParameter("@TimeReceived", OleDbType.VarChar, 100,
"TimeReceived");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

sqlParam = new OleDbParameter("@SubmittedBy", OleDbType.VarChar, 100,
"SubmittedBy");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

sqlParam = new OleDbParameter("@ClientNumber", OleDbType.VarChar, 100,
"ClientNumber");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

sqlParam = new OleDbParameter("@ClientName", OleDbType.VarChar, 250,
"ClientName");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

sqlParam = new OleDbParameter("@Status", OleDbType.VarChar, 250,
"Status");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

sqlParam = new OleDbParameter("@DateClosed", OleDbType.VarChar, 250,
"DateClosed");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

sqlParam = new OleDbParameter("@Product", OleDbType.VarChar, 250,
"Product");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

sqlParam = new OleDbParameter("@AssignedToTech", OleDbType.VarChar, 250,
"AssignedToTech");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

sqlParam = new OleDbParameter("@Detail", OleDbType.VarChar, 1250,
"Detail");

sqlParam.SourceVersion = DataRowVersion.Original;

sqlUpdateCommand.Parameters.Add(sqlParam);

m_LDataAdapter.InsertCommand = sqlUpdateCommand;

}

}



And this method attempts to save the data:

public void SaveChanges()

{

m_Dataset.AcceptChanges();

m_LDataAdapter.Update(m_Dataset, "tbl");

m_ObjConn.Close();

}



When I run the code in debugger I see the rows that were imported into
the dataset. I see that the insert Command was been added to the data
adapter. What am I missing?



Thanks so much for the help!

Ron
Click to expand...
Click to expand...
 
Top