getting this error while inserting near ExecuteNonQueryProcedure or function Transaction expects parameter idcount which was not supplied1 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970try if NameTxtBxText ToTxtBx0Text DropDownList1SelectedIndex 0 RadioButtonList2SelectedIndex 0 PatIDNOText ConvertToUInt64PatNoText lt

Error message in ExecutenonQuery: Procedure or function expects parameter which was not supplied

BigHeart

SOLVED CLOSED ACTIVE ANSWERED
User: BigHeart
Posted: on Jul 28, 2012 08:35 AM
Forum: ASP.Net Web Forms
Replies: 11
Views: 10277

getting this error while inserting near ExecuteNonQuery();

Procedure or function 'Transaction' expects parameter '@idcount', which was not supplied.

1

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(max) 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

Download FREE API for Word, Excel and PDF in ASP.Net

Mudassar

0 1

Reply
User: Mudassar
Replied: on Jul 28, 2012 08:38 AM
Report

Comment the line

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

And use the following stored proc

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(max) output
    
)
AS 
BEGIN 
         declare @idcount numeric(18,0)
         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

What I did was declared @idcount as variable in stored proc instead of parameter as there is no need to pass this from outside

BigHeart

0 2

Reply
User: BigHeart
Replied: on Jul 29, 2012 02:32 AM
Report

tried ur steps

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???

Mudassar

0 3

Reply
User: Mudassar
Replied: on Jul 29, 2012 04:07 AM
Report

Try this

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(max) output
     
)
AS
BEGIN
         declare @idcount nvarchar(100)         
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

BigHeart

0 4

Reply
User: BigHeart
Replied: on Jul 29, 2012 04:31 AM
Report

idcount is mumeric(18,0) in table design

error on executenonquery();

Procedure or function Transaction has too many arguments specified.

Mudassar

0 5

Reply
User: Mudassar
Replied: on Jul 29, 2012 04:49 AM
Report

So then how come the error is coming. Why don't you first make sure that your Stored proc is correct with all datatypes

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Error message in ExecutenonQuery: Procedure or function expects parameter which was not supplied

Company

Explore

Follow us

Contact Us