Click here to Skip to main content
15,867,834 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;
 
Share this answer
 
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
 
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.
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?
 
Share this answer
 

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