<asp:SqlDataSource ID="charges_gv_datasource" runat="server" ConnectionString="edited"
ProviderName="System.Data.OracleClient" SelectCommand="WITH relevant_ids AS (SELECT ir.result_id AS relevant_result_id FROM inspection_result_tbl ir) SELECT ir.charge_id, LISTAGG(ir.result_id, ', ') WITHIN GROUP (ORDER BY ir.charge_id) AS result_ids, ch.charge_progress, ch.claim_verification, ch.hours_allowed, ch.sap_notification, ch.total_checked, ch.charge FROM inspection_result_tbl ir LEFT JOIN relevant_ids ON ir.result_id=relevant_ids.relevant_result_id LEFT JOIN charges_tbl ch ON ir.charge_id=ch.charge_id WHERE ir.charge_id IS NOT NULL AND ir.result_id=relevant_ids.relevant_result_id GROUP BY ir.charge_id, ch.charge_progress, ch.claim_verification, ch.hours_allowed, ch.sap_notification, ch.total_checked, ch.charge"
UpdateCommand="UPDATE [charges_tbl] SET [CLAIM_VERIFICATION] = :claim_verification, [CHARGE_PROGRESS] = :charge_progress, [SAP_NOTIFICATION] = :sap_notification WHERE [CHARGE_ID] = :charge_id">
</asp:SqlDataSource>
<asp:GridView ID="charges_gv" runat="server" AutoGenerateEditButton="true" DataSourceID="charges_gv_datasource" DataKeyNames="charge_id"
AutoGenerateColumns="false" Width="805px">
<Columns>
<asp:TemplateField HeaderText="Charge ID" SortExpression="charge_id">
<EditItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Eval("charge_id") %>'></asp:Label>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("charge_id") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<%--<asp:BoundField DataField="charge_id" HeaderText="Charge ID" ItemStyle-HorizontalAlign="Center" />--%>
<%--<asp:BoundField DataField="result_ids" HeaderText="Result ID" ItemStyle-HorizontalAlign="Center" />--%>
<asp:TemplateField HeaderText="Result ID" ItemStyle-HorizontalAlign="Center">
<ItemTemplate>
<asp:Label ID="resultid" runat="server" Text='<%# Bind("result_ids")%>' />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Total Checked" ItemStyle-HorizontalAlign="Center">
<ItemTemplate>
<asp:Label ID="totalCheckedCT" runat="server" Text='<%# Eval("total_checked")%>' />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Hours Allowed" ItemStyle-HorizontalAlign="Center">
<ItemTemplate>
<asp:Label ID="hoursAllowedCT" runat="server" Text='<%# Eval("hours_allowed")%>' />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Charge" ItemStyle-HorizontalAlign="Center">
<ItemTemplate>
<asp:Label ID="chargeCT" runat="server" Text='<%# Eval("charge")%>' />
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="claim_verification" HeaderText="Claim Verification" ItemStyle-HorizontalAlign="Center" />
<asp:BoundField DataField="charge_progress" HeaderText="Charge Progress" ItemStyle-HorizontalAlign="Center" />
<asp:BoundField DataField="sap_notification" HeaderText="SAP Notification" ItemStyle-HorizontalAlign="Center" />
</Columns>
</asp:GridView>
I have a data source and a grid view and I am trying to enable edit on the gridview rows. Everything works perfect, so i can click edit, the fields change to textboxes, the cancel and update buttons apear and work but if i change the values and click the update button I get the error :
ORA-01036: illegal variable name/number
Any ideas why? I have checked all the spellings in the updatecommand and it is all correct. Please help me.
Stack Trace:
[OracleException (0x80131938): ORA-01036: illegal variable name/number
]
System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc) +488568
System.Data.OracleClient.OracleParameterBinding.Bind(OciStatementHandle statementHandle, NativeBuffer parameterBuffer, OracleConnection connection, Boolean& mustRelease, SafeHandle& handleToBind) +1297
System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals) +1204
System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean needRowid, OciRowidDescriptor& rowidDescriptor) +656
System.Data.OracleClient.OracleCommand.ExecuteNonQuery() +158
System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +2790646
System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +4065616
System.Web.UI.WebControls.GridView.HandleUpdate(GridViewRow row, Int32 rowIndex, Boolean causesValidation) +1350
System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +626
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +70
System.Web.UI.WebControls.GridViewRow.OnBubbleEvent(Object source, EventArgs e) +110
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +70
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +29
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2981
There are many resons why this comes up. Sometimes because we don't use the Oracle tools and sometimes because of incorrect gridview markup.
since you do not require to update value of Column:
charge_id
, you cannot use a BoundField for the same unless you specify it inDataKeyNames
property ofSqlDataSource
.A BoundField if not specified in
DataKeyNames
,generates a bind variable which if not used in the update statement will result in ORA-01036. To avoid the error, either specify this field inDataKeyNames
property OR change this to a TemplateField and use Eval.so two possible cases I forsee:1.) Set the
DataKeyNames
property of your SqlDataSource tocharge_id
.2.) If option 1 doesn't suits then :
Seeing your markup, definitely you have your
<EditItemTemplate>
in place as you haven't shown the same for many of<asp:TemplateField>
items.Make sure then you put your
charge_id
column in an<asp:TemplateField>
. Use<asp:Lable>
so it won't be changed even in edit mode.Seeing the Stack Trace provided Later On, : Better to Use Oracle Developer Tools (ODT) for VS and ODP.NET since they are better integrated with .NET and Oracle.
ODP.NET
usesOracle.DataAccess.Client
namespace.System.Data.OracleClient
belongs to the MS provider for Oracle. The connection string in theweb.config
needs to use theOracle.DataAccess.Client
as shown below:Go through this complete post for your reference: https://forums.oracle.com/message/2368331?#2366331http://forums.oracle.com/forums/thread.jspa?threadID=62196