Wednesday, April 10, 2019

C#: Write to DB with OleDbCommand and positional Parameters


string sql = "UPDATE Members SET Age = ?, Email = ? WHERE Name = ?";

OleDbConnection conn = new OleDbConnection(connectionString);

int rowsAffected = 0;

try {
  conn.Open();

  OleDbCommand comm = new OleDbCommand(sql, conn);
 
  comm.CommandType = CommandType.Text;
 
  // when CommandType is set to Text, parameter names are not important,
  // but the position of the paramter matters. *
  comm.Parameters.AddWithValue("parm1", intAgeValue);
  comm.Parameters.AddWithValue("parm2", strEmailValue);
  comm.Parameters.AddWithValue("parm3", strNameValue);
 
  rowsAffected = comm.ExecuteNonQuery();
}
catch (Exception ex) {
  // handle ex
}
finally {
  conn.Close();
}



According to Microsoft, the OLE DB.NET Provider does not support named parameters for passing parameters to an SQL Statement when CommandType is set to Text. In this case, the question mark (?) placeholder must be used.  Therefore, the order in which OleDbParameter objects are added to the OleDbParameterCollection must directly correspond to the position of the question mark placeholder for the parameter. Ref: https://docs.microsoft.com/en-us/dotnet/api/system.data.oledb.oledbcommand.commandtext?view=netframework-4.7.2#remarks

No comments:

 
Get This <