Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL2000 SQL-server-2005 C#3.0 , +
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
 

 
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
 
 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 25-Feb-13 0:44am
Lubnash833
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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
  Permalink  
Comments
Lubnash at 25-Feb-13 6:01am
   
Error : Incorrect syntax near select statement
S R JHALA at 25-Feb-13 6:23am
   
Select @id = max(id) from dbo.Tech_data
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Hi Dear,
Please do change in your code as below
Stored Procuedure
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
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

If the Id column is identity then write the following line after the insert statement
SET @id = @@IDENTITY
Then return @id
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

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.
 
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.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 386
1 Marcin Kozub 225
2 Sergey Alexandrovich Kryukov 215
3 Maciej Los 169
4 /\jmot 169
0 OriginalGriff 8,289
1 Sergey Alexandrovich Kryukov 7,407
2 DamithSL 5,624
3 Maciej Los 4,989
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web03 | 2.8.1411023.1 | Last Updated 25 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100