Help with Dropdownlist using forgeign key table

[teddymeu]

Hi Guys, this is kinda complicated but ill do my best to explain.
I have two tables. products and categories. Products holds product info and an image, its primary key is ProductID. Category table holds a category name and description and a primary key CategoryID. The producs table holds a Foreign key field called CategoryID which points to the CategoryID Primary key of the catgory table.

Ok i have two asp.net 2 vb forms. One is using a detailsview to edit, insert and update Category. I did this so that the end user could add new categories and descriptions to the Categories of products. This works fine.

My second page/ from is the product updates page, it works fine uploads the image, inserts the product info fine however i had to add in a dropdownlist where i wanted the user to select the category. Obviosly i couldnt use the category ID for the text as no one would know which id meant what, so i chose the Category Name as the datatext and Used the category ID as the datavalue and i bound the selected value to CategoryID. I Keep getting an error message when i submit saying "MUST DECLARE SCALAR VARIABLE @dropdownlist1" in new to all this and stuck, can someone take a look and advise me how to fix this.

My code is attached

<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %>
 
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="Server">
    <asp:DetailsView ID="UploadProductsImageUI" runat="server" DataSourceID="UploadPictureDataSource" DataKeyNames="ProductID" AutoGenerateRows="False" DefaultMode="Insert">
        <Fields>
            <asp:TemplateField HeaderText="Product Name">
                <InsertItemTemplate>
                    <asp:TextBox ID="ProductName" runat="server" Text='<%# Bind("ProductName") %>'></asp:TextBox>
                </InsertItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Category">
                <InsertItemTemplate>
                    <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="category" DataTextField="CategoryName"
                        DataValueField="CategoryID" SelectedValue='<%# Bind("CategoryID") %>'>
                    </asp:DropDownList>
                </InsertItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Colour">
                <InsertItemTemplate>
                    <asp:ListBox ID="Colour" runat="server" CssClass="content" Enabled="true" SelectionMode="Single">
                        <asp:ListItem>- Please Select -</asp:ListItem>
                        <asp:ListItem>Autumn Brown</asp:ListItem>
                        <asp:ListItem>Black</asp:ListItem>
                        <asp:ListItem>Fossil Mint</asp:ListItem>
                        <asp:ListItem>Kotah Black</asp:ListItem>
                        <asp:ListItem>kotah Blue</asp:ListItem>
                        <asp:ListItem>Misty Grey</asp:ListItem>
                        <asp:ListItem>Peacock</asp:ListItem>
                        <asp:ListItem>Raj Green</asp:ListItem>
                    </asp:ListBox><br />
                </InsertItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Product Price Landscaper">
                <InsertItemTemplate>
                    <asp:TextBox ID="ProductPriceLandscaper" runat="server" Text='<%# Bind("ProductPriceLandscaper") %>'></asp:TextBox>
                </InsertItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Product Price Retail">
                <InsertItemTemplate>
                    <asp:TextBox ID="ProductPriceRetail" runat="server" Text='<%# Bind("ProductPriceRetail") %>'></asp:TextBox>
                </InsertItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Product Price Wholsale">
                <InsertItemTemplate>
                    <asp:TextBox ID="ProductPriceWholeSale" runat="server" Text='<%# Bind("ProductPriceWholeSale") %>'></asp:TextBox>
                </InsertItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Product Image">
                <InsertItemTemplate>
                    <asp:FileUpload ID="UploadedFile" runat="server" />
                </InsertItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Image Title">
                <InsertItemTemplate>
                    <asp:TextBox ID="ImageTitle" runat="server" Text='<%# Bind("ImageTitle") %>'></asp:TextBox>
                </InsertItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Size">
                <InsertItemTemplate>
                    <asp:TextBox ID="Size" runat="server" Text='<%# Bind("Size") %>'></asp:TextBox>
                </InsertItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Pcs Crate">
                <InsertItemTemplate>
                    <asp:TextBox ID="pcsCrate" runat="server" Text='<%# Bind("PcsCrate") %>'></asp:TextBox>
                </InsertItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Sgm Crate">
                <InsertItemTemplate>
                    <asp:TextBox ID="sgmCrate" runat="server" Text='<%# Bind("SgmCrate") %>'></asp:TextBox>
                </InsertItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Weight">
                <InsertItemTemplate>
                    <asp:TextBox ID="weight" runat="server" Text='<%# Bind("Weight") %>'></asp:TextBox>
                </InsertItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField ShowHeader="False">
                <InsertItemTemplate>
                    <asp:LinkButton ID="btnInsert" runat="server" CausesValidation="True" CommandName="Insert" Text="Insert" ForeColor="#000099"></asp:LinkButton><br />
                    <asp:LinkButton ID="btnCancel" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancel" OnClick="btnCancel_Click" ForeColor="#000099"></asp:LinkButton>
                </InsertItemTemplate>
            </asp:TemplateField>
        </Fields>
    </asp:DetailsView>
    &nbsp;
    <asp:SqlDataSource ID="category" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringAccountType %>"
        SelectCommand="SELECT [CategoryID], [CategoryName] FROM [Category]"></asp:SqlDataSource>
    <br />
     <asp:SqlDataSource ID="UploadPictureDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringAccountType %>"
        InsertCommand="INSERT INTO [Products]([ProductName], [CategoryID], [Colour], [ProductPriceRetail], [ProductPriceWholeSale], [ProductPriceLandscaper], [ImageTitle], [ImageData], [ImageMimeType], [Size], [PcsCrate], [SgmCrate], [Weight]) VALUES (@ProductName, @DropDownList1, @Colour, @ProductPriceRetail, @ProductPriceWholeSale, @ProductPriceLandscaper, @ImageTitle, @ImageData, @MimeType, @Size, @pcsCrate, @sgmCrate, @weight)">
        <InsertParameters>
        <asp:Parameter Name="ProductName" Type="string" />
        <asp:Parameter Name="CategoryID" Type="int32" />
        <asp:Parameter Name="Colour" Type="string" />
        <asp:Parameter Name="ProductPriceRetail" Type="string" />
        <asp:Parameter Name="ProductPriceWholeSale" Type="String" />
        <asp:Parameter Name="ProductPriceLandscaper" Type="string" />
        <asp:Parameter Name="ImageTitle" Type="string" />
        <asp:Parameter Name="ImageData" />
        <asp:Parameter Name="MimeType" Type="string" />
        <asp:Parameter Name="Size" Type="string" />
        <asp:Parameter Name="pcsCrate" Type="string" />
        <asp:Parameter Name="sgmCrate" Type="string" />
        <asp:Parameter Name="weight" Type="string" /> 
        </InsertParameters>
        </asp:SqlDataSource>
        <asp:Label ID="ErrorMessage" runat="server" EnableViewState="False" Visible="False"></asp:Label>
</asp:Content>

[kenobewan]

I suggest try debugging without dropdownlist first. If this works why isn't it recognising the name?

[teddymeu]

I suggest try debugging without dropdownlist first. If this works why isn't it recognising the name?

If i dont have the drop down it works fine and writes the info to my db products table.

This problem only started happening when i added in the drop list, ive read up briefly on dropdown lists and something about the selected value, do i need to have a function for this so that when they select this it captures the selection?