error message

[BigHeart]

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 

[Ancient Dragon]

It would be helpful to know what the error message(s) are.

[BigHeart]

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

Procedure or function Transaction has too many arguments specified

[darkagn]

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.

[BigHeart]

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

[Momerath]

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

[darkagn]

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