Ambiguous column name 'ProductID' in asp.net

Question

I have this in my html:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" OnRowCommand = "GridView1_RowCommand"
        DataSourceID="SqlDataSource1">
    <Columns>
        <asp:BoundField DataField="CustomerID" HeaderText="CustomerID" 
                SortExpression="CustomerID" />
        <asp:BoundField DataField="ProductID" HeaderText="ProductID" 
                SortExpression="ProductID" />
        <asp:BoundField DataField="TotalProduct" HeaderText="TotalProduct" 
                SortExpression="TotalProduct" />
        <asp:BoundField DataField="UpdatedProduct" HeaderText="UpdatedProduct" SortExpression="UpdatedProduct" />
        <asp:BoundField DataField="ProductQuantity" HeaderText="ProductQuantity" SortExpression="ProductQuantity" />
        <asp:TemplateField>
            <ItemTemplate>
                <asp:LinkButton ID="btnApprove" runat="server" Text="Approve" CommandName="Approve" CommandArgument='<%# Eval("ProductID") %>' />
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:Myconn %>" 
        SelectCommand="SELECT CustomerProducts.*, Products.ProductQuantity FROM CustomerProducts INNER JOIN Products ON CustomerProducts.ProductID = Products.ProductID">
</asp:SqlDataSource>

and here is the code behind:

protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
        if (e.CommandName == "Approve")
        {
            using (SqlConnection scn = new SqlConnection("Data Source = 'PAULO'; Initial Catalog=ShoppingCartDB;Integrated Security =True"))
            {
                scn.Open();
                SqlCommand cmd = new SqlCommand("update o set o.Updatedproduct = p.ProductQuantity - o.Totalproduct from CustomerProducts o inner join Products p on o.ProductID = p.ProductID WHERE ProductID=@ProductID", scn);
                cmd.Parameters.AddWithValue("@ProductID", ID);
                cmd.ExecuteNonQuery();
            }
        }
    }

I really don't have an idea what is the error I'm getting. What I'm trying to do here is to upon link click, it will update the updatedproduct column.

Here is a screenshot

UPDATE:

I get this error:

Conversion failed when converting the nvarchar value '__Page' to data type int.

5 questions in 8 hours all of which are real basic sql concepts. Please take a sql tutorial. This is not a tutorial site. And you are on Microsoft SQL Server not mysql we now know after last night. — Drew, Jun 25 '16 at 14:46

score 3 · Accepted Answer · answered Jun 25 '16 at 14:45

3

Since the column ProductID is present in both tables, the WHERE clause find it Ambiguous. So,

Replace ProductID=@ProductID with o.ProductID=@ProductID

update o set o.Updatedproduct = p.ProductQuantity - o.Totalproduct 
from CustomerProducts o 
inner join Products p 
on o.ProductID = p.ProductID WHERE o.ProductID=@ProductID

answered Jun 25 '16 at 14:45

Zein Makki

29,485
6
52
63

@ReineViray I can't see `__Page` in the code in your question. – Zein Makki Jun 25 '16 at 14:49
@ReineViray Do you have any triggers on update in the database ? – Zein Makki Jun 25 '16 at 14:53
what do you mean by trigger sir? actually the statement that i have now is working fine when i try it with a button but the problem is it update all of the `updatedproduct column` and what i want is just to update a single column after link click sir. thank you – Reine Viray Jun 25 '16 at 14:55
@ReineViray the value of the variable "ID" is __Page, it should be and integer. From where are you reading this ? – Zein Makki Jun 25 '16 at 15:11
@Riene This is the correct answer. Now you it should work. – vivek Jun 25 '16 at 15:29
What you *could* do is perform community moderation activity to discourage Help Vampire behaviour – Drew Jun 25 '16 at 15:34
@user3185569, not sure sir. it just shows me that error when i try to debug it – Reine Viray Jun 25 '16 at 17:22
@ReineViray How are you setting the value `ID` ? It should be an integer, while you are setting it to a string with a value "__Page". That's why the error is stating `Can't convert from NVARCHAR to int` – Zein Makki Jun 25 '16 at 17:32
i tried to put it as `int ProductID = 0` does it make sense? nothing happens after i click on the approve link – Reine Viray Jun 25 '16 at 17:33
@user3185569 i didnt set up for an ID. i think its getting it somewhere in my webform but definitely i didnt declare it sir – Reine Viray Jun 25 '16 at 17:34
Probably, you need to set it by reading `` if i understand what you are trying to do correctly. That's why there is a `CommandArgument='<%# Eval("ProductID") %>'` – Zein Makki Jun 25 '16 at 17:35
what possible way on how to declare it sir @user3185569 for it to work.hmm – Reine Viray Jun 25 '16 at 17:35
@user3185569 thats the html code i currently have sir – Reine Viray Jun 25 '16 at 17:37
@ReineViray Well that's another issue not related to your original question, which is how to read the `ID` from the grid and pass it to code-behind. This is outside the scope of this question. I think you need to create another question for that as i don't know that answer. But this answers the original question which is the ambiguous column. – Zein Makki Jun 25 '16 at 17:40
http://stackoverflow.com/questions/38031305/conversion-failed-when-converting-the-nvarchar-value-page-to-data-type-int – Reine Viray Jun 25 '16 at 17:50

Ambiguous column name 'ProductID' in asp.net

1 Answers1

Linked