Column Ambiguously Defined Error in Oracle Query
I have a code like this.
<script runat="server">
protected void Page_Load(object sender, EventArgs e)
{
Calculate.Visible = false;
MV_Label.Visible = false;
RISK_Label.Visible = false;
KV_Label.Visible = false;
}
protected void Calculate_Click(object sender, EventArgs e)
{
MV_Label.Visible = true;
RISK_Label.Visible = true;
Calculate.Visible = true;
KV_Label.Visible = true;
double sumMV = 0;
foreach (GridViewRow gvr in GridView1.Rows)
{
CheckBox cb = (CheckBox)gvr.FindControl("NameCheckBoxField2");
if (cb.Checked == true)
{
double amount = Convert.ToDouble(gvr.Cells[9].Text);
sumMV += amount;
}
}
MV_Label.Text = sumMV.ToString();
double sumRISK = 0;
foreach (GridViewRow gvr in GridView1.Rows)
{
CheckBox cb = (CheckBox)gvr.FindControl("NameCheckBoxField1");
if (cb.Checked == true)
{
double amount = Convert.ToDouble(gvr.Cells[7].Text);
sumRISK += amount;
}
}
RISK_Label.Text = sumRISK.ToString();
double sumKV = 0;
foreach (GridViewRow gvr in GridView1.Rows)
{
CheckBox cb = (CheckBox)gvr.FindControl("NameCheckBoxField3");
if (cb.Checked == true)
{
double amount = Convert.ToDouble(gvr.Cells[11].Text);
sumKV += amount;
}
}
KV_Label.Text= sumRISK.ToString();
}
protected void SendToGridview_Click(object sender, EventArgs e)
{
DateTime dt_stb;
Calculate.Visible = true;
MV_Label.Visible = true;
RISK_Label.Visible = true;
KV_Label.Visible = true;
string strQuery = string.Empty;
string ConnectionString = ConfigurationManager.ConnectionStrings["ora"].ConnectionString;
OracleConnection myConnection = new OracleConnection(ConnectionString);
string txtBoxText1 = ((TextBox)Page.FindControl("TextBox1")).Text;
if (txtBoxText1 != "")
{
strQuery = @"SELECT A.HESAP_NO, A.TEKLIF_NO1 || '/' || A.TEKLIF_NO2 AS TEKLIF, A.MUS_K_ISIM AS MUSTERI,
B.MARKA, C.SASI_NO, C.SASI_DURUM, D.TAS_MAR, NVL(RISK_SASI(A.TEKLIF_NO1, A.TEKLIF_NO2, C.URUN_SIRA_NO, C.SIRA_NO, :S_TARIH_B),0) AS RISK,
NVL(MV_SASI(A.TEKLIF_NO1, A.TEKLIF_NO2, C.SIRA_NO, C.URUN_SIRA_NO, :S_TARIH_B),0) AS MV,
MV_K(TEKLIF_NO1,TEKLIF_NO2, :S_TARIH_B) AS KV
FROM S_TEKLIF A, S_URUN B, S_URUN_DETAY C, KOC_KTMAR_PR D
WHERE A.TEKLIF_NO1 || A.TEKLIF_NO2 = B.TEKLIF_NO1 || B.TEKLIF_NO2
AND A.TEKLIF_NO1 || A.TEKLIF_NO2 = C.TEKLIF_NO1 || C.TEKLIF_NO2
AND B.SIRA_NO = C.URUN_SIRA_NO
AND B.DISTRIBUTOR = D.DIST_KOD
AND B.MARKA = D.MARKA_KOD
AND B.URUN_KOD = D.TAS_KOD ";
}
string param = "";
foreach (ListItem l in CheckBoxList1.Items)
{
if (l.Selected)
{
param += string.Format("'{0}'", l.Value);
param += ",";
}
}
param = param.Remove(param.Length - 1);
strQuery = strQuery + " AND A.HESAP_NO IN (" + param + ")";
OracleCommand myCommand = new OracleCommand(strQuery, myConnection);
myCommand.CommandType = Syste开发者_如何学Gom.Data.CommandType.Text;
myCommand.Connection = myConnection;
myCommand.CommandText = strQuery;
dt_stb = DateTime.Parse(txtBoxText1);
myCommand.Parameters.AddWithValue(":S_TARIH_B", dt_stb);
myConnection.Open();
OracleDataReader dr = myCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
GridView1.DataSource = dr;
GridView1.DataBind();
GridView1.Visible = true;
myConnection.Close();
double sumMV = 0;
foreach (GridViewRow gvr in GridView1.Rows)
{
CheckBox cb = (CheckBox)gvr.FindControl("NameCheckBoxField2");
if (cb.Checked == true)
{
double amount = Convert.ToDouble(gvr.Cells[9].Text);
sumMV += amount;
}
}
MV_Label.Text = sumMV.ToString();
double sumRISK = 0;
foreach (GridViewRow gvr in GridView1.Rows)
{
CheckBox cb = (CheckBox)gvr.FindControl("NameCheckBoxField1");
if (cb.Checked == true)
{
double amount = Convert.ToDouble(gvr.Cells[7].Text);
sumRISK += amount;
}
}
RISK_Label.Text = sumRISK.ToString();
double sumKV = 0;
foreach (GridViewRow gvr in GridView1.Rows)
{
CheckBox cb = (CheckBox)gvr.FindControl("NameCheckBoxField3");
if (cb.Checked == true)
{
double amount = Convert.ToDouble(gvr.Cells[11].Text);
sumKV += amount;
}
}
KV_Label.Text = sumRISK.ToString();
}
</script>
<!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 runat="server">
<title></title>
<style type="text/css">
#form1
{
height: 729px;
width: 1083px;
}
.style1
{
width: 265px;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<br />
<img src="../images/Scania_Logo.gif" style="height: 49px; width: 193px" />
<asp:Label ID="Label1" runat="server" Font-Bold="True" Font-Size="X-Large"
ForeColor="Blue" Height="40px" Text="BV & RISK SIMULATOR"
Width="329px" style="text-align: center"></asp:Label>
<asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
</asp:ToolkitScriptManager>
<br />
<div style="OVERFLOW-Y:scroll; WIDTH:362px; HEIGHT:177px">
<br />
<table border="5" bordercolor=blue style="height: 116px; width: 343px">
<tr>
<td class="style1">
<asp:CheckBoxList ID="CheckBoxList1" runat="server"
DataSourceID="ChechkBoxDataSource" DataTextField="MUS_K_ISIM"
DataValueField="HESAP_NO" Font-Size="12pt">
</asp:CheckBoxList>
</td>
</tr>
</table>
</div>
<div style="width: 331px">
<br />
<asp:Textbox ID="TextBox1" runat="server" Font-Size="X-Small" Height="13px" Font-Names="Verdana" Width="75px" ></asp:Textbox>
<asp:CalendarExtender Format="dd/MM/yyyy" ID="TextBox1_CalendarExtender" runat="server"
TargetControlID="TextBox1">
</asp:CalendarExtender>
<asp:Image ID="ImageButton3" runat="server" ImageUrl="~/images/SmallCalendar.gif"/>
<br />
</div>
<asp:SqlDataSource ID="ChechkBoxDataSource" runat="server"
ConnectionString="<%$ ConnectionStrings:ora %>"
ProviderName="<%$ ConnectionStrings:ora.ProviderName %>"
SelectCommand="SELECT DISTINCT(A.HESAP_NO),A.MUS_K_ISIM
FROM S_TEKLIF A
ORDER BY A.MUS_K_ISIM">
</asp:SqlDataSource>
<br />
<asp:Button ID="SendToGridview" runat="server" Text="Send" Width="59px"
onclick="SendToGridview_Click" />
<br />
<br />
<asp:GridView ID="GridView1" runat="server"
Width="16px" CellPadding="4"
GridLines="None" Height="16px" ForeColor="#333333"
AutoGenerateColumns="False" DataKeyNames="RISK,MV" BorderColor="White"
BorderStyle="Ridge" >
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:BoundField HeaderText="HESAP" DataField="HESAP_NO" />
<asp:BoundField HeaderText="TEKLIF" DataField="TEKLIF" />
<asp:BoundField HeaderText="MUSTERI" DataField="MUSTERI" />
<asp:BoundField HeaderText="MARKA" DataField="MARKA" />
<asp:BoundField HeaderText="SASI" DataField="SASI_NO" />
<asp:BoundField HeaderText="DURUM" DataField="SASI_DURUM" />
<asp:BoundField HeaderText="TASIT MARKA" DataField="TAS_MAR" />
<asp:BoundField HeaderText="RISK" DataField="RISK" />
<asp:templatefield headertext="">
<itemtemplate>
<asp:CheckBox DataField="NameCheckBoxField1" ID="NameCheckBoxField1" Checked="True" runat="server"></asp:CheckBox>
</itemtemplate>
</asp:templatefield>
<asp:BoundField HeaderText="MV" DataField="MV" />
<asp:templatefield headertext="">
<itemtemplate>
<asp:CheckBox DataField="NameCheckBoxField2" ID="NameCheckBoxField2" Checked="True" runat="server"></asp:CheckBox>
</itemtemplate>
</asp:templatefield>
<asp:BoundField HeaderText="KV" DataField="KV" />
<asp:templatefield headertext="">
<itemtemplate>
<asp:CheckBox DataField="NameCheckBoxField3" ID="NameCheckBoxField3" Checked="True" runat="server"></asp:CheckBox>
</itemtemplate>
</asp:templatefield>
</Columns>
<EditRowStyle BackColor="#2461BF" />
<FooterStyle BackColor="#507CD1" ForeColor="White" Font-Bold="True" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#EFF3FB" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<sortedascendingcellstyle backcolor="#F4F4FD" />
<sortedascendingheaderstyle backcolor="#5A4C9D" />
<sorteddescendingcellstyle backcolor="#D8D8F0" />
<sorteddescendingheaderstyle backcolor="#3E3277" />
<SortedAscendingCellStyle BackColor="#F5F7FB"></SortedAscendingCellStyle>
<SortedAscendingHeaderStyle BackColor="#6D95E1"></SortedAscendingHeaderStyle>
<SortedDescendingCellStyle BackColor="#E9EBEF"></SortedDescendingCellStyle>
<SortedDescendingHeaderStyle BackColor="#4870BE"></SortedDescendingHeaderStyle>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:ora %>"
ProviderName="<%$ ConnectionStrings:ora.ProviderName %>"
SelectCommand=" SELECT A.HESAP_NO, A.TEKLIF_NO1 || '/' || A.TEKLIF_NO2 AS TEKLIF, A.MUS_K_ISIM ,
B.MARKA, C.SASI_NO, C.SASI_DURUM, D.TAS_MAR, NVL(RISK_SASI(A.TEKLIF_NO1, A.TEKLIF_NO2, C.URUN_SIRA_NO, C.SIRA_NO, :S_TARIH_B),0) AS RISK,
NVL(MV_SASI(A.TEKLIF_NO1, A.TEKLIF_NO2, C.URUN_SIRA_NO, C.SIRA_NO, :S_TARIH_B),0) AS MV,
MV_K(A.TEKLIF_NO1,A.TEKLIF_NO2, :S_TARIH_B) AS KV, 'NameCheckBoxField1' = 0x1, 'NameCheckBoxField2' = 0x1, 'NameCheckBoxField3' = 0x1
FROM S_TEKLIF A, S_URUN B, S_URUN_DETAY C, KOC_KTMAR_PR D
WHERE A.TEKLIF_NO1 || A.TEKLIF_NO2 = B.TEKLIF_NO1 || B.TEKLIF_NO2
AND A.TEKLIF_NO1 || A.TEKLIF_NO2 = C.TEKLIF_NO1 || C.TEKLIF_NO2
AND B.SIRA_NO = C.URUN_SIRA_NO
AND C.SASI_DURUM IN ('A','R')
AND B.DISTRIBUTOR = D.DIST_KOD
AND B.MARKA = D.MARKA_KOD
AND B.URUN_KOD = D.TAS_KOD ">
</asp:SqlDataSource>
<br />
<asp:Button ID="Calculate" runat="server" onclick="Calculate_Click"
Text="Calculate" />
<asp:Label ID="RISK_Label" runat="server" Font-Bold="True" ForeColor="#3333FF"></asp:Label>
<asp:Label ID="MV_Label" runat="server" Font-Bold="True" ForeColor="#3333FF"></asp:Label>
<asp:Label ID="KV_Label" runat="server" Font-Bold="True" ForeColor="#3333FF"></asp:Label>
<br />
<br />
</form>
</body>
</html>
BUT when i run this page i getting an error like this.
ORA-00918: column ambiguously defined
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-00918: column ambiguously defined
Source Error:
Line 128: myConnection.Open();
Line 129:
Line 130: OracleDataReader dr = myCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
Line 131:
Line 132: GridView1.DataSource = dr;
Probably right here MV_K(TEKLIF_NO1,TEKLIF_NO2, :S_TARIH_B) AS KV. It should be something like MV_K(A.TEKLIF_NO1,A.TEKLIF_NO2, :S_TARIH_B) AS KV since that column exists in A,B and C
You used table aliases and multiple tables but you did not use table alias prefixes in front of all fields. This is a typical situation where you get ambiguous columns.
加载中,请稍侯......
精彩评论