Visual Web Developer 2008 - ASP - Formview and Inserts

Question

Im using:

Visual Web Developer 2009 Access 2007

I am trying to insert into a products table, however i keep recieveing the error: "Data type mismatch in criteria expression."

The Edit and Delete sections work fine..

<%@ Page Title="" Language="VB" MasterPageFile="~/Master/MasterPage.master" AutoEventWireup="false" CodeFile="Products.aspx.vb" Inherits="Admin_Products" %>

Edit Products

    <asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" 
        DataKeyNames="product_id" DataSourceID="AccessDataSource1" DefaultMode="Insert" 
        Height="50px" Width="125px">
        <Fields>
            <asp:BoundField DataField="product_id" HeaderText="product_id" 
                InsertVisible="False" ReadOnly="True" SortExpression="product_id" />
            <asp:BoundField DataField="product_name" HeaderText="product_name" 
                SortExpression="product_name" />
            <asp:BoundField DataField="product_desc" HeaderText="product_desc" 
                SortExpression="product_desc" />
            <asp:BoundField DataField="product_cost" HeaderText="product_cost" 
                SortExpression="product_cost" />
            <asp:BoundField DataField="product_amount" HeaderText="product_amount" 
                SortExpression="product_amount" />
            <asp:BoundField DataField="category_id" HeaderText="category_id" 
                SortExpression="category_id" />
            <asp:CommandField ShowInsertButton="True" />
        </Fields>
    </asp:DetailsView>
    <asp:AccessDataSource ID="AccessDataSource1" runat="server" 
        DataFile="~/App_Data/Fandangles.mdb" 
        DeleteCommand="DELETE FROM [product] WHERE [product_id] = ?" 
        InsertCommand="INSERT INTO [product] ([product_name], [product_desc], [product_cost], [product_amount], [category_id]) VALUES (?, ?, ?, ?, ?)" 
        SelectCommand="SELECT * FROM [product]" 
        UpdateCommand="UPDATE [product] SET [product_name] = ?, [product_desc] = ?, [product_cost] = ?, [product_amount] = ?, [category_id] = ? WHERE [product_id] = ?">
        <DeleteParameters>
            <asp:Parameter Name="product_id" Type="Int32" />
        </DeleteParameters>
        <UpdateParameters>
            <asp:Parameter Name="product_name" Type="String" />
            <asp:Parameter Name="product_desc" Type="String" />
            <asp:Parameter Name="product_cost" Type="Decimal" />
            <asp:Parameter Name="product_amount" Type="Int32" />
            <asp:Parameter Name="category_id" Type="Int32" />
            <asp:Parameter Name="product_id" Type="Int32" />
        </UpdateParameters>
        <InsertParameters>
            <asp:Parameter Name="product_id" Type="Int32" />
            <asp:Parameter Name="product_name" Type="String" />
            <asp:Parameter Name="product_desc" Type="String" />
            <asp:Parameter Name="product_cost" Type="Decimal" />
            <asp:Parameter Name="product_amount" Type="Int32" />
            <asp:Parameter Name="category_id" Type="Int32" />
        </InsertParameters>
    </asp:AccessDataSource>
</p>
</asp:Content>


DB: Access DB

product_id - Autonumber
product_name - Text
product_desc - Text
product_cost - Currency
product_amount - Number
category_id  - Number - This is a lookup field

Any ideas?

Answer 1

Do you really need to pass your primary key "product_id" into the insert? If its an identity / PK its not needed.

Its not a param in your insert SQL yet it is a param in the datasoruces insert params.

Try this

<InsertParameters>
        <asp:Parameter Name="product_id" Type="Int32" />
        <asp:Parameter Name="product_name" Type="String" />
        <asp:Parameter Name="product_desc" Type="String" />
        <asp:Parameter Name="product_cost" Type="Decimal" />
        <asp:Parameter Name="product_amount" Type="Int32" />
        <asp:Parameter Name="category_id" Type="Int32" />
</InsertParameters>