cant save ReturnIDcount in database table .

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

ohh sorry ..
i am getting error message near ExecuteNonQuery();

Procedure or function Transaction has too many arguments specified

Remove the dbo. from the INSERT query. It thinks you are trying to call your procedure recursively rather than insert directly to the table Transactions. BTW naming is important in a database, and TRANSACTION is a special SQL word. Try using a more descriptive name for the procedure, such as CREATETRANSACTION which actually describes what the procedure does.

EDIT: Sorry, I misread your SQL. Can you post the code that calls this procedure, I am thinking that you have too many parameters listed for your execution.

Edited 4 Years Ago by darkagn

Conversion failed when converting the nvarchar value '301A1' to data type int
gettig this error in executeNonQuery

tried changing the procedure code

ALTER PROCEDURE [dbo].[usp_NEW_TRANSACTIONS] 

(

            @Patient nvarchar(50),
            @E_TO nvarchar(50),
            @R_type int,
            @User_name nvarchar(50),
            @ReportType nvarchar(50),
            @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,ReturnIDcount)values (@Patient,@E_TO,getdate(),@R_type,@User_name,@ReportType,@Patient_no,@Patient_ID_NO,@tempid,@ReturnIDcount)
          select @idcount =isnull( max(idcount),0) from Transactions where year(R_date)=year(getdate())
          set @ReturnIDcount = '301A' + cast( @idcount as numeric)
          return @ReturnIDcount


 END

C# code

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 = "usp_NEW_TRANSACTIONS";
                            cmd.Parameters.AddWithValue("@Patient", Patient_name);
                            cmd.Parameters.AddWithValue("@E_TO", Export_TO);
                            cmd.Parameters.AddWithValue("@R_type", reptype);
                            cmd.Parameters.AddWithValue("@User_name", Label1.Text = Session["name"].ToString());
                            cmd.Parameters.AddWithValue("@ReportType", replay_To_type);
                            cmd.Parameters.AddWithValue("@Patient_no", PatNoVal);
                            cmd.Parameters.AddWithValue("@Patient_ID_NO", PatID);
                            cmd.Parameters.Add("@ReturnIDcount", SqlDbType.NVarChar, 50);
                            cmd.Parameters["@ReturnIDcount"].Direction = ParameterDirection.Output;
                            cmd.Connection = con;
                            cmd.ExecuteNonQuery();
                            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);

It's returning a value so it isn't a NonQuery

ExecuteNonQuery() should be correct for executing a stored procedure.

set @ReturnIDcount = '301A' + cast( @idcount as numeric)

I would say this line in your procedure is incorrect. You are casting @idcount to a numeric field and attempting to append to a varchar, and @ReturnIDcount is listed as datatype nvarchar(max). To be sure, wrap the cast in another cast to an nvarchar type like so:

SET @ReturnIDcount = '301A' + CAST(CAST(@IdCount AS NUMERIC) AS NVARCHAR(40))
This article has been dead for over six months. Start a new discussion instead.