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,@ClientNa me,@Status,@DateClosed,@Product,@AssignedToTech,@D etail)",
(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