Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
i want my id as output when i submit the record i want to see the ID of that record

i m trying this i am not getting any thing


SQL
ALTER PROCEDURE [dbo].[Tech_sup] 
	
(
    
    
	@Build_name varchar(max),
	@Dept varchar(max),
	@Floor_no varchar(50),
	@Call_recvd varchar (max),
	@Ext_no int ,
	@Caller_name varchar(max) ,
	@Call_assign varchar(max),
	@problem varchar(max),
	@ip varchar(20) ,
	@Rec_Status int,
	@prob_type varchar(50),
	@id int output
	
	
	
)
AS
BEGIN
           
          
          INSERT INTO dbo.Tech_data (Build_name,Dept,Floor_no,Call_recvd,Ext_no,Caller_name,Call_assign,problem,ip,date_time,Rec_Status,prob_type)values (@Build_name,@Dept,@Floor_no,@Call_recvd,@Ext_no,@Caller_name,@Call_assign,@problem,@ip,getdate(),1,@prob_type)
            
        return @id

 END


c# code

C#
con.Open();
                   SqlCommand cmd = new SqlCommand();
                   cmd.CommandType = CommandType.StoredProcedure;
                   cmd.CommandText = "Tech_sup";
                   cmd.Parameters.AddWithValue("@Build_name", Build.Text);
                   cmd.Parameters.AddWithValue("@Dept", DropDownList2.Text);
                   cmd.Parameters.AddWithValue("@Floor_no", Floor.Text);
                   cmd.Parameters.AddWithValue("@Ext_no", Ext.Text);
                   cmd.Parameters.AddWithValue("@Call_recvd",DropDownList4.Text);
                   cmd.Parameters.AddWithValue("@Call_assign", DropDownList1.Text);
                   cmd.Parameters.AddWithValue("@Caller_name", Caller_Nm.Text);
                   cmd.Parameters.AddWithValue("@problem", Prob.Text);
                   cmd.Parameters.AddWithValue("@Rec_Status", SqlDbType.Bit);
                   cmd.Parameters.AddWithValue("@prob_type", DropDownList3.Text);
                   cmd.Parameters.Add("@id", SqlDbType.Int);
                   cmd.Parameters["@id"].Direction = ParameterDirection.Output;





                   if (string.IsNullOrEmpty(Ip.Text))
                   {
                       cmd.Parameters.Add(new SqlParameter("@ip", DBNull.Value.ToString()));
                   }
                   else
                   {
                       cmd.Parameters.Add(new SqlParameter("@ip", Ip.Text));
                   }
                   cmd.Connection = con;
                   cmd.ExecuteNonQuery();
                   con.Close();
                   TextBox1.Text = cmd.Parameters["@id"].Value.ToString();
                   ClientScriptManager cs = Page.ClientScript;
                   cs.RegisterStartupScript(this.GetType(), "id", "alert(" + cmd.Parameters["@id"].Value.ToString() + "');", true);
Posted

hi, after insert record on sp, write the below line

SEt @id=Select max(id) from dbo.Tech_data

then return @id.


you will get the output.


regards,
Prakash.T
 
Share this answer
 
Comments
$ultaNn 25-Feb-13 6:01am    
Error : Incorrect syntax near select statement
S R JHALA 25-Feb-13 6:23am    
Select @id = max(id) from dbo.Tech_data
Hi Dear,
Please do change in your code as below
Stored Procuedure
SQL
ALTER PROCEDURE [dbo].[Tech_sup] 
(
    	@Build_name varchar(max),
	@Dept varchar(max),
	@Floor_no varchar(50),
	@Call_recvd varchar (max),
	@Ext_no int ,
	@Caller_name varchar(max) ,
	@Call_assign varchar(max),
	@problem varchar(max),
	@ip varchar(20) ,
	@Rec_Status int,
	@prob_type varchar(50),
	@id int output
)
AS
BEGIN
        set @id = 0  
          
          INSERT INTO dbo.Tech_data (Build_name,Dept,Floor_no,Call_recvd,Ext_no,Caller_name,Call_assign,problem,ip,date_time,Rec_Status,prob_type)values (@Build_name,@Dept,@Floor_no,@Call_recvd,@Ext_no,@Caller_name,@Call_assign,@problem,@ip,getdate(),1,@prob_type)
            
        set @id = Scope_identity()
 
 END
 
Share this answer
 
If the Id column is identity then write the following line after the insert statement
SET @id = @@IDENTITY

Then return @id
 
Share this answer
 
What Mahesh has wrote is correct you just need to set the @id variable and no need for return statement in Stored Procedure, also in code you set the @id parameter direction to Output which is correct.

C#
cmd.ExecuteNonQuery(); 

return the number of rows affected so you can check on your code side if its greater than zero(0) than some operation has been made.
 
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