2

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
Improve this question
8
  • 1
    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. Commented Apr 17, 2015 at 14:23
  • 1
    You need to create datatable and pass it to sp and in sp you need UDT Commented Apr 17, 2015 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 Commented Apr 17, 2015 at 14:44
  • 1
    Have a look here then it is using UDT:stackoverflow.com/questions/1030848/… Commented Apr 17, 2015 at 14:48
  • 1
    You should check out Can we stop using AddWithValue() already? and stop using .AddWithValue() - it can lead to unexpected and surprising results... Commented Apr 17, 2015 at 16:12

3 Answers 3

Reset to default
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),
.............
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

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:- 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)
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;

Improve this answer

11 Comments

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.
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.
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)'
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.
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.
|
0

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#.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.