Oracle Databinding on Formview.

Question

Ok, Love VS2005, but it's not that Oracle friendly at first sight.

I can create a FormView bound to a SqlDataSource, and Bind() fields to view the data (I have to manually create the query because of [] around each field, but that's part of the incompatibility...), but anyway, the viewing works fine. This is ODBC->Oracle 9i, Feb CTP.

I cannot get the update to work though.

My UpdateCommand is:

UpdateCommand="UPDATE MYTAB SET ID = @ID WHERE (KEYID = @KEYID)"

This follows an example found at the link below, so am using the CommandName="Update" to run the update from within an EditItemTemplate that displays data bound to the SQLDataSource.
http://winfx.msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/winfx/ref/ns/system.web.ui.webcontrols/e/formviewmode/formviewmode.aspSo... how do I bind data and update it to an Oracle database? I've tried using : as the bind prefix too, since Oracle typically uses that).

Thanks 

Answer 1

Let me answer my own question...

You use an UpdateCommand like this:

UpdateCommand="UPDATE MYTAB SET ID = :ID WHERE (KEYID = :KEYID)"

(or Original_KEYID)

And include an UpdateParameters section like this:

 <UpdateParameters>
 <asp: Parameter Name="ID"/>
 </UpdateParameters>

Ensuring that you are using a Text='<%# Bind("ID") %>' on the relevant text boxes.

Now if only 'Open DataBase Connectivity' didn't use database specific binding parameters so we could write SELECT X FROM Y; and UPDATE Y SET X=Z; and have such a simple thing work against multiple databases.  I hate c r a p like this.

Answer 2

You can actually mark your own post as an answer too. Click "mark as answer".

Answer 3

I get the following error message when trying to Update from a DataGrid in
Visual Studio 2005 with an Oracle database:

ORA-01036: illegal variable name/number

Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information about
the error and where it originated in the code.

Exception Details: System.Data.OracleClient.Oracl­eException: ORA-01036:
illegal variable name/number

Below is my revelant code :

<asp:SqlDataSource
               id="SqlDataSource1"
               runat="server"
               ConnectionString="<%$ ConnectionStrings:FTTCSDB %>"
               ProviderName="System.Data.Orac­leClient"
               SelectCommand="SELECT * FROM FTTCS_PROJECT_TRACKER"
               UpdateCommand="
                    UPDATE FTTCS_PROJECT_TRACKER
                    SET
                         STATE = :STATE,
                         DISTRICT = :DISTRICT,
                         OSPE_SPOC = :OSPE_SPOC,
                         CELLSITE_ADDRESS = :CELLSITE_ADDRESS,
                         CELLSITE_ADDRESS_CORRECTIONS =
:CELLSITE_ADDRESS_CORRECTIONS,
                         FIBER_JOB_NO = :FIBER_JOB_NO,
                         FIBER_ECD = :FIBER_ECD,
                         FIBER_DATE_COMPLETE = :FIBER_DATE_COMPLETE,
                         CO_JOB_NO = :CO_JOB_NO,
                         CO_EQUIP_ECD = :CO_EQUIP_ECD,
                         CO_EQUIP_DATE_COMPLETE = :CO_EQUIP_DATE_COMPLETE,
                         RT_JOB_NO = :RT_JOB_NO,
                         RT_EQUIP_ECD = :RT_EQUIP_ECD,
                         RT_EQUIP_DATE_COMPLETE = :RT_EQUIP_DATE_COMPLETE
                    WHERE (REC_ID = :REC_ID)">
               <UpdateParameters>
                    <asp:Parameter Name="STATE" Direction="InputOutput"
DefaultValue=" "/>
                    <asp:Parameter Name="DISTRICT" Direction="InputOutput"
DefaultValue=" "/>
                    <asp:Parameter Name="OSPE_SPOC" Direction="InputOutput"  
DefaultValue=" "/>
                    <asp:Parameter Name="CELLSITE_ADDRESS"
Direction="InputOutput" DefaultValue=" "/>
                    <asp:Parameter Name="CELLSITE_ADDRESS_CORRECT­IONS"
Direction="InputOutput" DefaultValue=" " />
                    <asp:Parameter Name="FIBER_JOB_NO"
Direction="InputOutput" DefaultValue=" " />
                    <asp:Parameter Name="FIBER_ECD" Direction="InputOutput"
DefaultValue=" "/>
                    <asp:Parameter Name="FIBER_DATE_COMPLETE"
Direction="InputOutput" DefaultValue=" "/>
                    <asp:Parameter Name="CO_JOB_NO" Direction="InputOutput"
DefaultValue=" " />
                    <asp:Parameter Name="CO_EQUIP_ECD"
Direction="InputOutput" DefaultValue=" "/>
                    <asp:Parameter Name="CO_EQUIP_DATE_COMPLETE"
Direction="InputOutput" DefaultValue=" "/>
                    <asp:Parameter Name="RT_JOB_NO" Direction="InputOutput"
DefaultValue=" " />
                    <asp:Parameter Name="RT_EQUIP_ECD"
Direction="InputOutput" DefaultValue=" "/>
                    <asp:Parameter Name="RT_EQUIP_DATE_COMPLETE"
Direction="InputOutput" DefaultValue=" " />
             </UpdateParameters>
          </asp:SqlDataSource>

I have also used "@" for the ":". Same result. Any Ideas?

Thanks,
Tony