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;
}

i do not think you actully tried exactly the same query in FlameRobin, because your `returning` clause is broken — Arioch 'The, Feb 15 '21 at 09:07

Mark Rotteveel · Accepted Answer · 2021-02-15T09:26:03.627

2

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;

edited Feb 15 '21 at 09:26

answered Feb 15 '21 at 08:37

Mark Rotteveel

100,966
191
140
197

i tried using @ for all my parameters at first and then it only said @ is an invalid/unknown token. I fixed my issue by writing stored procedures. That did the trick. And I hope i never have to touch firebird or flamerobin ever again. They have both scorned and failed me. – Moonpaw Feb 15 '21 at 12:53
@Moonpaw If you tried to use `@` in FlameRobin, then that won't work. Firebird itself only knows positional parameters (`?`), the use of `@` is specific to the FirebirdSql.Data.FirebirdClient, which will translate between those named parameters and positional parameters. – Mark Rotteveel Feb 15 '21 at 17:45
@Moonpaw Anyway, sorry to hear that you don't like problems. I'm curious, exactly how did Firebird scorn you? – Mark Rotteveel Feb 15 '21 at 18:07

FBCommand Column Unknown?

1 Answers1