FBCommand Column Unknown?

Question

I'm using FBCommand to insert data into a single table in ASP.NET and using the <defaultConnectionFactory type="FirebirdSql.Data.EntityFramework6.FbConnectionFactory, EntityFramework.Firebird" />

In my insert method, I create the following command:

INSERT INTO PERSONEN ( NACHNAME, VORNAME, GEBURTSDATUM, STRASSE, PLZ, ORT, LAND, TELEFONNUMMER, EMAIL) 
VALUES (:Nachname, :Vorname, :Geburtsdatum, :Strasse, :PLZ, :Ort, :Land, :Telefonnummer, :Email) 
RETURNING ID into :Returnvalue;

(I'm using : here instead of @, because when I used @ for the parameters, it gave me an unknown token "@" error)

When it tries to run the command as "ExecuteNonQuery", I get the SQL error -206 Column Unknown NACHNAME

I'm convinced theres no typo here: https://gyazo.com/6366c37e86a2073a157b38bd732e3ecd

I have no clue what the error could be. I even followed the FAQ from the Firebird homepage (where strangely they add parameters with @, but that's another story)

Here's the full code for the method:

private int InsertOrUpdateRecord(bool ForceNew)
{
    string command = "";
    if(ForceNew)
    {
        command = "INSERT INTO PERSONEN ( NACHNAME, VORNAME, GEBURTSDATUM, STRASSE, PLZ, ORT, LAND, TELEFONNUMMER, EMAIL)" +
        " VALUES (:Nachname, :Vorname, :Geburtsdatum, :Strasse, :PLZ, :Ort, :Land, :Telefonnummer, :Email)";
    }
    else
    {
        command = "UPDATE OR INSERT INTO PERSONEN (ID, NACHNAME, VORNAME, GEBURTSDATUM, STRASSE, PLZ, ORT, LAND, TELEFONNUMMER, EMAIL)" +
         " VALUES (:ID, :Nachname, :Vorname, :Geburtsdatum, :Strasse, :PLZ, :Ort, :Land, :Telefonnummer, :Email)";
    }
    command += " RETURNING ID into :Returnvalue;";
    FbCommand cmd = new FbCommand(command);
    cmd.CommandType = CommandType.Text;
   
    //ID ist ein Sonderfall, diese hat keine Textbox Controls so wie die Spalten in der folgenden For Schleife, da sie readonly ist.
    cmd.Parameters.AddWithValue(":" + gdvPersonDetailedData.Columns[0].HeaderText, gdvPersonDetailedData.Rows[0].Cells[0].Text);//@ID
    for (int i = 1; i < gdvPersonDetailedData.Columns.Count; i++)//Übrige Parameter
    {
        cmd.Parameters.AddWithValue(":" + gdvPersonDetailedData.Columns[i].HeaderText, ((TextBox)gdvPersonDetailedData.Rows[0].Cells[i].Controls[0]).Text);
    }

    cmd.Parameters.Add(":Returnvalue", FbDbType.Integer).Direction = ParameterDirection.Output;//Für den Returnwert
    cmd.Connection = c;
    if(cmd.Connection.State != ConnectionState.Open)
    {
        cmd.Connection.Open();
    }
    cmd.ExecuteNonQuery();
    return (int)cmd.Parameters[":Returnvalue"].Value;
}

Answer 1

The Firebird ADO.net Provider (FirebirdSql.Data.FirebirdClient), does not support : as a prefix for parameters, it only supports @ as prefix of parameters. Using : as the prefix is the cause for the error, the Column Unknown error is triggered by the :Nachname parameter, not by the column NACHNAME in the column list. The fact the colon is not included in the error message has to do with how colon prefixed names are parsed in Firebird (Firebird only supports named parameters in PSQL (Firebird's procedural language), but the parser for DSQL (dynamic SQL, Firebird's 'normal' query language) does process them).

The problem that you have when you use @ is caused by the inclusion of into @Returnvalue in your query. The INTO clause is only valid in PSQL, not in DSQL.

The correct way to accept the RETURNING value is by referencing it by the name of the column(s) in the RETURNING clause (in Firebird 3 you can also alias them):

// ...
command += " RETURNING ID";
// ...
cmd.Parameters.Add("ID", FbDbType.Integer).Direction = ParameterDirection.Output;
// ...
return (int)cmd.Parameters["ID"].Value;