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.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 this question

Oracle Databinding on Formview.

Peter K
You can actually mark your own post as an answer too. Click "mark as answer".
biozal
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
MoHaN_GoPaL
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.
Oracle Databinding on Formview.

Answer this question

Oracle Databinding on Formview.

Hot Topic

.NET Development

Oracle Databinding on Formview.

Oracle Databinding on Formview.

Peter K

biozal

MoHaN_GoPaL