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);
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments