All --
Please help.
I have this sample working...
C:\Documents and Settings\kamoskim\My Documents\ComponentOne Samples\Studio for Silverlight\C1.Silverlight.Data\MasterDetail
...and it is great and I want to make it work with Sql Server 2005 Express.
I have things changed as best I can right now and the Retrive process works-- I get data onto the form.
However, when I try to Update the data, I get the following Run Time Error (RTE)...
System.Exception was unhandled by user code Message="Error updating data on the server. e.Result='Command parameter[10] '' is invalid.\nCommand parameter[11] '' is invalid.\nCommand parameter[12] '' is invalid.\nCommand parameter[13] '' is invalid.\nCommand parameter[14] '' is invalid.\nCommand parameter[15] '' is invalid.\nCommand parameter[16] '' is invalid.\nCommand parameter[17] '' is invalid.\nCommand parameter[18] '' is invalid.\n'. As of DateTime='2010-02-02 02:23:00.7815162 PM -05:00'." StackTrace: at MasterDetail01.Page.svc_UpdateDataCompleted(Object sender, UpdateDataCompletedEventArgs e) at MasterDetail01.DataService.DataServiceSoapClient.OnUpdateDataCompleted(Object state) InnerException:
....and when I step through the code, the Modifed rows are empty-- there are none when it gets on through into the web server.
Do you have any ideas? Comment? Guesses?
Thank you.
-- Mark Kamoski
FYI, I found my solution, which uses a GEFN and QandD fix to the sample code.
I had to, of course, switch out the connection string builder to grab an actual SqlServer based connection string-- but that is basic and was not the problem code.
The problem code was in the SmartDataSet class and the fix is below, which works both with an Access backend and a SqlServer backend...
private OleDbDataAdapter GetDataAdapter(DataTable dt){ OleDbDataAdapter da;
if (!_adapters.TryGetValue(dt, out da)) { // create adapter as usual string text = string.Format("select * from [{0}]", dt.TableName); da = new OleDbDataAdapter(text, _conn);
// build commands automatically OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
//old version... // //This does work for InsertAndDeleteAndUpdate when the database is Access. //This does not work for InsertAndDeleteAndUpdate when the database is SqlServer2005Express. // //cb.QuotePrefix = cb.QuoteSuffix = "`"; // << important!!!
//new version... // //This does work for InsertAndDeleteAndUpdate when the database is Access. //This does work for InsertAndDeleteAndUpdate when the database is SqlServer2005Express. // cb.QuotePrefix = "["; cb.QuoteSuffix = "]";
// fix automatic commands (ugh!) da = new OleDbDataAdapter(text, _conn);
if (dt.PrimaryKey.Length == 1) { DataColumn pk = dt.PrimaryKey[0];
da.UpdateCommand = FixCommand(cb.GetUpdateCommand(), pk); da.InsertCommand = FixCommand(cb.GetInsertCommand(), pk); da.DeleteCommand = FixCommand(cb.GetDeleteCommand(), pk);
Debug.WriteLine("da.DeleteCommand.Parameters.Count.ToString() " + da.DeleteCommand.Parameters.Count.ToString()); Debug.WriteLine("da.InsertCommand.Parameters.Count.ToString() " + da.InsertCommand.Parameters.Count.ToString()); Debug.WriteLine("da.UpdateCommand.Parameters.Count.ToString() " + da.UpdateCommand.Parameters.Count.ToString()); Debug.WriteLine("UpdateCommand: " + da.UpdateCommand.CommandText); Debug.WriteLine("InsertCommand: " + da.InsertCommand.CommandText); Debug.WriteLine("DeleteCommand: " + da.DeleteCommand.CommandText); Debug.WriteLine("Done."); } else { string err = string.Format("Update requires a single primary key, table '{0}' has {1} keys.", dt.TableName,
dt.PrimaryKey.Length); throw new InvalidOperationException(err); }
// save it for next update _adapters[dt] = da; }
// return fixed adapter return da;}
private OleDbCommand FixCommand(OleDbCommand cmd, DataColumn pk){ // trim everything after the id in the WHERE clause // to avoid stupid concurrency violation
//old... this does work with Access but not with SqlServer... // //string whereID = string.Format("WHERE ((`{0}` = ?)", pk.ColumnName);
//new when using sql server, use square brackets... // string whereID = string.Format("WHERE (([{0}] = ?)", pk.ColumnName);
int pos = cmd.CommandText.IndexOf(whereID, StringComparison.OrdinalIgnoreCase);
if (pos > -1) { whereID = whereID.Replace("((", "("); cmd.CommandText = cmd.CommandText.Substring(0, pos) + whereID; }
// copied from VS-generated commands (review: is this needed??) cmd.UpdatedRowSource = UpdateRowSource.Both;
//new... this did not exist before but it seems to be required when using SqlServer... //with this it works with SqlServer and Access... // int myCountOfQuestionMarks = this.CountStringOccurrences(cmd.CommandText, "?"); while (cmd.Parameters.Count > myCountOfQuestionMarks) { cmd.Parameters.RemoveAt(cmd.Parameters.Count - 1); }
foreach (OleDbParameter p in cmd.Parameters) { p.Precision = 0; }
// return fixed command return cmd;}
private int CountStringOccurrences(string text, string pattern){ // Loop through all instances of the string 'text'. int count = 0; int i = 0;
while ((i = text.IndexOf(pattern, i)) != -1) { i += pattern.Length; count++; }
return count;}
HTH.
Hi Mark,Thank you very much for sharing the solution.We will review it internally and try to make it part of the sample for the next release.Thanks!
Follow me: Twitter.com/leovernazza/
Leo --
As a related matter, please consider this other "quick win" wish list item that would be very easy for your team to do AND very nice for us users to have.
For the C1.SilverLight.Data.DataSet, please consider adding the following overloads...
ReadXml(string)
WriteXml(string)
...because...
...it is already more-or-less done internally but the overload does not exist...
...and...
...it would be very helpful when one wants to pass plain-text, human-readable, strings that contain XML instead of using byte arrays and memory streams.
Added to the todo list.Thanks!