Jon Stroh

I was wondering , Am I not allowed to change the values in the table that I am looping through This procedure below updates the MobileDbDataset fine but the line dr.PRIORITY = "U" chages when the code runs but the dataset is not updated when getChanges, Update and Accept Changes are called. What am I doing wrong

Thanks Jon Stroh

//This procedure will update changed Customer records on the PC to the Mobile Device's Client Database.

DataSet1.CUSTDataTable dt = new DataSet1.CUSTDataTable();

DataSet1TableAdapters.CUSTTableAdapter ta = new FBWinMobileSync.DataSet1TableAdapters.CUSTTableAdapter();

dataSet1.EnforceConstraints = false;

ta.FillByModified(dt);

progressBar1.Increment(10);

foreach (DataSet1.CUSTRow dr in dt.Rows)

{

FB7MobileDBDataSet.ClientsRow modMobileClient = this.fB7MobileDBDataSet.Clients.FindByCode(dr.CODE);

modMobileClient.LName = dr.LASTNAME;

modMobileClient.FName = dr.FIRSTNAME;

modMobileClient.Active = dr.ACTIVE;

modMobileClient.Address = dr.ADD1;

modMobileClient.Attn = dr.ATTN;

modMobileClient.City = dr.CITY;

modMobileClient.Name = dr.Name;

modMobileClient.PaymentMeth = dr.PaymentMethod;

modMobileClient.Phone1 = dr.PHONE1;

modMobileClient.Phone2 = dr.PHONE2;

modMobileClient.Phone3 = dr.Phone3;

modMobileClient.Phone4 = dr.Phone4;

modMobileClient.Phone5 = dr.Phone5;

modMobileClient.Priority = "U";

modMobileClient.Stable = dr.STABLE;

modMobileClient.State = dr.STATE;

modMobileClient.Type = dr.TYPEA;

modMobileClient.Zip = dr.ZIP;

dr.PRIORITY = "U";//= change back to no change to record

}

this.fB7MobileDBDataSet.Clients.GetChanges(DataRowState.Modified);

this.clientsTableAdapter.Update(this.fB7MobileDBDataSet.Clients);

this.fB7MobileDBDataSet.Clients.AcceptChanges();

this.dataSet1.CUST.GetChanges(DataRowState.Modified);

this.cUSTTableAdapter.Update(this.dataSet1.CUST);

this.dataSet1.CUST.AcceptChanges();

progressBar1.Increment(15);




Re: .NET Framework Data Access and Storage ForEach loop question about Databases

Jon Stroh

Just in case it matters the dataSet1.Cust database is a Paradox table I am accessing it with the VS2005 ODBC driver.

Thanks Jon Stroh






Re: .NET Framework Data Access and Storage ForEach loop question about Databases

Matt Neerincx

In general you can change the records in a loop, should work fine as you coded it.

The only other thing I can think of is somehow you did not setup the data adapter properly (did not setup proper insert and update and delete commands).






Re: .NET Framework Data Access and Storage ForEach loop question about Databases

Jon Stroh

In the Dataset1.xsd file when I select each of the Cust columns all that are supposed to say AllowDbNull say just that. After your answer was posted I selected the table adapter and then selected the UpdateCommand Parameters and clicked the collection button and the allowDBnull property was set to false in the update command param, is this normal that you have to set this again in the update,insert and delete commands

Thanks Very much for your previous answer!!

Jon Stroh






Re: .NET Framework Data Access and Storage ForEach loop question about Databases

Jon Stroh

The msvs 2005 generated update for the ODBC dataset looks strange to me below is the code. I am not very good with sql though. This dataset will not update.

UPDATE CUST
SET CODE = , LINK = , FIRSTNAME = , LASTNAME = , Name = , STABLE = , ACTIVE = , ATTN = , ADD1 = , CITY = , STATE = , ZIP = ,
PHONE1 = , PHONE2 = , Phone3 = , Phone4 = , Phone5 = , TYPEA = , PRIORITY = , Options = , Flags = , Filler = , Company = , Country = ,
Title = , Custom1 = , Custom2 = , Custom3 = , Custom4 = , PalmID = , PaymentMethod = , Notes =
WHERE (CODE = ) AND ( = 1 AND LINK IS NULL OR
LINK = ) AND ( = 1 AND FIRSTNAME IS NULL OR
FIRSTNAME = ) AND ( = 1 AND LASTNAME IS NULL OR
LASTNAME = ) AND ( = 1 AND Name IS NULL OR
Name = ) AND ( = 1 AND STABLE IS NULL OR
STABLE = ) AND ( = 1 AND ACTIVE IS NULL OR
ACTIVE = ) AND ( = 1 AND ATTN IS NULL OR
ATTN = ) AND ( = 1 AND ADD1 IS NULL OR
ADD1 = ) AND ( = 1 AND CITY IS NULL OR
CITY = ) AND ( = 1 AND STATE IS NULL OR
STATE = ) AND ( = 1 AND ZIP IS NULL OR
ZIP = ) AND ( = 1 AND PHONE1 IS NULL OR
PHONE1 = ) AND ( = 1 AND PHONE2 IS NULL OR
PHONE2 = ) AND ( = 1 AND Phone3 IS NULL OR
Phone3 = ) AND ( = 1 AND Phone4 IS NULL OR
Phone4 = ) AND ( = 1 AND Phone5 IS NULL OR
Phone5 = ) AND ( = 1 AND TYPEA IS NULL OR
TYPEA = ) AND ( = 1 AND PRIORITY IS NULL OR
PRIORITY = ) AND ( = 1 AND Options IS NULL OR
Options = ) AND ( = 1 AND Flags IS NULL OR
Flags = ) AND ( = 1 AND Filler IS NULL OR
Filler = ) AND ( = 1 AND Company IS NULL OR
Company = ) AND ( = 1 AND Country IS NULL OR
Country = ) AND ( = 1 AND Title IS NULL OR
Title = ) AND ( = 1 AND Custom1 IS NULL OR
Custom1 = ) AND ( = 1 AND Custom2 IS NULL OR
Custom2 = ) AND ( = 1 AND Custom3 IS NULL OR
Custom3 = ) AND ( = 1 AND Custom4 IS NULL OR
Custom4 = ) AND ( = 1 AND PalmID IS NULL OR
PalmID = ) AND ( = 1 AND PaymentMethod IS NULL OR
PaymentMethod = )

This is the msvs 2005 generated code for the one of the WinMobileDatabase's that will update fine.

UPDATE Horse
SET CODE = @p1, Stable = @p2, HorseName = @p3, NickName = @p4, Active = @p5, MainWork = @p6, LastDate = @p7, Weeks = @p8, DueDate = @p9,
LastCost = @p10, Totals = @p11, AngleLF = @p12, AngleRF = @p13, AngleLH = @p14, AngleRH = @p15, LengthLF = @p16, LengthRF = @p17,
LengthLH = @p18, LengthRH = @p19, SizeLF = @p20, SizeRF = @p21, SizeLH = @p22, SizeRH = @p23, StartAngleLF = @p24, StartAngleRF = @p25,
StartAngleLH = @p26, StartAngleRH = @p27, StartLengthLF = @p28, StartLengthRF = @p29, StartLengthLH = @p30, StartLengthRH = @p31,
NeedsNewFr = @p32, NeedsNewHd = @p33, Front = @p34, Hind = @p35, AngleList = @p36, LengthList = @p37, [Delete] = @p38
WHERE (CODE = @p39) AND (Stable = @p40) AND (HorseName = @p41) These are the indexed fields






Re: .NET Framework Data Access and Storage ForEach loop question about Databases

Jon Stroh

When editing a record in the CUST database and then clicking the Save button generated by Visual Studio on the cUSTBinding Navigator running this code

this.Validate();

this.cUSTBindingSource.EndEdit();

this.cUSTTableAdapter.Update(this.dataSet1.CUST);Error here

I get this error. Appears that the paradox Driver is not alowing the post. How do I know which of the three Paradox ODBC drivers to use Or is it that

System.Data.Odbc.OdbcException was unhandled
Message="ERROR [HY104] [Microsoft][ODBC Paradox Driver]Invalid precision value "
Source="odbcjt32.dll"
ErrorCode=-2146232009
StackTrace:
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
at FBWinMobileSync.DataSet1TableAdapters.CUSTTableAdapter.Update(CUSTDataTable dataTable) in C:\Documents and Settings\Jon\My Documents\Visual Studio 2005\Projects\FBWinMobileSync\FBWinMobileSync\DataSet1.Designer.cs:line 13451
at FBWinMobileSync.Form1.cUSTBindingNavigatorSaveItem_Click(Object sender, EventArgs e) in C:\Documents and Settings\Jon\My Documents\Visual Studio 2005\Projects\FBWinMobileSync\FBWinMobileSync\Form1.cs:line 66
at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)
at System.Windows.Forms.ToolStripButton.OnClick(EventArgs e)
at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)
at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e)
at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met)
at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met)
at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ToolStrip.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at FBWinMobileSync.Program.Main() in C:\Documents and Settings\Jon\My Documents\Visual Studio 2005\Projects\FBWinMobileSync\FBWinMobileSync\Program.cs:line 17
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()






Re: .NET Framework Data Access and Storage ForEach loop question about Databases

Matt Neerincx

I know the auto-generated update statement will poll the driver to determine what unique indexes are on the table. If no indexes are found then it will generate the "all fields in where clause" update statement. This is most likely not optimal, so if you do know the unqiue indexes you can prune out the other fields manually.

Currently you are using the Jet based Paradox driver (odbcjt32.dll). I have not used Paradox in probably 6 years so I can't remember much about it. I seem to recall Paradox shipped their own driver and Jet has one built in.

Invalid precision value means that the caller did not bind correctly to the precision for the field. Since you are using .NET provider for ODBC, you don't have much control over how the binding is done I think (not sure about this). Note it could be due to sending too many characters for a textual field. So I would start by simplifying your update statement first to elminate as many variables as possible to narrow down the root cause.