locked
Error message Procedure or function expects parameter '@idcount', which was not supplied.

    Question

  • getting this error while inserting near ExecuteNonQuery();
    
    Procedure or function 'Transaction' expects parameter '@idcount', which was not supplied.
    
      try
                {
                    if (NameTxtBx.Text == "" || ToTxtBx0.Text == "" || DropDownList1.SelectedIndex == 0 || RadioButtonList2.SelectedIndex == 0 || (PatID_NO.Text == "") || (Convert.ToUInt64(PatNo.Text) <= 0) || (Convert.ToUInt64(PatNo.Text) > 200000000))
                    {
                        Message("Please enter all fields", this);
                        return;
    
                    }
                    else
                    {
                        try
                        {
                            
                        
                            string Patient_name = NameTxtBx.Text, Export_TO = ToTxtBx0.Text  ;
                            int PatNoVal;
                            PatNoVal = Convert.ToInt32(PatNo.Text);
                            PatNoVal = int.Parse(PatNo.Text);
                            decimal PatID = decimal.Parse(PatID_NO.Text);
                            int? replay_To_type = Int16.Parse(DropDownList1.SelectedValue);
                            int? reptype = Int16.Parse(RadioButtonList2.SelectedValue);
                            try
                            {
    
                               con.Open();
                                SqlCommand cmd = new SqlCommand();
                                cmd.CommandType = CommandType.StoredProcedure;
                                cmd.CommandText = "Transaction";
                                cmd.Parameters.AddWithValue("@Patient", Patient_name);
                                cmd.Parameters.AddWithValue("@E_TO", Export_TO);
                                cmd.Parameters.AddWithValue("@R_type", reptype);
                                cmd.Parameters.AddWithValue("@ReportType", replay_To_type);
                                cmd.Parameters.AddWithValue("@Patient_no", PatNoVal);
                                cmd.Parameters.AddWithValue("@Patient_ID_NO", PatID);
                                cmd.Parameters.AddWithValue("@User_name", Label1.Text = Session["name"].ToString ());
                                //cmd.Parameters.Add("@idcount", SqlDbType.Decimal);
                                //cmd.Parameters["@idcount"].Direction = ParameterDirection.Output;
                                cmd.Parameters.Add("@idcount", SqlDbType.Decimal);
                                cmd.Parameters.Add("@ReturnIDcount", SqlDbType.NVarChar,50);
                                cmd.Parameters["@ReturnIDcount"].Direction = ParameterDirection.Output;
                                cmd.Connection = con;
                              cmd.ExecuteNonQuery(); <<<--- error in this line 
                                con.Close();
                                TextBox1.Text = cmd.Parameters ["@ReturnIDcount"].Value.ToString();
                                ClientScriptManager cs = Page.ClientScript;
                                cs.RegisterStartupScript(this.GetType(), "IDCount", "alert('YourID is ID :" + cmd.Parameters["@ReturnIDcount"].Value.ToString() + "');", true);
                                
                           
                            }
                            catch (Exception ex)
                            {
                                Message("Block 3: Error\nThe Reported fault is:\n" + ex.Message, this);
                            }
                            PatNo.Text = "";
                            NameTxtBx.Text = "";
                            ToTxtBx0.Text = "";
                            DropDownList1.SelectedIndex = -1;
                            RadioButtonList2.SelectedIndex = -1;
                            }
                        catch
                        {
                            Message(" ERROR 20", this);
                        }
    
                    }
                }
                catch
                {
                    Message("Error: Make sure that you are entering the data properly ", this);
                }

    stored procedure

    ALTER PROCEDURE [dbo].[Transaction]  

               
                @Patient nvarchar(50),
                @E_TO nvarchar(50),
                @R_type int,
                @User_name nvarchar(50),
                @ReportType int,
                @Patient_no  int,
                @Patient_ID_NO numeric(18,0),
                @idcount numeric(18,0) ,
                @ReturnIDcount  nvarchar(50) output
       

    AS  
    BEGIN  
               
             declare @tempid numeric(18,0)
             set @tempid = 0;  
             declare @idcnt numeric(18,0)
             select @idcnt =isnull( max(idcount),0) from Transactions where year(R_date)=year(getdate())  
             if (@idcnt =0)  
             set @tempid=1  
             else  
             set @tempid = @idcnt +1  
             
              
            
              
              INSERT INTO dbo.Transactions (Patient,E_TO,R_date,R_from,User_name,report_type,Patient_no,Patient_ID_NO,idcount)
              values 
              (@Patient,@E_TO,getdate(),@R_type,@User_name,@ReportType,@Patient_no,@Patient_ID_NO,@tempid)
              select @idcount =isnull( max(idcount),0) from Transactions where year(R_date)=year(getdate())
               set @ReturnIDcount = '301A' + cast( @idcount as varchar)
              return @ReturnIDcount
            
     END 


    New born in developing

    Saturday, July 28, 2012 10:25 AM

All replies

  • You didnt not pass any parameter for "idcount" field. Change this line of code to:

      cmd.Parameters.Add("@idcount", SqlDbType.Decimal).Value = 1; //pass some id value!


    Mitja

    Saturday, July 28, 2012 11:00 AM
  • The error "Procedure or function expects parameter '@idcount', which was not supplied" occurs when the parameter does not accept NULL values, and you have not provided a value for the parameter in your client code. Specifically, your code does

    cmd.Parameters.Add("@idcount", SqlDbType.Decimal);

    which adds the parameter to the SqlCommand, but you never provide a value for the parameter.

    One way in which you can set the value is this:

    cmd.Parameters["@idcount"].Value = something;

    This problem does not happen with the rest of the parametes because you are using AddWithValue or you are changing the ParameterDirection.

    Looking at your stored procedure, it looks like @idcount should be an OUTPUT parameter. If you add the OUTPUT keyword to its declaration, then you can set the ParameterDirection to Output in the client code (you actually have this inside a comment). In this case, you don't have to assign the .Value to the parameter. 

    Saturday, July 28, 2012 11:08 AM
  • .first my stored procedure was like this. its used to insert the data and increment idcount by one till the end of the year when the next year starts it will change to 1 again.

    then i get requirement to add 301A before idcount nuber so that it should save and display like this

    301A1

    301A2

    301A3

    so on till the year ends 

    .

    .

    301A100000

    when next year starts 

    301A1

    .

    .

    .

    ALTER PROCEDURE [dbo].[Transaction]  
    ( 
               
                @Patient nvarchar(50),
                @E_TO nvarchar(50),
                @R_type int,
                @User_name nvarchar(50),
                @ReportType int,
                @Patient_no  int,
                @Patient_ID_NO numeric(18,0),
                @idcount numeric(18,0) output
       
    ) 
    AS  
    BEGIN  
               
             declare @tempid numeric(18,0)
             set @tempid = 0;  
             declare @idcnt numeric(18,0)
             select @idcnt =isnull( max(idcount),0) from Transactions where year(R_date)=year(getdate())  
             if (@idcnt =0)  
             set @tempid=1  
             else  
             set @tempid = @idcnt +1  
          
            
              
              INSERT INTO dbo.Transactions (Patient,E_TO,R_date,R_from,User_name,report_type,Patient_no,Patient_ID_NO,idcount)
              values 
              (@Patient,@E_TO,getdate(),@R_type,@User_name,@ReportType,@Patient_no,@Patient_ID_NO,@tempid)
              select @idcount =isnull( max(idcount),0) from Transactions where year(R_date)=year(getdate())
               
                       return @idcount
     END 



    New born in developing

    Saturday, July 28, 2012 11:24 AM
  • You do need @idcount as a parameter in the SP!
    It should be declared as a local variable.
    So simply remove it from the SP signature and the Command.Paramters collection

    in C#, remove this line:
    cmd.Parameters.Add("@idcount", SqlDbType.Decimal);
    T-SQL:
    ALTER PROCEDURE [dbo].[Transaction]  
    ( 
               
                @Patient nvarchar(50),
                @E_TO nvarchar(50),
                @R_type int,
                @User_name nvarchar(50),
                @ReportType int,
                @Patient_no  int,
                @Patient_ID_NO numeric(18,0),
                @ReturnIDcount  nvarchar(50) output
       
    ) 
    AS  
    BEGIN  
               
             declare @tempid numeric(18,0)
             declare @idcount numeric(18,0)
    
             select @tempid = (isnull( max(idcount),0) + 1) from Transactions where year(R_date)=year(getdate())  
            
              
              INSERT INTO dbo.Transactions (Patient,E_TO,R_date,R_from,User_name,report_type,Patient_no,Patient_ID_NO,idcount)
              values 
              (@Patient,@E_TO,getdate(),@R_type,@User_name,@ReportType,@Patient_no,@Patient_ID_NO,@tempid)
              select @idcount =isnull( max(idcount),0) from Transactions where year(R_date)=year(getdate())
               set @ReturnIDcount = '301A' + cast( @idcount as varchar)
              return @ReturnIDcount
            
     END  

    Saturday, July 28, 2012 4:07 PM
  • Getting this error in execute onquery();

    Conversion failed when converting the nvarchar value '301A11' to data type int.

    and 301A11 is not saving in idcount or ReturnIDcount column???


    New born in developing

    Sunday, July 29, 2012 7:44 AM
  • So there still is an error with the datatypes...
    Since you've being posting multiple version of your code:
    What is the type of the "@ReturnIDcount" parameter in the SP?
    Should be "nvarchar(50) output".
    Same is true for the DataType of the CommandParameter in C#
    it should be SqlDataType.NVarChar, 50.

    Since the error reported complains about '301A11' being unconvertible to int
    there actually still is code that tries and assigns this value to an
    integer variable. I can't decide which statement this is, since I don't know
    the code you effectively run.

    As for: '301A11' not being stored to a column.
    Which statement to you expect to do that?
    The sole INSERT statement will store "@tempid" (with the value of '11' in your example)
    to  "dbo.Transactions.idcount".
    There is no INSERT in your code to store the '301A'-prefixed string to whatever column/table
    it's just returned by the SP as an out and then displayed in a TextBox1.

    Chris

    Sunday, July 29, 2012 1:44 PM
  • i am confusing u sorry ....!!

    idcount is used to increment by for one year when next year starts it will change to 1 again , there is a column idcount from there i display idcount in a TextBOX , ok 

    now i have to change that process to add 301A before idcount number like 301A1,301A2 so on.... 

    so i added a new column ReturnIDcount in table to save 301A1 , 301A2 ... and i am not getting how to save 301A1 in ReturnIDcount column,, 

    i tried ur code again now the error i am getting near ExecuteNonQuery(); is

    Procedure or function Transaction has too many arguments specified




    New born in developing

    Sunday, July 29, 2012 9:10 PM
  • You need to keep the SQL code of your MSSQL stored proc
    and the C# code that runs this stored proc in snyc.

    "Procedure or function Transaction has too many arguments specified"
    means that the call to the SP "Transcation" executed in your app
    supplies too many parameters.
    If you remove a parameter in SQL, you also need to remove from Parameter
    collection - so that every parameter in SQL has a counterpart in C#.

    Chris

    Monday, July 30, 2012 3:27 PM
  • how to save 301A1 in ReturnIDcount column,,  ???

    New born in developing

    Saturday, August 04, 2012 8:09 AM