Click here to Skip to main content
15,867,308 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 :-)

 
Share this answer
 
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;
           }
 
Share this answer
 
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?
Hi Use the below line.

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


Hope this helps
 
Share this answer
 
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.
Hi abdul,you can try it like this

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

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