ORA-01036: illegal variable name/number in GridView when updating
Hello,
I have a problem with gridview that is connected to Oracle relational
database. The SELECT function of GridView works fine but when i am trying to
update just some of the fields I get ORA-01036.
I figured out that when i create a gridview for one table the update works
fine, but when i make some joins in a query for a select function of gridview
so that i can get some descriptions that are i related tables of a fact table
the update doen't work. I found numerous of forums issued this problem , but
I couldnt found the solution.
P.S. The delete and insert functions of gridview are not enabled!
CODE:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs"
Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Untitled Page</title>
<style type="text/css">
p, body, td, tr {
font-family: Tahoma;
font-size: 9pt;
}
.Error {
font-size: 9pt;
color: Red;
}
.btnCssClass1 {
font-size: 7pt;
margin: 0;
height: 18px;
padding: 0;
border: 1;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConflictDetection="CompareAllValues"
ConnectionString="<%$ ConnectionStrings:oracleOlap %>"
DeleteCommand='DELETE FROM "BI_ALP_NAR_FACT" WHERE "BI_ALP_F_DATE_ID" =
:original_BI_ALP_F_DATE_ID AND "BI_ALP_ART_H_ID" = :original_BI_ALP_ART_H_ID
AND "BI_ALP_KUP_ID" = :original_BI_ALP_KUP_ID AND "BI_ALP_NAR_ID" =
:original_BI_ALP_NAR_ID AND "BI_ALP_DATE2" = :original_BI_ALP_DATE2 AND
"BI_ALP_NADZ_4" = :original_BI_ALP_NADZ_4 AND "BI_ALP_NADZ_3" =
:original_BI_ALP_NADZ_3 AND "BI_ALP_NADZ_2" = :original_BI_ALP_NADZ_2 AND
"BI_ALP_NADZ_1" = :original_BI_ALP_NADZ_1 AND "BI_ALP_STAT_NAR" =
:original_BI_ALP_STAT_NAR'
InsertCommand='INSERT INTO "BI_ALP_NAR_FACT"
("BI_ALP_F_DATE_ID", "BI_ALP_ART_H_ID", "BI_ALP_KUP_ID", "BI_ALP_NAR_ID",
"BI_ALP_DATE2", "BI_ALP_NADZ_4", "BI_ALP_NADZ_3", "BI_ALP_NADZ_2",
"BI_ALP_NADZ_1", "BI_ALP_STAT_NAR") VALUES (:BI_ALP_F_DATE_ID,
:BI_ALP_ART_H_ID, :BI_ALP_KUP_ID, :BI_ALP_NAR_ID, :BI_ALP_DATE2,
:BI_ALP_NADZ_4, :BI_ALP_NADZ_3, :BI_ALP_NADZ_2, :BI_ALP_NADZ_1,
:BI_ALP_STAT_NAR)'
OldValuesParameterFormatString="original_{0}" ProviderName="<%$
ConnectionStrings:oracleOlap.ProviderName %>"
SelectCommand='SELECT A.BI_ALP_F_DATE_ID, B.BI_ALP_DATE,
A.BI_ALP_ART_H_ID, C.ART_H_SIFRA, A.BI_ALP_KUP_ID, D.BI_ALP_KUP_SIFRA,
A.BI_ALP_NAR_ID, E.BI_ALP_NAR_SIFRA, E.BI_ALP_NAR_DATE, A.BI_ALP_STAT_NAR,
A.BI_ALP_NADZ_1, A.BI_ALP_NADZ_2, A.BI_ALP_NADZ_3, A.BI_ALP_NADZ_4 FROM
BI_ALP_NAR_FACT A LEFT OUTER JOIN BI_ALP_DATUM B ON B.BI_ALP_DAT_ID =
A.BI_ALP_F_DATE_ID LEFT OUTER JOIN BI_ALP_ART_H C ON C.ART_H_ID =
A.BI_ALP_ART_H_ID LEFT OUTER JOIN BI_ALP_KUP_H D ON D.BI_ALP_KUP_ID =
A.BI_ALP_KUP_ID LEFT OUTER JOIN BI_ALP_NAR E ON E.BI_ALP_NAR_ID =
A.BI_ALP_NAR_ID'
UpdateCommand='UPDATE BI_ALP_NAR_FACT SET BI_ALP_NADZ_4 =
:BI_ALP_NADZ_4, BI_ALP_NADZ_3 = :BI_ALP_NADZ_3, BI_ALP_NADZ_2 =
:BI_ALP_NADZ_2, BI_ALP_NADZ_1 = :BI_ALP_NADZ_1, BI_ALP_STAT_NAR =
:BI_ALP_STAT_NAR WHERE (BI_ALP_F_DATE_ID = :original_BI_ALP_F_DATE_ID) AND
(BI_ALP_ART_H_ID = :original_BI_ALP_ART_H_ID) AND (BI_ALP_KUP_ID =
:original_BI_ALP_KUP_ID) AND (BI_ALP_NAR_ID = :original_BI_ALP_NAR_ID) AND
(BI_ALP_DATE2 = :original_BI_ALP_DATE2) AND (BI_ALP_NADZ_4 =
:original_BI_ALP_NADZ_4) AND (BI_ALP_NADZ_3 = :original_BI_ALP_NADZ_3) AND
(BI_ALP_NADZ_2 = :original_BI_ALP_NADZ_2) AND (BI_ALP_NADZ_1 =
:original_BI_ALP_NADZ_1) AND (BI_ALP_STAT_NAR = :original_BI_ALP_STAT_NAR)'>
<DeleteParameters>
<asp:Parameter Name="original_BI_ALP_F_DATE_ID"
Type="Decimal" />
<asp:Parameter Name="original_BI_ALP_ART_H_ID"
Type="Decimal" />
<asp:Parameter Name="original_BI_ALP_KUP_ID" Type="Decimal" />
<asp:Parameter Name="original_BI_ALP_NAR_ID" Type="Decimal" />
<asp:Parameter Name="original_BI_ALP_DATE2" Type="Decimal" />
<asp:Parameter Name="original_BI_ALP_NADZ_4" Type="Decimal" />
<asp:Parameter Name="original_BI_ALP_NADZ_3" Type="Decimal" />
<asp:Parameter Name="original_BI_ALP_NADZ_2" Type="Decimal" />
<asp:Parameter Name="original_BI_ALP_NADZ_1" Type="Decimal" />
<asp:Parameter Name="original_BI_ALP_STAT_NAR"
Type="Decimal" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="BI_ALP_DATE2" Type="Decimal" />
<asp:Parameter Name="BI_ALP_NADZ_4" Type="Decimal" />
<asp:Parameter Name="BI_ALP_NADZ_3" Type="Decimal" />
<asp:Parameter Name="BI_ALP_NADZ_2" Type="Decimal" />
<asp:Parameter Name="BI_ALP_NADZ_1" Type="Decimal" />
<asp:Parameter Name="BI_ALP_STAT_NAR" Type="Decimal" />
<asp:Parameter Name="original_BI_ALP_F_DATE_ID"
Type="Decimal" />
<asp:Parameter Name="original_BI_ALP_ART_H_ID"
Type="Decimal" />
<asp:Parameter Name="original_BI_ALP_KUP_ID" Type="Decimal" />
<asp:Parameter Name="original_BI_ALP_NAR_ID" Type="Decimal" />
<asp:Parameter Name="original_BI_ALP_DATE2" Type="Decimal" />
<asp:Parameter Name="original_BI_ALP_NADZ_4" Type="Decimal" />
<asp:Parameter Name="original_BI_ALP_NADZ_3" Type="Decimal" />
<asp:Parameter Name="original_BI_ALP_NADZ_2" Type="Decimal" />
<asp:Parameter Name="original_BI_ALP_NADZ_1" Type="Decimal" />
<asp:Parameter Name="original_BI_ALP_STAT_NAR"
Type="Decimal" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="BI_ALP_F_DATE_ID" Type="Decimal" />
<asp:Parameter Name="BI_ALP_ART_H_ID" Type="Decimal" />
<asp:Parameter Name="BI_ALP_KUP_ID" Type="Decimal" />
<asp:Parameter Name="BI_ALP_NAR_ID" Type="Decimal" />
<asp:Parameter Name="BI_ALP_DATE2" Type="Decimal" />
<asp:Parameter Name="BI_ALP_NADZ_4" Type="Decimal" />
<asp:Parameter Name="BI_ALP_NADZ_3" Type="Decimal" />
<asp:Parameter Name="BI_ALP_NADZ_2" Type="Decimal" />
<asp:Parameter Name="BI_ALP_NADZ_1" Type="Decimal" />
<asp:Parameter Name="BI_ALP_STAT_NAR" Type="Decimal" />
</InsertParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server"
ConnectionString="<%$ ConnectionStrings:oracleOlap %>"
ProviderName="<%$ ConnectionStrings:oracleOlap.ProviderName %>"
SelectCommand='SELECT "BI_ALP_STATNAR_NAZIV", "BI_ALP_STATNAR_ID" FROM
"BI_ALP_STATUS_NAR"'>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource3" runat="server"
ConnectionString="<%$ ConnectionStrings:oracleOlap %>"
ProviderName="<%$ ConnectionStrings:oracleOlap.ProviderName %>"
SelectCommand='SELECT "BI_ALP_KRTO_NAZIV", "BI_ALP_KRTO_ID" FROM
"BI_ALP_NADZ_TOCKE"'>
</asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server"
AutoGenerateColumns="False" CellPadding="4"
DataKeyNames="BI_ALP_F_DATE_ID,BI_ALP_ART_H_ID,BI_ALP_KUP_ID,BI_ALP_NAR_ID"
DataSourceID="SqlDataSource1"
ForeColor="#333333" GridLines="None" AllowPaging="True"
AllowSorting="True">
<FooterStyle BackColor="#5D7B9D" Font-Bold="True"
ForeColor="White" />
<Columns>
<asp:CommandField ShowEditButton="True" />
<asp:TemplateField HeaderText="Status"
SortExpression="BI_ALP_STAT_NAR">
<EditItemTemplate>
<asp:DropDownList ID="DropDownListST2"
runat="server" DataSourceID="SqlDataSource2"
DataTextField="BI_ALP_STATNAR_NAZIV" DataValueField="BI_ALP_STATNAR_ID"
SelectedValue='<%# Bind("BI_ALP_STAT_NAR") %>'>
</asp:DropDownList>
</EditItemTemplate>
<ItemTemplate>
<asp:DropDownList ID="DropDownListST1"
runat="server" DataSourceID="SqlDataSource2"
DataTextField="BI_ALP_STATNAR_NAZIV" DataValueField="BI_ALP_STATNAR_ID"
Enabled="False" SelectedValue='<%# Bind("BI_ALP_STAT_NAR") %>'>
</asp:DropDownList>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Date"
SortExpression="BI_ALP_DATE">
<EditItemTemplate>
<asp:Label ID="Label101" runat="server" Text='<%#
Bind("BI_ALP_DATE") %>'></asp:Label>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label10" runat="server" Text='<%#
Bind("BI_ALP_DATE") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Item number"
SortExpression="ART_H_SIFRA">
<EditItemTemplate>
<asp:Label ID="Label91" runat="server" Text='<%#
Bind("ART_H_SIFRA") %>'></asp:Label>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label9" runat="server" Text='<%#
Bind("ART_H_SIFRA") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Customer number"
SortExpression="BI_ALP_KUP_SIFRA">
<EditItemTemplate>
<asp:Label ID="Label81" runat="server" Text='<%#
Bind("BI_ALP_KUP_SIFRA") %>'></asp:Label>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label8" runat="server" Text='<%#
Bind("BI_ALP_KUP_SIFRA") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Order number"
SortExpression="BI_ALP_NAR_SIFRA">
<EditItemTemplate>
<asp:Label ID="Label71" runat="server" Text='<%#
Bind("BI_ALP_NAR_SIFRA") %>'></asp:Label>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label7" runat="server" Text='<%#
Bind("BI_ALP_NAR_SIFRA") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Order date"
SortExpression="BI_ALP_NAR_DATE">
<EditItemTemplate>
<asp:Label ID="Label61" runat="server" Text='<%#
Bind("BI_ALP_NAR_DATE") %>'></asp:Label>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label6" runat="server" Text='<%#
Bind("BI_ALP_NAR_DATE") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Check point 1"
SortExpression="BI_ALP_NADZ_1">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList2" runat="server"
DataSourceID="SqlDataSource3" DataTextField="BI_ALP_KRTO_NAZIV"
DataValueField="BI_ALP_KRTO_ID" SelectedValue='<%# Bind("BI_ALP_NADZ_1") %>'>
</asp:DropDownList>
</EditItemTemplate>
<ItemTemplate>
<asp:DropDownList ID="DropDownList1" runat="server"
DataSourceID="SqlDataSource3" DataTextField="BI_ALP_KRTO_NAZIV"
DataValueField="BI_ALP_KRTO_ID" Enabled="False" SelectedValue='<%#
Bind("BI_ALP_NADZ_1") %>'>
</asp:DropDownList>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Check point 2"
SortExpression="BI_ALP_NADZ_2">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList4" runat="server"
DataSourceID="SqlDataSource3" DataTextField="BI_ALP_KRTO_NAZIV"
DataValueField="BI_ALP_KRTO_ID" SelectedValue='<%# Bind("BI_ALP_NADZ_2") %>'>
</asp:DropDownList>
</EditItemTemplate>
<ItemTemplate>
<asp:DropDownList ID="DropDownList3" runat="server"
DataSourceID="SqlDataSource3" DataTextField="BI_ALP_KRTO_NAZIV"
DataValueField="BI_ALP_KRTO_ID" Enabled="False" SelectedValue='<%#
Bind("BI_ALP_NADZ_2") %>'>
</asp:DropDownList>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Check point 3"
SortExpression="BI_ALP_NADZ_3">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList6" runat="server"
DataSourceID="SqlDataSource3" DataTextField="BI_ALP_KRTO_NAZIV"
DataValueField="BI_ALP_KRTO_ID" SelectedValue='<%# Bind("BI_ALP_NADZ_3") %>'>
</asp:DropDownList>
</EditItemTemplate>
<ItemTemplate>
<asp:DropDownList ID="DropDownList5" runat="server"
DataSourceID="SqlDataSource3" DataTextField="BI_ALP_KRTO_NAZIV"
DataValueField="BI_ALP_KRTO_ID" Enabled="False" SelectedValue='<%#
Bind("BI_ALP_NADZ_3") %>'>
</asp:DropDownList>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Check point 4"
SortExpression="BI_ALP_NADZ_4">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList8" runat="server"
DataSourceID="SqlDataSource3" DataTextField="BI_ALP_KRTO_NAZIV"
DataValueField="BI_ALP_KRTO_ID" SelectedValue='<%# Bind("BI_ALP_NADZ_4") %>'>
</asp:DropDownList>
</EditItemTemplate>
<ItemTemplate>
<asp:DropDownList ID="DropDownList7" runat="server"
DataSourceID="SqlDataSource3" DataTextField="BI_ALP_KRTO_NAZIV"
DataValueField="BI_ALP_KRTO_ID" Enabled="False" SelectedValue='<%#
Bind("BI_ALP_NADZ_4") %>'>
</asp:DropDownList>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<EditRowStyle BackColor="#999999" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True"
ForeColor="#333333" />
<PagerStyle BackColor="#284775" ForeColor="White"
HorizontalAlign="Center" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True"
ForeColor="White" />
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>
</form>
</body>
</html>
The only thing that i found is that the number of field in a select wuery
and update query is different, since i need to update just some of them. And
of course, some of the filed are only binding or joins to fact table. I think
that you will undrestand when you will lok at my code, there is no code
behind , that is it!
Date:Tue, 17 Jul 2007 15:08:00 -0700
Author:
|
