Click here to Skip to main content
15,889,281 members
Please Sign up or sign in to vote.
4.50/5 (2 votes)
See more:
I am trying to insert data using a SP as follows:

SQL
ALTER PROCEDURE [dbo].[AddTrainingRoomRq]

@req_start_date DateTime,
@req_end_date DateTime,
@req_times text,
@requestor_name text,
@requestor_phone text,
@requestor_mail_symbol text,
@requestor_email text,
@class_name text,
@user_num text,
@other_software text,
@internet_access bit,
@intranet_access bit,
@other_logons bit,
@other_logon_list text,
@additional_comments varchar (500),
@Result nvarchar (500) output

AS
BEGIN

Declare 

@ClassID int

Set @ClassID = 	(SELECT CAST(RAND() * 1000000000 AS INT) AS [RandomNumber])

INSERT INTO cal_tr_requests
           (ClassID, req_start_date, req_end_date, req_times, requestor_name, requestor_phone, requestor_mail_symbol, requestor_email, class_name, user_num, other_software, 
		   internet_access, intranet_access, other_logons, other_logon_list, additional_comments, date_of_req)
VALUES        (@ClassID,@req_start_date,@req_end_date,@req_times,@requestor_name,@requestor_phone,@requestor_mail_symbol,@requestor_email,@class_name,@user_num,@other_software,
              @internet_access,@intranet_access,@other_logons,@other_logon_list,@additional_comments,getdate())

Set @Result = Convert(nvarchar,@ClassID)

END

My code behind:
<pre lang="c#"{
    public partial class trainingroom : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            String strSysDt = Convert.ToString(System.DateTime.Now);
        }

        protected void lbInsert_Click(object sender, EventArgs e)
        {
            string strReqNm = ((TextBox)fvAddBookingInfo.FindControl("txtReqName")).Text;
            string strPhone = ((TextBox)fvAddBookingInfo.FindControl("txtPhone")).Text;
            string strMailSymbol = ((TextBox)fvAddBookingInfo.FindControl("txtMailSym")).Text;
            string strEmail = ((TextBox)fvAddBookingInfo.FindControl("txtEmail")).Text;
            string strFirstDt = ((TextBox)fvAddBookingInfo.FindControl("txtFirstDt")).Text;
            string strLastDt = ((TextBox)fvAddBookingInfo.FindControl("txtLastDt")).Text;
            string strUseTime = ((TextBox)fvAddBookingInfo.FindControl("txtUseTime")).Text;
            string strClassTy = ((TextBox)fvAddBookingInfo.FindControl("txtClass")).Text;
            string strNumOfUsr = ((TextBox)fvAddBookingInfo.FindControl("txtNumOfUsr")).Text;
            string strOtherSW = ((TextBox)fvAddBookingInfo.FindControl("txtOtherSW")).Text;
            CheckBox ckInternet = (CheckBox)fvAddBookingInfo.FindControl("cbInternet");
            CheckBox ckIntranet = (CheckBox)fvAddBookingInfo.FindControl("cbIntranet");
            CheckBox ckOthrLog = (CheckBox)fvAddBookingInfo.FindControl("cbOtherLog");
            string strListOthLog = ((TextBox)fvAddBookingInfo.FindControl("txtOtherLogList")).Text;
            string strComment = ((TextBox)fvAddBookingInfo.FindControl("txtComment")).Text;
            String strSysDt = Convert.ToString(System.DateTime.Now);

            SqlConnection conn = new SqlConnection();
            SqlCommand cmd = new SqlCommand();
            conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["CFMAppsConnectionString"].ConnectionString;
            cmd.Connection = conn;
            cmd.CommandText = "AddTrainingRoomRq";
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.Add("@requestor_name", SqlDbType.NVarChar).Value = strReqNm;
            cmd.Parameters.Add("@requestor_phone", SqlDbType.NVarChar).Value = strPhone;
            cmd.Parameters.Add("@requestor_mail_symbol", SqlDbType.NVarChar).Value = strMailSymbol;
            cmd.Parameters.Add("@requestor_email", SqlDbType.NVarChar).Value = strEmail;
            cmd.Parameters.Add("@req_start_date", SqlDbType.DateTime).Value = strFirstDt;
            cmd.Parameters.Add("@req_end_date", SqlDbType.DateTime).Value = strLastDt;
            cmd.Parameters.Add("@req_times", SqlDbType.NVarChar).Value = strUseTime;
            cmd.Parameters.Add("@class_name", SqlDbType.NVarChar).Value = strClassTy;
            cmd.Parameters.Add("@user_num", SqlDbType.NVarChar).Value = strNumOfUsr;
            cmd.Parameters.Add("@other_software", SqlDbType.NVarChar).Value = strOtherSW;
            cmd.Parameters.Add("@other_logon_list", SqlDbType.NVarChar).Value = strListOthLog;
            cmd.Parameters.Add("@additional_comments", SqlDbType.NVarChar).Value = strComment;
            cmd.Parameters.Add("@date_of_req", SqlDbType.DateTime).Value = strSysDt;
            if (ckInternet.Checked)
            {
                cmd.Parameters.Add("@internet_access", SqlDbType.Bit).Value = 1;
            }
            else
            {
                cmd.Parameters.Add("@internet_access", SqlDbType.Bit).Value = 0;
            }
            if (ckIntranet.Checked)
            {
                cmd.Parameters.Add("@intranet_access", SqlDbType.Bit).Value = 1;
            }
            else
            {
                cmd.Parameters.Add("@intranet_access", SqlDbType.Bit).Value = 0;
            }
            if (ckOthrLog.Checked)
            {
                cmd.Parameters.Add("@other_logons", SqlDbType.Bit).Value = 1;
            }
            else
            {
                cmd.Parameters.Add("@other_logons", SqlDbType.Bit).Value = 0;
            }
            //cmd.Parameters.Add("@Result", SqlDbType.NVarChar, 500).Direction = ParameterDirection.Output;
            //cmd.Parameters("@Result").Direction = ParameterDirection.Output;
            SqlParameter outPutParameter = new SqlParameter();
            outPutParameter.ParameterName = "@Result";
            outPutParameter.SqlDbType = System.Data.SqlDbType.NVarChar;
            outPutParameter.Size = 500;
            outPutParameter.Direction = System.Data.ParameterDirection.Output;
            cmd.Parameters.Add(outPutParameter);
            
            conn.Open();
            cmd.ExecuteNonQuery();
            //cmd.ExecuteScalar();           
            conn.Close();
            string Result = outPutParameter.Value.ToString();
            lblResultMsg.Text = Result;
            //lblResultMsg.Text = cmd.Parameters["@Result"].Value.ToString();

        }

    }
}>


I am getting error: Procedure or function has too many arguments specified.
Please help!

What I have tried:

I searched and tried a few solution without success.
Posted
Updated 14-Apr-21 8:04am
v2
Comments
Richard MacCutchan 17-Aug-16 11:05am    
You have 15 parameters in your stored procedure, but 16 in your SQL command.

Quote:
Please help!
Start by helping yourself!
It is a matter of counting !
The error message tells you that the SQL procedure expect a number of parameters and that the calling program gives a different number of parameters.

the solution: Take yourself by the hand and compare the 2 list of parameters, check line by line, check the parameter name on both sides ( 1 char difference and it don't match).
It can help to have the parameters in same order on both sides.
 
Share this answer
 
@date_of_req isn't a listed parameter.
 
Share this answer
 
Comments
Dave Kreskowiak 19-Oct-18 21:38pm    
Asked and answered with a better answer 2 years ago.
CHill60 21-Oct-18 5:22am    
Well done for spotting which parameter is causing the problem. But I would have worded this differently by pointing out that within the SP, the column date_of_req is being set to the explicit value getdate(). There is no parameter listed in the SP definition called @date_of_req, therefore the error can be corrected by removing the following line in the code-behind
cmd.Parameters.Add("@date_of_req", SqlDbType.DateTime).Value = strSysDt;
I commonly run into this issue in nodejs when reusing a request object. Create a new one if you are unsure if the object is clean.
 
Share this answer
 
Comments
Dave Kreskowiak 14-Apr-21 15:20pm    
You didn't bother to read Solution 1 before posting, did you?
Nathan Baker 2021 14-Apr-21 15:23pm    
I did read it but it led me in a different direction before finally identifying where the issue actually was. The list that was added was identical however the request was dirty from another request.
Dave Kreskowiak 14-Apr-21 15:39pm    
If you're commonly running into this problem, it's probably because you're constantly making the mistake of reusing objects instead of creating new ones every time you need to hit the database.
Nathan Baker 2021 14-Apr-21 15:42pm    
By commonly I meant this the 2nd time. Whacked that parameter out of my functions and forced them to do exactly this.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900