Oracle Databinding on Formview.
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.asp
So... 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
[1159 byte] By [
dpilcher] at [2008-1-12]
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.
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.OracleException: ORA-01036:
illegal variable name/number
Below is my revelant code :
<asp:SqlDataSource
id="SqlDataSource1"
runat="server"
ConnectionString="<%$ ConnectionStrings:FTTCSDB %>"
ProviderName="System.Data.OracleClient"
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_CORRECTIONS"
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
