Re: DB update issue

It's hard to troubleshoot without understanding the table schema, but
what I'd suggest is to write the completed update statement to a
variable, set a breakpoint, then copy the entire thing over to an
Access query window after selecting the SQL option. Then run it. That
will give you more precise information on whether it's a syntax or
permissions error.

--Mary

On Thu, 3 Nov 2005 07:33:01 -0800, AJK <AJK@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:

>I am having a problem updating a record with dates in access. The update
>works fine until I add the date field then I get an error that this is a
>non-updatable field. What does that mean? I have been scowering the internet
>for 2 days to no avail. PLEASE HELP. I have posted the SQL statement code
>below. I have tried this about 30 different ways. I have some of the current
>code below.
>
>thanks
>
>ajk
>
>
>
>string strAccessConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data
>Source=C:\\AquariumLogv1r1.mdb";
>string updateStatement = null;
>
>if (f.FishID > 0)
>{
>updateStatement = "UPDATE Fish_Header ";
>}
>else
>{
>updateStatement = "INSERT into Fish_Header ";
>}
>
>string deathAge = " ";
>if(DateTime.Equals("01/01/2000", f.DateOfDeath))
>{
>int ageInMonths = int.Parse(f.Age);
>TimeSpan deathAgeTimespan = (f.DateOfDeath.AddMonths(ageInMonths) -
>f.PurchaseDate);
>deathAge = deathAgeTimespan.TotalDays.ToString();
>}
>else
>{
>f.CauseOfDeath = " ";
>f.DateOfDeath = DateTime.Parse("01/01/2000");
>}
>
>
>updateStatement += "SET Sex = '" + f.Sex + "', " +
>"ApproxAge = '" + f.Age + "' , " +
>"Length = " + f.Length + ", " +
>"PlacedinTankID = " + f.Tank + ", " +
>"ScientificName = '" + f.SciName + "', " +
>"CommonName = '" + f.CommonName + "', " +
>"GivenName = '" + f.GivenName + "', " +
>"Origin = '" + f.Origin + "', " +
>"DatePlacedinTank = #2005-01-01 11:18:15 PM#, " +
>"ConditionWhenpurchased = '" + f.PurchaseCondition + "', " +
>"Food = '" + f.Food + "', " +
>// "DateExpired = '" + f.DateOfDeath + "', " +
>"DeathAge = '" + deathAge + "', " +
>"ProbableCause = '" + f.CauseOfDeath + "', " +
>"UnusualConditions = '" + f.UnusualConditions + "', " +
>// "PurchaseDate = '" + f.PurchaseDate + "', " +
>"Comments = '" + f.Comments + "' ";
>int rowsUpdated = 0;
>if (f.FishID > 0)
>{
>updateStatement += " WHERE FishID = " + f.FishID;
>}
>
>OleDbConnection fishMaintConnection = new OleDbConnection(strAccessConn);
>fishMaintConnection.Open();
>OleDbTransaction fishTrans = fishMaintConnection.BeginTransaction();
>OleDbCommand fishCmd = new OleDbCommand();
>fishCmd.Connection = fishMaintConnection;
>fishCmd.Transaction = fishTrans;
>
>try
>{
>fishCmd.CommandText = updateStatement;
>fishTrans.Commit();
>fishCmd.ExecuteNonQuery();
>
>// fishMaintConnection = new OleDbConnection(strAccessConn);
>// OleDbCommand updateCommand = new OleDbCommand(updateStatement,
>fishMaintConnection);
>// updateCommand.Parameters.Clear();
>// OleDbParameter parm = new OleDbParameter();
>// parm.ParameterName = "@TankDate";
>// parm.Value = DateTime.Now.Date.ToString();
>// parm.OleDbType = OleDbType.DBDate;
>// MessageBox.Show(parm.Value.ToString());
>// updateCommand.Parameters.Add(parm);
>// fishMaintConnection.Open();
>// rowsUpdated = updateCommand.ExecuteNonQuery();
>}
>catch (Exception ex)
>{
>MessageBox.Show(ex.Message + "\n\n" + updateStatement + "\n\n" + "Error::: "
>+ ex.GetBaseException(), "DB Error");
>}
>fishCmd.Dispose();
>fishMaintConnection.Close();
>MessageBox.Show("Rows Updated/Added: " + rowsUpdated, "DB Updated");
>}
>}
>}
.

Loading