Click here to Skip to main content
15,349,320 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi guys,

i have wrote an stored procedure with type output, as well.

n

i'm passing the other parameters to the stored procedure but how to get the
parameter which is of type output.

Stored Procedure:

GO
/****** Object:  StoredProcedure [dbo].[Sp_JobStep1]    Script Date: 07/20/2014 22:40:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Proc [dbo].[Sp_JobStep1]
(
      @JobName nvarchar(255)         
     ,@JobCode nvarchar(255)        
     ,@StartDate date       
     ,@EndDate date        
     ,@JobID int output
)
as

begin

insert into Job
	(
	JobName,
	JobCode,
	StartDate,
	EndDate
	)        
 select   
		  @JobName          
		 ,@JobCode         
		 ,@StartDate        
		 ,@EndDate         
		 
		 
select @JobID = @@IDENTITY from Job
     
end


c# code:

SqlParameter[] Param = new SqlParameter[11];
                Param[0] = new SqlParameter("@JobName", Jobname);
                Param[1] = new SqlParameter("@JobCode", JobCode);
                Param[2] = new SqlParameter("@StartDate", strDate);
                Param[3] = new SqlParameter("@EndDate", endDate);
                Param[4] = new SqlParameter("@Client", Client);
                Param[5] = new SqlParameter("@JobDesc", JobDesc);
                Param[6] = new SqlParameter("@Rig", rig);
                Param[7] = new SqlParameter("@Well", well);
                Param[8] = new SqlParameter("@SectionID", Section);
                Param[9] = new SqlParameter("@hours", hours);
                Param[10] = new SqlParameter("@JobID", SqlDbType.Int);
                Param[10].Direction = ParameterDirection.Output;

                if (Section > 0)
                {
                    DAL.Insertion("Sp_JobStep1", Param);//DataAccessLayer
                }

                string JobID = Param["@JobID"].Value.ToString();//Error: Cannot implicitly convert string to int ["@JobID"]

//how to deal with the sql parameter of type 'output' n its value.


plzzz suggest me.


Thanks
Posted
Updated 27-Apr-22 0:20am
v3
Comments
[no name] 20-Jul-14 17:29pm
   
try int jobid = Param["@JobID"].Value; after you execute your query.
abdul subhan mohammed 21-Jul-14 7:49am
   
i'm getting error on this line,
string jid = Param["@JobID"].Value.ToString();//Cannot implicitly convert string to int [on "@jobid"]
Dilan Shaminda 21-Jul-14 9:14am
   
try it like this,
string jid= Param[10].Value.ToString();
abdul subhan mohammed 21-Jul-14 9:27am
   
Thank you v.much Hard_Rockz...
its works.
thanks again.
Dilan Shaminda 21-Jul-14 9:38am
   
My pleasure.Happy coding :-)

Hi abdul,you can try it like this

C#
param[4] = new SqlParameter("@JobID",SqlDbType.Int);
param[4].Direction = ParameterDirection.Output;
   
Comments
abdul subhan mohammed 21-Jul-14 14:34pm
   
string JobID = Param[10].Value.ToString();
Hi Use the below line.

string JobID = objSQLCommand.Parameters["@JobID"].Value.ToString()


Hope this helps
   
Comments
abdul subhan mohammed 21-Jul-14 9:21am
   
i'm calling the stored procedure through dataAccesslayer using sqlhelper class.
if (Section > 0)
{
DAL.Insertion("Sp_JobStep1", Param);//DataAccessLayer
}
so, how can i use cmd.parameter["@jobid"].value.tostirng();
Ranjeet Patel 21-Jul-14 9:38am
   
So you are using the DAL.Insertion method which is static method but It should return a parameter jobId(but you are not returning any thing) that you can use. go to the Insertion method and use the objSQLCommand.Parameters["@JobID"].Value.ToString() there.
Thanks to Hard_Rockz, with the help of him, i solved this issue.

i'm sticking my code for Ref#

try
           {
               string Jobname = txtjobname.Text;
               string JobCode = txtjobcode.Text;
               DateTime strDate = Convert.ToDateTime(txtsdate.Text);
               DateTime endDate = Convert.ToDateTime(txtedate.Text);
               string Client = txtclient.Text;
               string JobDesc = txtjobdescription.Text;
               int rig = Convert.ToInt32(ddlrig.SelectedValue);
               int well = Convert.ToInt32(ddlwell.SelectedValue);
               int Section = Convert.ToInt32(ddlSection.SelectedValue);
               string hours = txtehrs.Text;

               SqlParameter[] Param = new SqlParameter[11];
               Param[0] = new SqlParameter("@JobName", Jobname);
               Param[1] = new SqlParameter("@JobCode", JobCode);
               Param[2] = new SqlParameter("@StartDate", strDate);
               Param[3] = new SqlParameter("@EndDate", endDate);
               Param[4] = new SqlParameter("@Client", Client);
               Param[5] = new SqlParameter("@JobDesc", JobDesc);
               Param[6] = new SqlParameter("@Rig", rig);
               Param[7] = new SqlParameter("@Well", well);
               Param[8] = new SqlParameter("@SectionID", Section);
               Param[9] = new SqlParameter("@hours", hours);
               Param[10] = new SqlParameter("@JobID", SqlDbType.Int);
               Param[10].Direction = ParameterDirection.Output;

               if (Section > 0)
               {
                   DAL.Insertion("Sp_JobStep1", Param);
               }

               string JobID = Param[10].Value.ToString();//Solved

               Response.Redirect("JobStep2.aspx?JobID=" + JobID);

           }
           catch (Exception)
           {

               throw;
           }
   
Comments
Dilan Shaminda 21-Jul-14 9:37am
   
I am so glad that i could help you.But can i ask you formally why my answer is down voted?

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