SqlCeDataAdapter not running Insert Command...

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_ResultText,
@aure_ResultValue,@aure_AuditHeaderResult,@aure_Floor,
@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.GetInsertCommand().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

Earl
Sun Mar 18 03:15:45 CDT 2007

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" <iKill@NotMyEmail.com> wrote in message
news:ebtGkp%23ZHHA.4856@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_ResultText,
> @aure_ResultValue,@aure_AuditHeaderResult,@aure_Floor,
> @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.GetInsertCommand().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
>
>
>
>

iKiLL
Mon Mar 19 06:19:48 CDT 2007

Thanks for the Advice Earl.




"Earl" <brikshoe@newsgroups.nospam> wrote in message
news:%23YxSOWTaHHA.3520@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" <iKill@NotMyEmail.com> wrote in message
> news:ebtGkp%23ZHHA.4856@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_ResultText,
>> @aure_ResultValue,@aure_AuditHeaderResult,@aure_Floor,
>> @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.GetInsertCommand().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
>>
>>
>>
>>
>
>

iKiLL
Tue Mar 20 05:34:33 CDT 2007


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" <brikshoe@newsgroups.nospam> wrote in message
news:%23YxSOWTaHHA.3520@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" <iKill@NotMyEmail.com> wrote in message
> news:ebtGkp%23ZHHA.4856@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_ResultText,
>> @aure_ResultValue,@aure_AuditHeaderResult,@aure_Floor,
>> @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.GetInsertCommand().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
>>
>>
>>
>>
>
>