asp.net - sqldatasource - detailsview - use stored proc to insert record

Question

I am trying to use stored proc to insert record using detailsview and sqldatasource. I get the following error:

Procedure or function 'CustRec_iu' expects parameter '@firstname', which was not supplied.

My detailsview definition is as follows:

<asp:DetailsView ID="dvCustDetails1" runat="server" AutoGenerateRows="False" 
    DataSourceID="SqlDataSource1" Height="149px" Width="469px">
            <Fields>
                <asp:BoundField DataField="FirstName" HeaderText="First Name" 
                    SortExpression="FirstName" />
                <asp:BoundField DataField="LastName" HeaderText="Last Name" 
                    SortExpression="LastName" />
                <asp:CommandField ButtonType="Button" ShowInsertButton="True" />
            </Fields>
</asp:DetailsView>

In the code behind , PageLoad looks as follows:

    SqlDataSource1.ConnectionString = Connection.ConnectionStr;

    //SqlDataSource1.InsertCommand = "INSERT INTO [customer] (firstname,lastname,active) values(@firstname,@lastname,@active)";
    SqlDataSource1.SelectCommand = "select firstname,lastname from customer";

    //SqlDataSource1.SelectCommand = "CustRec";
    SqlDataSource1.InsertCommand = "CustRec_iu";

    SqlDataSource1.InsertParameters.Clear();

    SqlDataSource1.InsertParameters.Add(new Parameter("firstname", DbType.String));
    SqlDataSource1.InsertParameters.Add(new Parameter("LastName", DbType.String));
    SqlDataSource1.InsertParameters.Add(new Parameter("Active",DbType.Int16,"1"));

Notice that if I use commented out inline statement then the insert works.

My stored proc looks as follows:

ALTER PROCEDURE dbo.CustRec_iu
    (
    @custid int = null,
    @firstname varchar(100),
    @lastname varchar(100),
    @Active bit = 1 
    )

AS
    if (isnull(@custid,0) = 0)
    begin
     insert into customer
      (firstname,lastname,Active) 
     values
      (@firstname,@lastname,@Active)
    end
    else
    begin
     update customer 
     set 
      firstname=@firstname, 
      lastname= @lastname, 
      active = @Active
     where 
      custid = @custid
    end
    /* SET NOCOUNT ON */
    RETURN

What I dont understand how the input parameter interacts between sqldatasource, detailsview etc. How does it work with insline statement and not work with stored proc? How does sql datasource and detailsview work in terms of events? Google search and print book Professional asp.net 3.5 in c# and VB is not much help .

Thank you in advance to read my question.

Answer 1

After a quick look, it looks like a default value is not assigned to the insert parameters. Try the Parameter class constructor...

New Parameter(name, dbType, defaultValue)

Also, you probably don't need to do the sqldatasource in the code behind. You could try the following.

<asp:DetailsView DataSourceID="SqlDataSource1" Height="149px" Width="469px">
   <Fields>
      <asp:BoundField DataField="FirstName" HeaderText="First Name" SortExpression="FirstName" />
      <asp:BoundField DataField="LastName" HeaderText="Last Name" SortExpression="LastName" />
      <asp:CommandField ButtonType="Button" ShowInsertButton="True" />
   </Fields>
</asp:DetailsView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:YourConnectionString %>"
   InsertCommand="CustRec_iu"
   InsertCommandType="StoredProcedure" 
   SelectCommand="select firstname,lastname from customer" 
   SelectCommandType="Text">
        <InsertParameters>
            <asp:Parameter Name="firstname" Type="String" />
            <asp:Parameter Name="LastName" Type="String" />
            <asp:Parameter Name="Active" Type="String" DefaultValue="1" />
        </InsertParameters>
</asp:SqlDataSource>

Answer 2

I think you may be missing SqlDataSource1.InsertCommandType = CommandType.StoredProcedure

Answer 3

Inline statesments work just like the sproc, except you create the string with the @param statements embedded in the string.

You should just have to pull in your update and insert statement in your sproc. You then have to create the parameter links.

If you want to have this drag and drop, insert the update statement into the wizard. The worst case scenario is adding the parameter mappings. So, the drag and drop does not really offer much here.