Why the ExecuteNonQuery catch exception {"validation error for column \"ORGTABLE\".\"FIKEYID\", value \"* null *\""}

Question

Why the ExecuteNonQuery catch exception {"validation error for column \"ORGTABLE\".\"FIKEYID\", value \"* null *\""}

string stValuesPlaceHolder = "@p0";
for (int iii = 1; iii < liststFieldValuesNoKeyId.Count; iii++)
    stValuesPlaceHolder += ", @p" + (iii).ToString();

FbTransaction fbTransaction = fbConn.BeginTransaction();
FbCommand fbCmd = new FbCommand("INSERT INTO " + stTableName + "(" + stFieldNamesNoKeyId + ") VALUES ( " + stValuesPlaceHolder + " )", fbConn, fbTransaction);

for (int iii = 0; iii < liststFieldValuesNoKeyId.Count; iii++) {
    string stPlaceHolder = "@p" + (iii).ToString();
    string stValue = liststFieldValuesNoKeyId[iii];
    fbCmd.Parameters.AddWithValue(stPlaceHolder, stValue);
}

fbCmd.ExecuteNonQuery();
fbTransaction.Commit();

The stTableName is OrgTable.

The fields names are:

fstPriority, fstInfo, fstDateCreated, fstDateModified, fiKeyID.

The field definitions are:

fstPriority VARCHAR(30), fstInfo VARCHAR(100), fstDateCreated VARCHAR(30), fstDateModified VARCHAR(30), fiKeyID INTEGER PRIMARY KEY

---

In this section of the code:

stFieldNamesNoKeyId = "fstPriority, fstInfo, fstDateCreated, fstDateModified".

stValuesPlaceHolder = "@p0, @p1, @p2, @p3"

Four fbCmd.Parameters.AddWithValue:

stPlaceHolder = "@p0" ... stValue = "1st value";

stPlaceHolder = "@p1" ... stValue = "2nd value";

stPlaceHolder = "@p2" ... stValue = "3rd value";

stPlaceHolder = "@p3" ... stValue = "4th value";

I did not add a value for fiKeyID as it as the PRIMARY KEY.

Answer 1

I did not add a value for fiKeyID as it as the PRIMARY KEY.

So you try to insert a NULL primary key. This is not allowed.

http://www.firebirdsql.org/manual/nullguide-keys.html

NULLs are never allowed in primary keys. A column can only be (part of) a PK if it has been defined as NOT NULL, either in the column definition or in a domain definition.

Then, you might want to ask server for auto-generating IDs. There are few ways of doing it.

Firebird 3 comes with auto-inc column type, for example. Which is a syntactic sugar over tools that were explicitly used by database developer before.

Firebird 2 and prior versions used GENERATORS (aka SQL SEQUENCE) to achieve it.

You have to make a BEFORE-INSERT (or BEFORE-INSERT-OR-UPDATE) trigger on the table, that would fill the ID field from the generator, if the ID field was NULL. http://www.firebirdfaq.org/faq29/

CREATE GENERATOR gen_t1_id;
SET GENERATOR gen_t1_id TO 0;
set term !! ;
 CREATE TRIGGER T1_BI FOR T1
 ACTIVE BEFORE INSERT POSITION 0
 AS
 BEGIN
 if (NEW.ID is NULL) then NEW.ID = GEN_ID(GEN_T1_ID, 1);
 END!!
set term ; !!

There it boils down to your SQL access library. Because typically after you inserted the row - you have to know its ID.

If you do not care about that ID of newborn row, you may skip the rest.

But if you want to both insert the row and know its ID then it boils down to another choice.

Low-tech SQL-only libraries would force you to take a doubletrip:

• SELECT GEN_ID(GEN_T1_ID, 1) FROM RDB$DATABASE or SELECT NEXT VALUE FOR GEN_T1_ID FROM RDB$DATABASE would reserve you a free token, then you would explicitly assign your ID PK-column to that value and insert it together with data columns, bypassing the trigger.

• Or with advanced SQL libraries you may ask Firebird to auto-calculate value and report it to you: INSERT INTO tablename(data1,data2,dataq3) VALUES (1,2,3) RETURNING id. See https://en.wikipedia.org/wiki/Insert_(SQL)#Retrieving_the_key

Whether you need to learn the inserted ID or not, and whether your SQL library supports INSERT-RETURNING command or not - it is up to you to decide.

However when I do Google search ( it is www.google.com ) it comes with many links about C# Firebird Insert Returniung for many different C# SQL libraries, and again only you can tell which one you use. For few examples from different libs:

• http://www.ibprovider.com/eng/documentation/firebird_21_adonet.html
• http://www.sql.ru/forum/actualutils.aspx?action=gotomsg&tid=720869&msg=8075816
• Retrieve last id from Firebird db table
• Firebird insert...returning asp.net
• https://social.msdn.microsoft.com/Forums/en-US/e7099cfb-7809-460a-aae9-79a2bd703454/how-i-can-return-the-id-after-insert-a-record-into-a-database-firebird-25-with-linq?forum=adodotnetentityframework

et cetera

Answer 2

Definitions:

public const string stMAIN_TABLE_NAME = " OrgTable ";
public const string stDELETED_TABLE_NAME = "  BackupTable ";

public const string stFIELD_DEFINITIONS = " fstPriority VARCHAR(30)" + 
                                          ", fstInfo VARCHAR(100)" +
                                          ", fstDateCreated VARCHAR(30)" +
                                          ", fstDateModified VARCHAR(30)" +
                                          ", fiKeyID INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY ";

public const string stFIELD_NAMES_NO_KEY_ID = " fstPriority" + 
                                              ", fstInfo" + 
                                              ", fstDateCreated" + 
                                              ", fstDateModified ";

public const string stFIELD_NAMES_KEY_ID = " fiKeyID ";

public const string stFIELD_NAMES = stFIELD_NAMES_NO_KEY_ID + ", " + stFIELD_NAMES_KEY_ID;

Code:

//------------------------------
static private bool boCreateDatabaseTables(string stPathFilename, 
                                           string stUserID, 
                                           string stPassword, 
                                           List<string> liststTableNames, 
                                           List<string> liststFieldDefinitions) 
{
  bool boErrorFlag = false;
  int iTablesCount = liststTableNames.Count();
  string stOpenConn = new FbConnectionStringBuilder {
    Database = stPathFilename,
    UserID = stUserID,
    Password = stPassword,
    ServerType = FbServerType.Embedded,
    ClientLibrary = stCLIENT_LIBRARY
    }.ToString();
  using (FbConnection fbConn = new FbConnection(stOpenConn)) {
    try {
      fbConn.Open();

      FbTransaction fbTransaction = fbConn.BeginTransaction();
      for (int ii = 0; ii < iTablesCount; ii++) {
        string stSql = "CREATE TABLE " + liststTableNames[ii] + "( " + liststFieldDefinitions[ii] + ")";
        FbCommand fbCmd = new FbCommand(stSql, fbConn, fbTransaction);
        fbCmd.ExecuteNonQuery();
      }
      fbTransaction.Commit();
    }
    catch (Exception ex) {
      boErrorFlag = true;
      MessageBox.Show("catch ... GlobalsFirebird ... boCreateDatabaseTables ... " + ex.Message);
    }
}
return boErrorFlag;
}//boCreateDatabaseTables
//------------------------------
//------------------------------
static public bool boAddRow(string stPathFilename,
                            string stUserID,
                            string stPassword,
                            string stTableName,
                            string stFieldNamesNoKeyId,
                            string stFieldNamesKeyId,
                            List<string> liststFieldValuesNoKeyId) 
{
  bool boErrorFlag = false;
  string stOpenConn = new FbConnectionStringBuilder {
    Database = stPathFilename,
    UserID = stUserID,
    Password = stPassword,
    ServerType = FbServerType.Embedded,
    ClientLibrary = stCLIENT_LIBRARY
  }.ToString();

  using(FbConnection fbConn = new FbConnection(stOpenConn)) {
    fbConn.Open();
    try {
      string stValuesPlaceHolder = "@p0";
      for (int iii = 1; iii < liststFieldValuesNoKeyId.Count; iii++)
        stValuesPlaceHolder += ", @p" + (iii).ToString();
      FbTransaction fbTransaction = fbConn.BeginTransaction();
      string stCmd = "INSERT INTO " + stTableName + "(" + stFieldNamesNoKeyId + ") VALUES ( " + stValuesPlaceHolder + " ) RETURNING  " + stFieldNamesKeyId;
      FbCommand fbCmd = new FbCommand(stCmd, fbConn, fbTransaction);

      for (int iii = 0; iii < liststFieldValuesNoKeyId.Count; iii++) {
        string stPlaceHolder = "@p" + (iii).ToString();
        string stValue = liststFieldValuesNoKeyId[iii];
        fbCmd.Parameters.AddWithValue(stPlaceHolder, stValue);
      }
      fbCmd.Parameters.Add(new FbParameter() { Direction = System.Data.ParameterDirection.Output });
      fbCmd.ExecuteNonQuery();
      fbTransaction.Commit();
    }
    catch (Exception ex) {
      boErrorFlag = true;
      MessageBox.Show("catch ... GlobalsFirebird ... boAddRow ... " + ex.Message);
    }
  }
  return boErrorFlag;
}//boAddRow
//------------------------------