Stored Procedure with multiple inserts SQL and ASP.Net

Question

I'm new to writing stored procedures. I want to write a stored procedure in SQL Server for multiple inserts from a table in ASP.net.

This is what I have so far:

Stored procedure:

ALTER procedure [dbo].[spUser]
    @userName VARCHAR(50),
    @membershipnr INT,
    @email VARCHAR(50)
as
begin
    DECLARE @returnvalue INT

    IF (SELECT COUNT(*) FROM User WHERE Membershipnr = @membershipnr) > 0
    BEGIN
        SET @returnvalue = -1
    END
    ELSE
    BEGIN
        SET @returnvalue = 1

        INSERT INTO User(Name, Membershipnr, Email) 
        VALUES(@userName, @membershipnr, @email)
    END

    SELECT @returnvalue
end

C# code:

SqlCommand cmd = new SqlCommand("spUser", conn);
cmd.CommandType = CommandType.StoredProcedure;

SqlCommand com = new SqlCommand("spTeam", conn);
com.CommandType = CommandType.StoredProcedure;

if (ddl1.SelectedValue == "2" && rb.SelectedValue == "M" && ddl2.SelectedValue == "1")
{
    cmd.Parameters.AddWithValue("@userName", txtName.Text);
    cmd.Parameters.AddWithValue("@userName", txtName2.Text);
    cmd.Parameters.AddWithValue("@membershipnr", txtMembershipnr.Text);
    cmd.Parameters.AddWithValue("@membershipnr", txtMembershipnr2.Text);
    cmd.Parameters.AddWithValue("@email", txtEmail.Text);
    cmd.Parameters.AddWithValue("@email", txtEmail2.Text);
    com.Parameters.AddWithValue("@clubID", "1");
    com.Parameters.AddWithValue("@class", "2xM");
    com.Parameters.AddWithValue("@teamName", txtTeam.Text);

    conn.Open();
    int answer = (int)cmd.ExecuteNonQuery();

}

I get an error when I try to insert 2 columns. The error I get is:

Procedure or function spUser HAS too many arguments specified.

I think the problem is my stored procedure. But I don't know how I can specify more inserts in the procedure. I searched the internet but I couldn't find the answer.

Does somebody know how I can fix this so the error goes away and my insert will work?

Thanks in advance!

UPDATE:

I'm using a 3 table with the texboxes.

One of the tables:

<asp:Table ID="tblTwo" runat="server" class="table">
    <asp:TableHeaderRow>
        <asp:TableHeaderCell>Name</asp:TableHeaderCell>
        <asp:TableHeaderCell>Email</asp:TableHeaderCell>
        <asp:TableHeaderCell>Membershipnr</asp:TableHeaderCell>
    </asp:TableHeaderRow>

    <asp:TableRow>
        <asp:TableCell>
            <asp:TextBox ID="txtName" type="text" class="form-control" runat="server"></asp:TextBox>
        </asp:TableCell>
        <asp:TableCell>
            <asp:TextBox ID="txtEmail" type="text" class="form-control" runat="server"></asp:TextBox>
        </asp:TableCell>
        <asp:TableCell>
            <asp:TextBox ID="txtMembershipnr" type="text" class="form-control" runat="server"></asp:TextBox>
        </asp:TableCell>
    </asp:TableRow>
    <asp:TableRow>
        <asp:TableCell>
            <asp:TextBox ID="txtName2" type="text" class="form-control" runat="server"></asp:TextBox>
        </asp:TableCell>
        <asp:TableCell>
            <asp:TextBox ID="txtEmail2" type="text" class="form-control" runat="server"></asp:TextBox>
        </asp:TableCell>
        <asp:TableCell>
            <asp:TextBox ID="txtMembershipnr2" type="text" class="form-control" runat="server"></asp:TextBox>
        </asp:TableCell>
    </asp:TableRow>
</asp:Table>

Also I have a second stored procedure for CulbID, Class and Team because this has to be filled into another SQL Server table.

Code of that stored procedure:

ALTER procedure [dbo].[spTeam]
    @teamName VARCHAR (50)
as
BEGIN
    INSERT INTO Ploeg(Name, RegistrationDate)
    VALUES(@teamName, GETDATE())
END

You didn't define `clubID`, `class` and `teamName` parameters in your sp definition. You only declared 3 parameter in your sp but you try to add 6 different parameters to it. You need to define these parameters in your sp definition as well. — Soner Gönül, Apr 17 '15 at 14:23
You need to create datatable and pass it to sp and in sp you need UDT — Ehsan Sajjad, Apr 17 '15 at 14:35
@EhsanSajjad How should I do that? Because I'm new to stored proecdures and I only know the kind of procedure I used here :S — Marjolein, Apr 17 '15 at 14:44
First confirm if I understood your question right, you want to do multiple rows insert in one call to sp? — Ehsan Sajjad, Apr 17 '15 at 14:45
@EhsanSajjad Yes thats right! And only for the spUser NOT for spTeam. — Marjolein, Apr 17 '15 at 14:46
Have a look here then it is using UDT:http://stackoverflow.com/questions/1030848/how-to-pass-user-defined-table-type-as-stored-procedured-parameter-in-c-sharp — Ehsan Sajjad, Apr 17 '15 at 14:48
@EhsanSajjad thanks I will look at that! I will let you know if that solved my problem. — Marjolein, Apr 17 '15 at 15:01
You should check out [Can we stop using AddWithValue() already?](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) and stop using `.AddWithValue()` - it can lead to unexpected and surprising results... — marc_s, Apr 17 '15 at 16:12

score 3 · Answer 1 · answered Apr 17 '15 at 14:25

3

Your stored procedure has less number of input variables then what you are sending from your C# code. You need to define them in your stored procedure.

Try to add all the input variables which you are sending from your code like this:

ALTER procedure [dbo].[spUser]
@userName VARCHAR(50),
@membershipnr INT,
@email VARCHAR(50)
@clubID int,
@class varchar(5),
@teamname varchar(15),
.............

answered Apr 17 '15 at 14:25

Rahul Tripathi

168,305
31
280
331

Thanks but I am using 2 stored procedures. Because I need to insert data into 2 table in my database. I have updated my question with more code. – Marjolein Apr 17 '15 at 14:40
@Marjolein:- If you are using 2 stored procedures then you have to call both the stored procedure explicitly and provide the input parameters as defined in the code. If the second stored procedure is getting called from inside the first SP then also you need to provide all the input parameters of the SP(*provided that the output parameter of 1st SP is not getting used in 2nd SP*) – Rahul Tripathi Apr 17 '15 at 14:42

score 2 · Accepted Answer · answered Apr 17 '15 at 14:47

2

Like others have mentioned, you've got too many parameters in you C# code. Your Stored Procedure takes only 3 parameters

@userName VARCHAR(50),
@membershipnr INT,
@email VARCHAR(50)

As such, when you are adding the parameters in C#, they need to match (the number of parameters, name and type) i.e.

cmd.Parameters.AddWithValue("@userName", txtName.Text);
cmd.Parameters.AddWithValue("@membershipnr", txtMembershipnr.Text);
cmd.Parameters.AddWithValue("@email", txtEmail.Text);

Personally, I like to pass the type as well using the following

cmd.Parameters.Add("userName", SqlDbType.VarChar).Value = txtName.Text;
cmd.Parameters.Add("membershipnr", SqlDbType.Int).Value = txtMembershipnr.Text;
cmd.Parameters.Add("email", SqlDbType.VarChar).Value = txtEmail.Text;

answered Apr 17 '15 at 14:47

scm8jet

66
5

How should this solve my problem? I need to insert multiple rows into my database. And I have already defined the types of my parameters in my SP. Please explain. – Marjolein Apr 17 '15 at 14:53
If it was me, I would have a C# function called AddUser that takes 3 parameters AddUser(membershipnr,username, email). Then call that function individually for each record you want to add. – scm8jet Apr 17 '15 at 14:57
If you want to add multiple records with one call to the DB, you will need to have repeated groups of parameters in the Stored Procedure definition i.e. `code` @userName1 VARCHAR(50), @membershipnr1 INT, @email1 VARCHAR(50) @userName2 VARCHAR(50), @membershipnr2 INT, @email2 VARCHAR(50) @, userName3 VARCHAR(50), @membershipnr3 INT, @email3 VARCHAR(50)' – scm8jet Apr 17 '15 at 15:01
Oke thanks but I am using 3 tables with textboxes can I not fill parameters that are defined in the sp? Because I have multiple tables whitch should use other parameters that also need to be defined in my SP. For example: I have 1 table with txtName, txtName2, txtEmail ect. for this table I should use than `@userName, @userName2, @email` ect. But I also have a second table with should use the parameters `@userName3` ect. – Marjolein Apr 17 '15 at 15:27
It's not clear what you are trying to achieve. The thing to remember is that the parameters you are passing to the SP need to match - if your Stored Procedure takes 3 parameters with names x,y,z then you can only pass it 3 parameters with matching names x,y,z. In you sample, your SP accepts only 3 parameters but you are tyring to send it 9. – scm8jet Apr 17 '15 at 15:50
If your SP takes 9 parameters that update different tables in the insert, then just seperate the INSERT statements with a semi colon. Not sure if this is what you are trying to achieve, but just thought I'd tag it on as it sounds like you might be. – scm8jet Apr 17 '15 at 15:51
The idea is: when a user select a value in a ddl there will be a table visible with a number of rows. Example: If user selects a value 2 there will be a table shown with 2 rows and when a user selects a value 3 there will be a table shown with 3 rows. But there can only be one table shown! The table with 2 rows has txtName and txtName2 but the table with 3 rows has txtName3, txtName4 and txtName5. I want to have 1 SP for this. But I don't know how. – Marjolein Apr 17 '15 at 16:12
I have never used a semi colon how should I do that? And do I need to do that? Now you hopefully know what I am trying to do. – Marjolein Apr 17 '15 at 16:13
Sorry for the delay getting back to you. I've got a better idea of what you are trying to achieve. In your case I would have a single stored procedure called from a function called AddUser. Then have a loop to call this function for the number of Inserts you want to perform. – scm8jet Apr 20 '15 at 08:14
If your stored procedure needs to update more than one table, then you can seperate the inserts with a semi colon character (;). I.e. `insert into tbl1 ... where id = 1; insert into tbl2 ... where id = 1;` – scm8jet Apr 20 '15 at 08:15
My Stored Procedure work now! Thanks for your help! :) – Marjolein Apr 22 '15 at 10:26

score 0 · Answer 3 · answered Apr 17 '15 at 14:24

You're specifying too many parameters in your code (some missing, some duplicated). The way you've written the procedure would require the stored proc to be executed once for each row of data you want to insert.

Usually this would be achieved with a loop in your C#.

Stored Procedure with multiple inserts SQL and ASP.Net

3 Answers3