question

ws-5490 avatar image
0 Votes"
ws-5490 asked ws-5490 commented

Microsoft Access ODBC driver does not execute UPDATE statements

First of all, please understand that we are translating Japanese.

The following code does not perform UPDATE.
The updated line is returned as 0.
Please tell me the problem area and help me.


 public void Save(ProcessEntity process)
 {
     string insert = @"
 INSERT INTO Process 
 (ProcessId,CreateDate,SerialNumber,StaffName,Terminal,CompleteDate)
 VALUES 
 (?,?,?,?,?,?) 
 ";
     string update = @"
 UPDATE Process
 SET CreateDate = ?,
 SerialNumber = ?,
 StaffName = ?,
 Terminal = ?,
 CompleteDate = ?
 WHERE ProcessId = ?
 AND CreateDate =?
 AND SerialNumber = ?
 ";
     var args = new List<OdbcParameter>
     {
         new OdbcParameter("@ProcessId",process.ProcessId.Value),
         new OdbcParameter("@CreateDate",process.CreateDate.Value),
         new OdbcParameter("@SerialNumber",process.SerialNumber.Str),
         new OdbcParameter("@StaffName",process.StaffName.Str),
         new OdbcParameter("@Terminal",process.Terminal),
         new OdbcParameter("@CompleteDate",process.CompleteDate.Value),
     };

     OdbcHelper.ExecuteProcess(insert, update, args.ToArray());
 }



 internal static void ExecuteProcess(string insert, string update, OdbcParameter[] paramaters)
 {
     using (var connection = new OdbcConnection(ProcessConnection))
     using (var command = new OdbcCommand(update, connection))
     {
         connection.Open();
         if (paramaters != null)
         {
             command.Parameters.AddRange(paramaters);
         }
         if (command.ExecuteNonQuery() < 1)
         {
             command.CommandText = insert;
             command.ExecuteNonQuery();
         }
     }
 }
dotnet-csharpoffice-access-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Viorel-1 avatar image
0 Votes"
Viorel-1 answered ws-5490 commented

Try these modifications:

 string insert = @"
 INSERT INTO Process
 (CreateDate,SerialNumber,StaffName,Terminal,CompleteDate,ProcessId)
 VALUES
 (?,?,?,?,?,?)
 ";
 . . .
 var args = new List<OdbcParameter>
  {
      new OdbcParameter("@CreateDate",process.CreateDate.Value),
      new OdbcParameter("@SerialNumber",process.SerialNumber.Str),
      new OdbcParameter("@StaffName",process.StaffName.Str),
      new OdbcParameter("@Terminal",process.Terminal),
      new OdbcParameter("@CompleteDate",process.CompleteDate.Value),
      new OdbcParameter("@ProcessId",process.ProcessId.Value),
  };


· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The INSERT statement runs successfully.
However, the UPDATE statement does not execute.

  1.      if (command.ExecuteNonQuery() < 1)
    

At the above point, the number of updated rows will be 0 and we will proceed to INSERT.
I want to run UPDATE here.


0 Votes 0 ·

But, without testing, one can see the parameters are in a different order for insert/update,
so the modification from Viorel should set the right order, no ?

1 Vote 1 ·

I finally understood.
I didn't understand how the symbol was connected to the parameter.
In ACCESS, I learned that the order of SQL and the order of parameters must match.

This problem has afflicted me for a few days.
Thank you Castorix31.

       string insert = @"
   INSERT INTO Process 
   (StaffName,Terminal,CompleteDate,ProcessId,CreateDate,SerialNumber)
   VALUES 
   (?,?,?,?,?,?) 
   ";
       string update = @"
   UPDATE Process
   SET StaffName = ?,
   Terminal = ?,
   CompleteDate = ?
   WHERE ProcessId = ?
   AND CreateDate = ?
   AND SerialNumber = ?
   ";
       var args = new List<OdbcParameter>
       {
              new OdbcParameter("@StaffName",process.StaffName.Str),
              new OdbcParameter("@Terminal",process.Terminal),
              new OdbcParameter("@CompleteDate",process.CompleteDate.Value),
              new OdbcParameter("@ProcessId",process.ProcessId.Value),
              new OdbcParameter("@CreateDate",process.CreateDate.Value),
              new OdbcParameter("@SerialNumber",process.SerialNumber.Str),
       };
0 Votes 0 ·