As someone new brand new to asp.net I am trying to do something relatively simple to avoid inserting duplicate records.

I have a stored procedure - code at the end - that checks whether a department of the same name exists in the database and if not creates a new row with the new departmental name (the departmentID is set as the pk and increments automatically).

But when I call the stored procedure via the InsertCommand within a gridview I recieve the following message:

System.Data.SqlClient.SqlException: Procedure or function InsertDepartment has too many arguments specified.

I'm sure I'm missing something obvious. Am I making mistakes in how I am specifying the stored proc or missing command add parameter?

Any help would be very much appreciated.

Cheers

Page mark up

<asp:DetailsView ID="DetailsView2" runat="server"
                AutoGenerateInsertButton="True" AutoGenerateRows="False"
                DataSourceID="SqlDataSource1" DefaultMode="Insert" Height="50px"
                Width="329px">
                <Fields>
                    <asp:BoundField DataField="Department" HeaderText="Department"
                        SortExpression="Department" />
                </Fields>
            </asp:DetailsView>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server"
                ConnectionString="<%$ ConnectionStrings:LinwoodSchoolWorksConnectionStri ng %>"
                SelectCommand="SELECT Department FROM Departments order by department asc"
                InsertCommand="InsertDepartment" InsertCommandType="StoredProcedure" >

                <InsertParameters>
                    <asp:Parameter Name="DepartmentName" Type="String" />
                </InsertParameters>
            </asp:SqlDataSource>

Stored Procedure

ALTER PROCEDURE InsertDepartment
(
  @DepartmentName varchar(50)
)
AS
IF EXISTS(SELECT 'True' FROM Departments WHERE Department = @DepartmentName)
BEGIN
  SELECT 'This record already exists!'
END
ELSE
BEGIN
  SELECT 'Record Added'
  INSERT into Departments(Department) VALUES(@DepartmentName)
END

This looks OK to me, but I'm not an expert on using the SqlDataSource.

Can you try doing a SQL trace to see what the exact call to SQL is from the program? This may shed some light on the problem.

-Peter
compiledthoughts.com

javascript:insertsmilie(':D')

Thanks for the suggestion, but Duh! I believed the answer was straight forward. I managed to solve the problem by renaming the boundfield associated with the insert command in the details view like

<asp:DetailsView ID="DetailsView2" runat="server"
                AutoGenerateInsertButton="True" AutoGenerateRows="False"
                DataSourceID="SqlDataSource1" DefaultMode="Insert" Height="50px"
                Width="329px">
                <Fields>
                    <asp:BoundField DataField="DepartmentName" HeaderText="Department" />

                </Fields>
            </asp:DetailsView>

 This is same name as the input parameter in the stored proc, rather than the name of the column in the database ie. Department. This appears to resolve the problem of the "too many arguments passed to the procedure" error message although I'm not sure why, especially given number of parameters in the select and insert commands are the same. Mmmm!



David