当前位置: 动力学知识库 > 问答 > 编程问答 >

c# - ORA-01036 after rewriting an Oracle insert command

问题描述:

I am reworking legacy code and came to this query:

sqlDS.InsertCommand = "INSERT INTO SCHEMA.TABLE " +

"(ID, DATUM, ID_STATE, G_AM, G_VON, DATUM_BEW) " +

"VALUES (" + ((strukturen.Login)Session["svar_bew"]).ID_Bew.ToString() +

", TO_DATE('" + _datum + "', 'DD.MM.YYYY HH24:MI:SS'), 2, TO_DATE('" + lbl_bew.Text +

"', 'DD.MM.YYYY'), 'ID" + ((strukturen.Login)Session["svar_bew"]).ID_Bew.ToString() +

"', TO_DATE('" + _datum + "', 'DD.MM.YYYY HH24:MI:SS'))";

Which works, but I want this abomination rewritten into a paramerterized format, for obious reasons.

Now this is my progress so far:

sqlDS.InsertCommand = "INSERT INTO SCHEMA.TABLE (ID, DATUM, ID_STATE, G_AM, G_VON, DATUM_BEW) " +

"VALUES (:Id_Bew, TO_DATE(':Datum', 'DD.MM.YYYY HH24:MI:SS'), 2, TO_DATE(':BewAm', 'DD.MM.YYYY'), 'ID' || :Id_Bew, TO_DATE(':Datum', 'DD.MM.YYYY HH24:MI:SS'))";

sqlDS.InsertParameters.Add("Id_Bew", ((strukturen.Login)Session["svar_bew"]).ID_Bew.ToString());

sqlDS.InsertParameters.Add("Datum", _datum);

sqlDS.InsertParameters.Add("BewAm", lbl_bew.Text);

This is the table in question:

COLUMN | TYPE

-----------------------

ID | NUMBER

DATUM | DATE

/* Columns | ommited */

ID_STATE | NUMBER

G_AM | DATE

G_VON | CHAR(10 BYTE)

DATUM_BEW | DATE

The variables follow no fixed schema yet, so they were declared in German, never mind that in this case.

The problem is, that this new code now throws an ORA-01036 an I suspect that it is at this location:

'ID" + ((strukturen.Login)Session["svar_bew"]).ID_Bew.ToString() --> 'ID' || :Id_Bew

In the original SQL query the string "ID" is concatinated with an numeric ID which is pulled out of a session variable. My suspicion is, that I concatinated it the wrong way in my paramerterized query and botched up the syntax.

Question is: How can I resolve this error and bring this insert command into a proper format?

网友答案:

Bind Variables Should be defined without embedding in single quotes.

':bind_variable' ---> Wrong (Treated as plain string instead)
:bind_variable ---> Right

So, When you attempted to add a bind parameter, it threw ORA-01036: illegal variable name/number

 sqlDS.InsertCommand = "INSERT INTO SCHEMA.TABLE (ID, DATUM, ID_STATE, G_AM, G_VON, DATUM_BEW) " +
                          "VALUES (:Id_Bew, TO_DATE(:Datum, 'DD.MM.YYYY HH24:MI:SS'), 2, TO_DATE(:BewAm, 'DD.MM.YYYY'), 'ID' || :Id_Bew, TO_DATE(:Datum, 'DD.MM.YYYY HH24:MI:SS'))";
                            sqlDS.InsertParameters.Add("Id_Bew", ((strukturen.Login)Session["svar_bew"]).ID_Bew.ToString());
                            sqlDS.InsertParameters.Add("Datum", _datum);
                            sqlDS.InsertParameters.Add("BewAm", lbl_bew.Text);
分享给朋友:
您可能感兴趣的文章:
随机阅读: