Update Syntax for Oracle 10g using ASP.NET

Question

I have a GridView with an Update button. I want to update a field in the database but I think the '@' in the code below is causing the problem in my ASP .NET page. What can be done within the grid or in the update (UpdateCommand) statement? Note, I get an Ora 00936 error.

<asp:SqlDataSource ID="dsBooks" runat="server"
            ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"
            ConnectionString="<%$ ConnectionStrings:ConnectionString %>"         
            SelectCommand="SELECT OBJECTID, TRACT, GIS_ACRES, COMMENTS, PDF_STORAGE FROM CampusDev.CU_POLY ORDER BY OBJECTID"
            UpdateCommand="UPDATE CampusDev.CU_POLY SET COMMENTS='just atest' WHERE OBJECTID=@OBJECTID"

Answer 1

Oracle usually has : instead of @:

WHERE OBJECTID = :OBJECTID

Or perhaps objectid is a reserved word, which you can escape with " in Oracle:

WHERE "OBJECTID" = :OBJECTID

Or perhaps you'd have to specify a parameter to ASP.NET:

 <asp:SqlDataSource ...>
     <UpdateParameters>
         <asp:Parameter Type="Int32" Name="ObjectId" />
     </UpdateParameters>
 </asp:SqlDataSource>

Answer 2

The ":OBJECTID" in Oracle parlance is a Bind Variable.

I'm ignorant of asp.net semantics, but you will want to use bind variables here. This link should provide a more complete explanation, but basically it's this:

cmd.Parameters.Add(new OracleParameter(“OBJECTID″, @OBJECTID));
UpdateCommand="UPDATE CampusDev.CU_POLY 
       SET COMMENTS='just atest' WHERE OBJECTID=:OBJECTID"

Then execute your command.

Always use bind variables where possible in production code - it allows Oracle to avoid hard parsing of the SQL statement.

Also, the name of the Bind Variable is unimportant to Oracle. The order in which it appears is the important aspect. You could just as easily say

WHERE OBJECTID=:1

with the same effect.