By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,905 Members | 1,703 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,905 IT Pros & Developers. It's quick & easy.

SqlCeDataAdapter not running Insert Command...

P: n/a
Hi all

I am having problems getting my SqlCeDataAdapter to Update the SQL Mobile
Data base.
i am using C# CF2.
I have tried this a number of different ways. Starting with the command
builder but eventually i wrote out the entire command my self, and still
nothing.
The only thing that i can think this that because i am trying to add a table
that is created outside the data adaptor and then Merge/Copy the data into
the Adaptor so it can be saved.
If some one could take a look at the code below and let me know if it should
work? Or am I doing something wrong cos I have virtually copied this from
the MSDN site and i am getting no errors.

Sorry but there is allot of commenting out because of testing and trying
different things.
Thanks,
ink

#region OLD CODE
DataTable dtAnswers = (DataTable)e.AnswersDataTable;
SqlCeCommand oComSelect = null;
SqlCeCommand oComInsert = null;
//SqlCeCommandBuilder oComBuild = null;
DataSet oDS = null;
SqlCeDataAdapter oDA = null;
//string sSQL = "SELECT [pda_AuRe_AuditResultsID] FROM
AuditResults WHERE 1=2";
string sSQL = "SELECT [AuRe_CreatedDate] ,[AuRe_UpdatedDate]
,[AuRe_TimeStamp] ,[pda_AuRe_AuditsID]";
sSQL += " ,[aure_ResultText] ,[aure_ResultValue]
,[aure_AuditHeaderResult] ,[aure_Floor] ,[aure_RoomName] ";
sSQL += " ,[aure_Comment] ,[pda_aure_RoomTypeId]
,[pda_aure_SurveyQuestionsID]";
sSQL += " FROM AuditResults WHERE 1=2";

try
{
oDS = new DataSet();

//Set the Locale for the DataSet.
//Use the current culture as the default.
oDS.Locale =
System.Globalization.CultureInfo.CurrentCulture;

oComSelect = new SqlCeCommand();
oComSelect.Connection = DBConnection.GetLocalConnection();
oComSelect.CommandText = sSQL;

oDA = new SqlCeDataAdapter(oComSelect);
//oDA.SelectCommand = oCom;
sSQL = "INSERT INTO [AuditResults] ([AuRe_CreatedDate],
[AuRe_UpdatedDate], [AuRe_TimeStamp], [pda_AuRe_AuditsID], ";
sSQL += " [aure_ResultText], [aure_ResultValue],
[aure_AuditHeaderResult], [aure_Floor], [aure_RoomName], ";
sSQL += " [aure_Comment], [pda_aure_RoomTypeId],
[pda_aure_SurveyQuestionsID])";
sSQL += " VALUES (?, ?, ?,? ,?, ?,?,?, ?,?, ?,?)";
//sSQL += " VALUES (@AuRe_CreatedDate, @AuRe_UpdatedDate,
@AuRe_TimeStamp,@pda_AuRe_AuditsID,@aure_ResultTex t,
@aure_ResultValue,@aure_AuditHeaderResult,@aure_Fl oor,
@aure_RoomName,@aure_Comment,
@pda_aure_RoomTypeId,@pda_aure_SurveyQuestionsID)" ;
oComInsert = new SqlCeCommand();
oComInsert.Connection = DBConnection.GetLocalConnection();
oComInsert.CommandText = sSQL;
oComInsert.Parameters.Add(new
SqlCeParameter("@AuRe_CreatedDate", SqlDbType.DateTime, 8,
"AuRe_CreatedDate"));
oComInsert.Parameters.Add(new
SqlCeParameter("@AuRe_UpdatedDate", SqlDbType.DateTime, 8,
"AuRe_UpdatedDate"));
oComInsert.Parameters.Add(new
SqlCeParameter("@AuRe_TimeStamp", SqlDbType.DateTime, 8, "AuRe_TimeStamp"));
oComInsert.Parameters.Add(new
SqlCeParameter("@pda_AuRe_AuditsID", SqlDbType.UniqueIdentifier, 16,
"pda_AuRe_AuditsID"));
oComInsert.Parameters.Add(new
SqlCeParameter("@aure_ResultText", SqlDbType.NVarChar, 500,
"aure_ResultText"));
oComInsert.Parameters.Add(new
SqlCeParameter("@aure_ResultValue", SqlDbType.Int, 4, "aure_ResultValue"));
oComInsert.Parameters.Add(new
SqlCeParameter("@aure_AuditHeaderResult", SqlDbType.NChar, 1,
"aure_AuditHeaderResult"));
oComInsert.Parameters.Add(new SqlCeParameter("@aure_Floor",
SqlDbType.NVarChar, 50, "aure_Floor"));
oComInsert.Parameters.Add(new
SqlCeParameter("@aure_RoomName", SqlDbType.NVarChar, 50, "aure_RoomName"));
oComInsert.Parameters.Add(new
SqlCeParameter("@aure_Comment", SqlDbType.NVarChar, 2000, "aure_Comment"));
oComInsert.Parameters.Add(new
SqlCeParameter("@pda_aure_RoomTypeId", SqlDbType.UniqueIdentifier, 16,
"pda_aure_RoomTypeId"));
oComInsert.Parameters.Add(new
SqlCeParameter("@pda_aure_SurveyQuestionsID", SqlDbType.UniqueIdentifier,
16, "pda_aure_SurveyQuestionsID"));
//oComBuild = new SqlCeCommandBuilder(oDA);
//oComBuild.QuotePrefix = "[";
//oComBuild.QuoteSuffix = "]";
//oComBuild.SetAllValues = true;
//oComBuild.DataAdapter = oDA;

//oComInsert = new SqlCeCommand();
//oComInsert =
DBConnection.GetLocalConnection().CreateCommand();
//oComInsert.Parameters.Add(oComBuild.GetInsertComma nd().Parameters);
//oComInsert.CommandText =
oComBuild.GetInsertCommand().CommandText;
//oDA.InsertCommand = oComInsert;
//oDA.InsertCommand.CommandText =
oComBuild.GetInsertCommand().CommandText;

////Use the Combo Name for the data table name to.
//if (oDS.Tables["Answers"] == null)
//{
// //Fill the DataTable People within the DataSet sqlDS
// oDA.Fill(oDS, "Answers");
//}
//else
//{
// // Refresh the Employees DataSet.
// oDS.Clear();
// oDA.Fill(oDS, "Answers");
//}

//oDS.Tables["Answers"].BeginLoadData();
//oDS.Tables["Answers"].Merge(dtAnswers);
//oDS.Tables["Answers"].EndLoadData();
oDS.Tables.Add(dtAnswers.Copy());

//oDA.Update(oDS.Tables["Answers"]);
oDA.Update(oDS, "Answers");
oDS.AcceptChanges();

}
catch (SqlCeException err)
{
GlobalErrorHandler.ErrHandler(err,
"QA_Audit_QuestionsFinnished");
}
catch (Exception err)
{
GlobalErrorHandler.ErrHandler(err,
"QA_Audit_QuestionsFinnished");
}
finally
{
if (oDA != null) { oDA.Dispose(); }
}
//Close the form when done.
this.Close();
#endregion


Mar 16 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
I would get lost reading thru all of those strings, so I'm going to just try
to touch the basics ...

One thing to keep in mind: A data adapter does not "save" or "hold"
anything. Think of it as a bridge between your in-memory data (datatables or
datasets) and the actual database.

Command builders are worse than worthless. I don't even want to go there.

If you are not updating your database, then you most likely have a problem
with the Update SQL, the command object or parameters therein.

I saw an Insert command in your code, but I did not see an Update command.
Insert adds new data, Update changes it.

To test your problem fairly easily, you might set up a test app for Winforms
and see if you can update a sample database (NOT on the Mobile device). Find
your problems there (much easier to debug), then go back to the Mobile code
and resolve it.

"iKiLL" <iK***@NotMyEmail.comwrote in message
news:eb****************@TK2MSFTNGP03.phx.gbl...
Hi all

I am having problems getting my SqlCeDataAdapter to Update the SQL Mobile
Data base.
i am using C# CF2.
I have tried this a number of different ways. Starting with the command
builder but eventually i wrote out the entire command my self, and still
nothing.
The only thing that i can think this that because i am trying to add a
table that is created outside the data adaptor and then Merge/Copy the
data into the Adaptor so it can be saved.
If some one could take a look at the code below and let me know if it
should work? Or am I doing something wrong cos I have virtually copied
this from the MSDN site and i am getting no errors.

Sorry but there is allot of commenting out because of testing and trying
different things.
Thanks,
ink

#region OLD CODE
DataTable dtAnswers = (DataTable)e.AnswersDataTable;
SqlCeCommand oComSelect = null;
SqlCeCommand oComInsert = null;
//SqlCeCommandBuilder oComBuild = null;
DataSet oDS = null;
SqlCeDataAdapter oDA = null;
//string sSQL = "SELECT [pda_AuRe_AuditResultsID] FROM
AuditResults WHERE 1=2";
string sSQL = "SELECT [AuRe_CreatedDate] ,[AuRe_UpdatedDate]
,[AuRe_TimeStamp] ,[pda_AuRe_AuditsID]";
sSQL += " ,[aure_ResultText] ,[aure_ResultValue]
,[aure_AuditHeaderResult] ,[aure_Floor] ,[aure_RoomName] ";
sSQL += " ,[aure_Comment] ,[pda_aure_RoomTypeId]
,[pda_aure_SurveyQuestionsID]";
sSQL += " FROM AuditResults WHERE 1=2";

try
{
oDS = new DataSet();

//Set the Locale for the DataSet.
//Use the current culture as the default.
oDS.Locale =
System.Globalization.CultureInfo.CurrentCulture;

oComSelect = new SqlCeCommand();
oComSelect.Connection = DBConnection.GetLocalConnection();
oComSelect.CommandText = sSQL;

oDA = new SqlCeDataAdapter(oComSelect);
//oDA.SelectCommand = oCom;
sSQL = "INSERT INTO [AuditResults] ([AuRe_CreatedDate],
[AuRe_UpdatedDate], [AuRe_TimeStamp], [pda_AuRe_AuditsID], ";
sSQL += " [aure_ResultText], [aure_ResultValue],
[aure_AuditHeaderResult], [aure_Floor], [aure_RoomName], ";
sSQL += " [aure_Comment], [pda_aure_RoomTypeId],
[pda_aure_SurveyQuestionsID])";
sSQL += " VALUES (?, ?, ?,? ,?, ?,?,?, ?,?, ?,?)";
//sSQL += " VALUES (@AuRe_CreatedDate, @AuRe_UpdatedDate,
@AuRe_TimeStamp,@pda_AuRe_AuditsID,@aure_ResultTex t,
@aure_ResultValue,@aure_AuditHeaderResult,@aure_Fl oor,
@aure_RoomName,@aure_Comment,
@pda_aure_RoomTypeId,@pda_aure_SurveyQuestionsID)" ;
oComInsert = new SqlCeCommand();
oComInsert.Connection = DBConnection.GetLocalConnection();
oComInsert.CommandText = sSQL;
oComInsert.Parameters.Add(new
SqlCeParameter("@AuRe_CreatedDate", SqlDbType.DateTime, 8,
"AuRe_CreatedDate"));
oComInsert.Parameters.Add(new
SqlCeParameter("@AuRe_UpdatedDate", SqlDbType.DateTime, 8,
"AuRe_UpdatedDate"));
oComInsert.Parameters.Add(new
SqlCeParameter("@AuRe_TimeStamp", SqlDbType.DateTime, 8,
"AuRe_TimeStamp"));
oComInsert.Parameters.Add(new
SqlCeParameter("@pda_AuRe_AuditsID", SqlDbType.UniqueIdentifier, 16,
"pda_AuRe_AuditsID"));
oComInsert.Parameters.Add(new
SqlCeParameter("@aure_ResultText", SqlDbType.NVarChar, 500,
"aure_ResultText"));
oComInsert.Parameters.Add(new
SqlCeParameter("@aure_ResultValue", SqlDbType.Int, 4,
"aure_ResultValue"));
oComInsert.Parameters.Add(new
SqlCeParameter("@aure_AuditHeaderResult", SqlDbType.NChar, 1,
"aure_AuditHeaderResult"));
oComInsert.Parameters.Add(new SqlCeParameter("@aure_Floor",
SqlDbType.NVarChar, 50, "aure_Floor"));
oComInsert.Parameters.Add(new
SqlCeParameter("@aure_RoomName", SqlDbType.NVarChar, 50,
"aure_RoomName"));
oComInsert.Parameters.Add(new
SqlCeParameter("@aure_Comment", SqlDbType.NVarChar, 2000,
"aure_Comment"));
oComInsert.Parameters.Add(new
SqlCeParameter("@pda_aure_RoomTypeId", SqlDbType.UniqueIdentifier, 16,
"pda_aure_RoomTypeId"));
oComInsert.Parameters.Add(new
SqlCeParameter("@pda_aure_SurveyQuestionsID", SqlDbType.UniqueIdentifier,
16, "pda_aure_SurveyQuestionsID"));
//oComBuild = new SqlCeCommandBuilder(oDA);
//oComBuild.QuotePrefix = "[";
//oComBuild.QuoteSuffix = "]";
//oComBuild.SetAllValues = true;
//oComBuild.DataAdapter = oDA;

//oComInsert = new SqlCeCommand();
//oComInsert =
DBConnection.GetLocalConnection().CreateCommand();

//oComInsert.Parameters.Add(oComBuild.GetInsertComma nd().Parameters);
//oComInsert.CommandText =
oComBuild.GetInsertCommand().CommandText;
//oDA.InsertCommand = oComInsert;
//oDA.InsertCommand.CommandText =
oComBuild.GetInsertCommand().CommandText;

////Use the Combo Name for the data table name to.
//if (oDS.Tables["Answers"] == null)
//{
// //Fill the DataTable People within the DataSet sqlDS
// oDA.Fill(oDS, "Answers");
//}
//else
//{
// // Refresh the Employees DataSet.
// oDS.Clear();
// oDA.Fill(oDS, "Answers");
//}

//oDS.Tables["Answers"].BeginLoadData();
//oDS.Tables["Answers"].Merge(dtAnswers);
//oDS.Tables["Answers"].EndLoadData();
oDS.Tables.Add(dtAnswers.Copy());

//oDA.Update(oDS.Tables["Answers"]);
oDA.Update(oDS, "Answers");
oDS.AcceptChanges();

}
catch (SqlCeException err)
{
GlobalErrorHandler.ErrHandler(err,
"QA_Audit_QuestionsFinnished");
}
catch (Exception err)
{
GlobalErrorHandler.ErrHandler(err,
"QA_Audit_QuestionsFinnished");
}
finally
{
if (oDA != null) { oDA.Dispose(); }
}
//Close the form when done.
this.Close();
#endregion


Mar 18 '07 #2

P: n/a
Thanks for the Advice Earl.


"Earl" <br******@newsgroups.nospamwrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...
>I would get lost reading thru all of those strings, so I'm going to just
try to touch the basics ...

One thing to keep in mind: A data adapter does not "save" or "hold"
anything. Think of it as a bridge between your in-memory data (datatables
or datasets) and the actual database.

Command builders are worse than worthless. I don't even want to go there.

If you are not updating your database, then you most likely have a problem
with the Update SQL, the command object or parameters therein.

I saw an Insert command in your code, but I did not see an Update command.
Insert adds new data, Update changes it.

To test your problem fairly easily, you might set up a test app for
Winforms and see if you can update a sample database (NOT on the Mobile
device). Find your problems there (much easier to debug), then go back to
the Mobile code and resolve it.

"iKiLL" <iK***@NotMyEmail.comwrote in message
news:eb****************@TK2MSFTNGP03.phx.gbl...
>Hi all

I am having problems getting my SqlCeDataAdapter to Update the SQL Mobile
Data base.
i am using C# CF2.
I have tried this a number of different ways. Starting with the command
builder but eventually i wrote out the entire command my self, and still
nothing.
The only thing that i can think this that because i am trying to add a
table that is created outside the data adaptor and then Merge/Copy the
data into the Adaptor so it can be saved.
If some one could take a look at the code below and let me know if it
should work? Or am I doing something wrong cos I have virtually copied
this from the MSDN site and i am getting no errors.

Sorry but there is allot of commenting out because of testing and trying
different things.
Thanks,
ink

#region OLD CODE
DataTable dtAnswers = (DataTable)e.AnswersDataTable;
SqlCeCommand oComSelect = null;
SqlCeCommand oComInsert = null;
//SqlCeCommandBuilder oComBuild = null;
DataSet oDS = null;
SqlCeDataAdapter oDA = null;
//string sSQL = "SELECT [pda_AuRe_AuditResultsID] FROM
AuditResults WHERE 1=2";
string sSQL = "SELECT [AuRe_CreatedDate] ,[AuRe_UpdatedDate]
,[AuRe_TimeStamp] ,[pda_AuRe_AuditsID]";
sSQL += " ,[aure_ResultText] ,[aure_ResultValue]
,[aure_AuditHeaderResult] ,[aure_Floor] ,[aure_RoomName] ";
sSQL += " ,[aure_Comment] ,[pda_aure_RoomTypeId]
,[pda_aure_SurveyQuestionsID]";
sSQL += " FROM AuditResults WHERE 1=2";

try
{
oDS = new DataSet();

//Set the Locale for the DataSet.
//Use the current culture as the default.
oDS.Locale =
System.Globalization.CultureInfo.CurrentCulture ;

oComSelect = new SqlCeCommand();
oComSelect.Connection = DBConnection.GetLocalConnection();
oComSelect.CommandText = sSQL;

oDA = new SqlCeDataAdapter(oComSelect);
//oDA.SelectCommand = oCom;
sSQL = "INSERT INTO [AuditResults] ([AuRe_CreatedDate],
[AuRe_UpdatedDate], [AuRe_TimeStamp], [pda_AuRe_AuditsID], ";
sSQL += " [aure_ResultText], [aure_ResultValue],
[aure_AuditHeaderResult], [aure_Floor], [aure_RoomName], ";
sSQL += " [aure_Comment], [pda_aure_RoomTypeId],
[pda_aure_SurveyQuestionsID])";
sSQL += " VALUES (?, ?, ?,? ,?, ?,?,?, ?,?, ?,?)";
//sSQL += " VALUES (@AuRe_CreatedDate, @AuRe_UpdatedDate,
@AuRe_TimeStamp,@pda_AuRe_AuditsID,@aure_ResultTe xt,
@aure_ResultValue,@aure_AuditHeaderResult,@aure_F loor,
@aure_RoomName,@aure_Comment,
@pda_aure_RoomTypeId,@pda_aure_SurveyQuestionsID) ";
oComInsert = new SqlCeCommand();
oComInsert.Connection = DBConnection.GetLocalConnection();
oComInsert.CommandText = sSQL;
oComInsert.Parameters.Add(new
SqlCeParameter("@AuRe_CreatedDate", SqlDbType.DateTime, 8,
"AuRe_CreatedDate"));
oComInsert.Parameters.Add(new
SqlCeParameter("@AuRe_UpdatedDate", SqlDbType.DateTime, 8,
"AuRe_UpdatedDate"));
oComInsert.Parameters.Add(new
SqlCeParameter("@AuRe_TimeStamp", SqlDbType.DateTime, 8,
"AuRe_TimeStamp"));
oComInsert.Parameters.Add(new
SqlCeParameter("@pda_AuRe_AuditsID", SqlDbType.UniqueIdentifier, 16,
"pda_AuRe_AuditsID"));
oComInsert.Parameters.Add(new
SqlCeParameter("@aure_ResultText", SqlDbType.NVarChar, 500,
"aure_ResultText"));
oComInsert.Parameters.Add(new
SqlCeParameter("@aure_ResultValue", SqlDbType.Int, 4,
"aure_ResultValue"));
oComInsert.Parameters.Add(new
SqlCeParameter("@aure_AuditHeaderResult", SqlDbType.NChar, 1,
"aure_AuditHeaderResult"));
oComInsert.Parameters.Add(new
SqlCeParameter("@aure_Floor", SqlDbType.NVarChar, 50, "aure_Floor"));
oComInsert.Parameters.Add(new
SqlCeParameter("@aure_RoomName", SqlDbType.NVarChar, 50,
"aure_RoomName"));
oComInsert.Parameters.Add(new
SqlCeParameter("@aure_Comment", SqlDbType.NVarChar, 2000,
"aure_Comment"));
oComInsert.Parameters.Add(new
SqlCeParameter("@pda_aure_RoomTypeId", SqlDbType.UniqueIdentifier, 16,
"pda_aure_RoomTypeId"));
oComInsert.Parameters.Add(new
SqlCeParameter("@pda_aure_SurveyQuestionsID", SqlDbType.UniqueIdentifier,
16, "pda_aure_SurveyQuestionsID"));
//oComBuild = new SqlCeCommandBuilder(oDA);
//oComBuild.QuotePrefix = "[";
//oComBuild.QuoteSuffix = "]";
//oComBuild.SetAllValues = true;
//oComBuild.DataAdapter = oDA;

//oComInsert = new SqlCeCommand();
//oComInsert =
DBConnection.GetLocalConnection().CreateCommand() ;

//oComInsert.Parameters.Add(oComBuild.GetInsertComma nd().Parameters);
//oComInsert.CommandText =
oComBuild.GetInsertCommand().CommandText;
//oDA.InsertCommand = oComInsert;
//oDA.InsertCommand.CommandText =
oComBuild.GetInsertCommand().CommandText;

////Use the Combo Name for the data table name to.
//if (oDS.Tables["Answers"] == null)
//{
// //Fill the DataTable People within the DataSet sqlDS
// oDA.Fill(oDS, "Answers");
//}
//else
//{
// // Refresh the Employees DataSet.
// oDS.Clear();
// oDA.Fill(oDS, "Answers");
//}

//oDS.Tables["Answers"].BeginLoadData();
//oDS.Tables["Answers"].Merge(dtAnswers);
//oDS.Tables["Answers"].EndLoadData();
oDS.Tables.Add(dtAnswers.Copy());

//oDA.Update(oDS.Tables["Answers"]);
oDA.Update(oDS, "Answers");
oDS.AcceptChanges();

}
catch (SqlCeException err)
{
GlobalErrorHandler.ErrHandler(err,
"QA_Audit_QuestionsFinnished");
}
catch (Exception err)
{
GlobalErrorHandler.ErrHandler(err,
"QA_Audit_QuestionsFinnished");
}
finally
{
if (oDA != null) { oDA.Dispose(); }
}
//Close the form when done.
this.Close();
#endregion



Mar 19 '07 #3

P: n/a

I Think the problem with my data addaptor.
I have writen a test like you sugested and it works.
The Primary Key is an GUID with a default value and i have also made it the
ROWGUID.
It seems that when i create the select command and explicetly add the Key
Column it still does not exist in the Action commands and if you want to use
the command builder you have to have the Primary Key Column.

I am not 100 pecent yet but i will test this theory and see.

The strange thing is that when i create each command manualy i still get the
sam problem.

ink

"Earl" <br******@newsgroups.nospamwrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...
>I would get lost reading thru all of those strings, so I'm going to just
try to touch the basics ...

One thing to keep in mind: A data adapter does not "save" or "hold"
anything. Think of it as a bridge between your in-memory data (datatables
or datasets) and the actual database.

Command builders are worse than worthless. I don't even want to go there.

If you are not updating your database, then you most likely have a problem
with the Update SQL, the command object or parameters therein.

I saw an Insert command in your code, but I did not see an Update command.
Insert adds new data, Update changes it.

To test your problem fairly easily, you might set up a test app for
Winforms and see if you can update a sample database (NOT on the Mobile
device). Find your problems there (much easier to debug), then go back to
the Mobile code and resolve it.

"iKiLL" <iK***@NotMyEmail.comwrote in message
news:eb****************@TK2MSFTNGP03.phx.gbl...
>Hi all

I am having problems getting my SqlCeDataAdapter to Update the SQL Mobile
Data base.
i am using C# CF2.
I have tried this a number of different ways. Starting with the command
builder but eventually i wrote out the entire command my self, and still
nothing.
The only thing that i can think this that because i am trying to add a
table that is created outside the data adaptor and then Merge/Copy the
data into the Adaptor so it can be saved.
If some one could take a look at the code below and let me know if it
should work? Or am I doing something wrong cos I have virtually copied
this from the MSDN site and i am getting no errors.

Sorry but there is allot of commenting out because of testing and trying
different things.
Thanks,
ink

#region OLD CODE
DataTable dtAnswers = (DataTable)e.AnswersDataTable;
SqlCeCommand oComSelect = null;
SqlCeCommand oComInsert = null;
//SqlCeCommandBuilder oComBuild = null;
DataSet oDS = null;
SqlCeDataAdapter oDA = null;
//string sSQL = "SELECT [pda_AuRe_AuditResultsID] FROM
AuditResults WHERE 1=2";
string sSQL = "SELECT [AuRe_CreatedDate] ,[AuRe_UpdatedDate]
,[AuRe_TimeStamp] ,[pda_AuRe_AuditsID]";
sSQL += " ,[aure_ResultText] ,[aure_ResultValue]
,[aure_AuditHeaderResult] ,[aure_Floor] ,[aure_RoomName] ";
sSQL += " ,[aure_Comment] ,[pda_aure_RoomTypeId]
,[pda_aure_SurveyQuestionsID]";
sSQL += " FROM AuditResults WHERE 1=2";

try
{
oDS = new DataSet();

//Set the Locale for the DataSet.
//Use the current culture as the default.
oDS.Locale =
System.Globalization.CultureInfo.CurrentCulture ;

oComSelect = new SqlCeCommand();
oComSelect.Connection = DBConnection.GetLocalConnection();
oComSelect.CommandText = sSQL;

oDA = new SqlCeDataAdapter(oComSelect);
//oDA.SelectCommand = oCom;
sSQL = "INSERT INTO [AuditResults] ([AuRe_CreatedDate],
[AuRe_UpdatedDate], [AuRe_TimeStamp], [pda_AuRe_AuditsID], ";
sSQL += " [aure_ResultText], [aure_ResultValue],
[aure_AuditHeaderResult], [aure_Floor], [aure_RoomName], ";
sSQL += " [aure_Comment], [pda_aure_RoomTypeId],
[pda_aure_SurveyQuestionsID])";
sSQL += " VALUES (?, ?, ?,? ,?, ?,?,?, ?,?, ?,?)";
//sSQL += " VALUES (@AuRe_CreatedDate, @AuRe_UpdatedDate,
@AuRe_TimeStamp,@pda_AuRe_AuditsID,@aure_ResultTe xt,
@aure_ResultValue,@aure_AuditHeaderResult,@aure_F loor,
@aure_RoomName,@aure_Comment,
@pda_aure_RoomTypeId,@pda_aure_SurveyQuestionsID) ";
oComInsert = new SqlCeCommand();
oComInsert.Connection = DBConnection.GetLocalConnection();
oComInsert.CommandText = sSQL;
oComInsert.Parameters.Add(new
SqlCeParameter("@AuRe_CreatedDate", SqlDbType.DateTime, 8,
"AuRe_CreatedDate"));
oComInsert.Parameters.Add(new
SqlCeParameter("@AuRe_UpdatedDate", SqlDbType.DateTime, 8,
"AuRe_UpdatedDate"));
oComInsert.Parameters.Add(new
SqlCeParameter("@AuRe_TimeStamp", SqlDbType.DateTime, 8,
"AuRe_TimeStamp"));
oComInsert.Parameters.Add(new
SqlCeParameter("@pda_AuRe_AuditsID", SqlDbType.UniqueIdentifier, 16,
"pda_AuRe_AuditsID"));
oComInsert.Parameters.Add(new
SqlCeParameter("@aure_ResultText", SqlDbType.NVarChar, 500,
"aure_ResultText"));
oComInsert.Parameters.Add(new
SqlCeParameter("@aure_ResultValue", SqlDbType.Int, 4,
"aure_ResultValue"));
oComInsert.Parameters.Add(new
SqlCeParameter("@aure_AuditHeaderResult", SqlDbType.NChar, 1,
"aure_AuditHeaderResult"));
oComInsert.Parameters.Add(new
SqlCeParameter("@aure_Floor", SqlDbType.NVarChar, 50, "aure_Floor"));
oComInsert.Parameters.Add(new
SqlCeParameter("@aure_RoomName", SqlDbType.NVarChar, 50,
"aure_RoomName"));
oComInsert.Parameters.Add(new
SqlCeParameter("@aure_Comment", SqlDbType.NVarChar, 2000,
"aure_Comment"));
oComInsert.Parameters.Add(new
SqlCeParameter("@pda_aure_RoomTypeId", SqlDbType.UniqueIdentifier, 16,
"pda_aure_RoomTypeId"));
oComInsert.Parameters.Add(new
SqlCeParameter("@pda_aure_SurveyQuestionsID", SqlDbType.UniqueIdentifier,
16, "pda_aure_SurveyQuestionsID"));
//oComBuild = new SqlCeCommandBuilder(oDA);
//oComBuild.QuotePrefix = "[";
//oComBuild.QuoteSuffix = "]";
//oComBuild.SetAllValues = true;
//oComBuild.DataAdapter = oDA;

//oComInsert = new SqlCeCommand();
//oComInsert =
DBConnection.GetLocalConnection().CreateCommand() ;

//oComInsert.Parameters.Add(oComBuild.GetInsertComma nd().Parameters);
//oComInsert.CommandText =
oComBuild.GetInsertCommand().CommandText;
//oDA.InsertCommand = oComInsert;
//oDA.InsertCommand.CommandText =
oComBuild.GetInsertCommand().CommandText;

////Use the Combo Name for the data table name to.
//if (oDS.Tables["Answers"] == null)
//{
// //Fill the DataTable People within the DataSet sqlDS
// oDA.Fill(oDS, "Answers");
//}
//else
//{
// // Refresh the Employees DataSet.
// oDS.Clear();
// oDA.Fill(oDS, "Answers");
//}

//oDS.Tables["Answers"].BeginLoadData();
//oDS.Tables["Answers"].Merge(dtAnswers);
//oDS.Tables["Answers"].EndLoadData();
oDS.Tables.Add(dtAnswers.Copy());

//oDA.Update(oDS.Tables["Answers"]);
oDA.Update(oDS, "Answers");
oDS.AcceptChanges();

}
catch (SqlCeException err)
{
GlobalErrorHandler.ErrHandler(err,
"QA_Audit_QuestionsFinnished");
}
catch (Exception err)
{
GlobalErrorHandler.ErrHandler(err,
"QA_Audit_QuestionsFinnished");
}
finally
{
if (oDA != null) { oDA.Dispose(); }
}
//Close the form when done.
this.Close();
#endregion



Mar 20 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.