I am working with inserting customer complaints and want to get the complaint id back when the insert is complete.  So far I have the complaint being inserted without problem but I am not getting the complaint id back in the program.
SQL Insert procedure:
ALTER PROCEDURE [dbo].[InsertComplaint]

	@fcomp_assign CHAR(30),
	@fcomp_bu CHAR(25),			-- required
	@fcomp_cntct_id SMALLINT,			-- required
	@fcomp_create_by CHAR(30),			-- required
	@fcomp_cust CHAR(8),			-- required
	@fcomp_desc CHAR(8000),			-- required
	@fcomp_ftr BIT,				-- required
	@fcomp_inv CHAR(8),
	@fcomp_mor CHAR(8),
	@fcomp_reason CHAR(50),			-- required
	@fcomp_rep CHAR(8),			-- required
	@fcomp_so CHAR(8),
	@fcomp_src CHAR(8000),
	@fcomp_status CHAR(8),
	@fcomp_urgency CHAR(8),
	@fcomp_weight DECIMAL,			-- required 
	@fch_act_type CHAR(10),
	@fch_cmmt CHAR(8000),
	@fcomp_id INT OUTPUT

AS
BEGIN

DECLARE @fcomp_id SMALLINT 

INSERT INTO fcomp_mstr (
		fcomp_assign
		, fcomp_bu				
		, fcomp_cntct_id 			
		, fcomp_create_by			
		, fcomp_create_dt
		, fcomp_cust			
		, fcomp_desc			
		, fcomp_ftr					
		, fcomp_inv
		, fcomp_mor
		, fcomp_reason			
		, fcomp_rep				
		, fcomp_so
		, fcomp_src 
		, fcomp_status 
		, fcomp_urgency
		, fcomp_weight 			
	)
		VALUES (
		@fcomp_assign
		, @fcomp_bu				
		, @fcomp_cntct_id 			
		, @fcomp_create_by			
		, getDate()
		, @fcomp_cust			
		, @fcomp_desc			
		, @fcomp_ftr					
		, @fcomp_inv
		, @fcomp_mor
		, @fcomp_reason			
		, @fcomp_rep				
		, @fcomp_so
		, @fcomp_src 
		, @fcomp_status 
		, @fcomp_urgency
		, @fcomp_weight 		
	)

SET @fcomp_id = scope_identity()

END
 Code behind: (removed some code for reading ease)
    Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim iCount As Integer = 0
        Dim return_id As New System.Data.SqlClient.SqlParameter("@fcomp_id", Data.SqlDbType.Int)
        return_id.Direction = Data.ParameterDirection.Output
        
        srcComplaint.InsertParameters("fcomp_id").DefaultValue = txtReturn.Text
        
        srcComplaint.Insert()

        srcComplaint.DataBind()
        lblReturnCompId.Text = "ReturnId: " & CStr(return_id.Value)
        panSuccess.Visible = True

    End Sub
 ASP code:
<asp:SqlDataSource ID="srcComplaint" runat="server" 
            ConnectionString="<%$ ConnectionStrings:FastSqlServer %>"
            SelectCommand="SELECT * FROM fcomp_mstr"
            InsertCommand="InsertComplaint" 
            InsertCommandType="StoredProcedure" > 
        <InsertParameters>
            <asp:Parameter Name="fcomp_assign" Type="String" />
            <asp:Parameter Name="fcomp_bu" Type="String" />
            <asp:Parameter Name="fcomp_cntct_id" Type="Int32" />
            <asp:Parameter Name="fcomp_create_by" Type="String" />
            <asp:Parameter Name="fcomp_cust" Type="String" />
            <asp:Parameter Name="fcomp_desc" Type="String" />
            <asp:Parameter Name="fcomp_ftr" Type="String" />
            <asp:Parameter Name="fcomp_inv" Type="String" />
            <asp:Parameter Name="fcomp_mor" Type="String" />
            <asp:Parameter Name="fcomp_reason" Type="String" />
            <asp:Parameter Name="fcomp_rep" Type="String" />
            <asp:Parameter Name="fcomp_so" Type="String" />
            <asp:Parameter Name="fcomp_src" Type="String" />
            <asp:Parameter Name="fcomp_status" Type="String" />
            <asp:Parameter Name="fcomp_urgency" Type="String" />
            <asp:Parameter Name="fch_act_type" Type="String" />
            <asp:Parameter Name="fch_cmmt" Type="String" />
            <asp:Parameter Name="fcomp_weight" Type="Decimal" />
            <asp:Parameter Name="fcomp_id" Direction="Output" Type="Int32" />
         </InsertParameters>           
</asp:SqlDataSource>

Remember to mark as answer if this post answered your question.

at the end of the storedProcedure , 
instead of this : SET @fcomp_id = scope_identity()
write this : select  scope_identity()
 
and remove the @fcomp_id parameter from the storedProcedure parameteres 
 and in the insert code :
 
    Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim iCount As Integer = 0
        dim InsertedID as integer= srcComplaint.Insert()
        srcComplaint.DataBind()
        lblReturnCompId.Text = "ReturnId: " & CStr(InsertedID )
        panSuccess.Visible = True

    End Sub
 
Regards,

Anas Ghanem.


Note:Please Don't hesitate to click "Report Abuse" link if you noticed something wrong on the forums (like duplicate ,Off-topic,offensive,or any post that violates the website "TERMS OF USE"). -- Thanks!

Tried this and get 2 as an answer when it should have been 67.  Here is my code I changed:
 
ALTER PROCEDURE [dbo].[InsertComplaint]

	@fcomp_assign CHAR(30),
	@fcomp_bu CHAR(25),				-- required
	@fcomp_cntct_id SMALLINT,		-- required
	@fcomp_create_by CHAR(30),		-- required
	@fcomp_cust CHAR(8),			-- required
	@fcomp_desc CHAR(8000),			-- required
	@fcomp_ftr BIT,					-- required
	@fcomp_inv CHAR(8),
	@fcomp_mor CHAR(8),
	@fcomp_reason CHAR(50),			-- required
	@fcomp_rep CHAR(8),				-- required
	@fcomp_so CHAR(8),
	@fcomp_src CHAR(8000),
	@fcomp_status CHAR(8),
	@fcomp_urgency CHAR(8),
	@fcomp_weight DECIMAL,			-- required 
	@fch_act_type CHAR(10),
	@fch_cmmt CHAR(8000)

AS
BEGIN
DECLARE @fcomp_id SMALLINT 

INSERT INTO fcomp_mstr (
		fcomp_assign
		, fcomp_bu				
		, fcomp_cntct_id 			
		, fcomp_create_by			
		, fcomp_create_dt
		, fcomp_cust			
		, fcomp_desc			
		, fcomp_ftr					
		, fcomp_inv
		, fcomp_mor
		, fcomp_reason			
		, fcomp_rep				
		, fcomp_so
		, fcomp_src 
		, fcomp_status 
		, fcomp_urgency
		, fcomp_weight 			
	)
		VALUES (
		@fcomp_assign
		, @fcomp_bu				
		, @fcomp_cntct_id 			
		, @fcomp_create_by			
		, getDate()
		, @fcomp_cust			
		, @fcomp_desc			
		, @fcomp_ftr					
		, @fcomp_inv
		, @fcomp_mor
		, @fcomp_reason			
		, @fcomp_rep				
		, @fcomp_so
		, @fcomp_src 
		, @fcomp_status 
		, @fcomp_urgency
		, @fcomp_weight 		
	)
 
-- assign auto generated id for complaint
SELECT @fcomp_id = scope_identity()

INSERT INTO fch_hist (
		fch_act_type
		, fch_assign
		, fch_by
		, fch_cmmt
		, fch_comp_id
		, fch_dt
	)
	VALUES (
		@fch_act_type
		, @fcomp_assign
		, @fcomp_create_by
		, @fch_cmmt
		, @fcomp_id
		, getDate()
) 

SELECT scope_identity()
END
      Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim iCount As Integer = 0

        srcComplaint.Insert()
        Dim return_id As Integer = srcComplaint.Insert()
        srcComplaint.DataBind()
        lblReturnCompId.Text = "ReturnId: " & CStr(return_id)
        panSuccess.Visible = True

    End Sub
 

Remember to mark as answer if this post answered your question.

HI
yes it seeems that Insert function will returns the Number of  rows affected instead of the last id !
ok back to your original  code , I realized that you declared the fcomp_id parameter twice !
one in the datasource declaration (ASPX code) and one in button  click ! why ? you don't need that !
Another issue ! are you working with some datacontrols like FormView , details view ???
If not , then you must set the values for all the paremeters in the code ! 
 
Update :
try to use Inserted event handler of the datasoruce 
Protected Sub SqlDataSource1_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Inserted 
Dim param As System.Data.Common.DbParameter = e.Command.Parameters("fcomp_id")
Dim fcomp_id As Integer = param.Value 
End Sub
 
Regards,

Anas Ghanem.


Note:Please Don't hesitate to click "Report Abuse" link if you noticed something wrong on the forums (like duplicate ,Off-topic,offensive,or any post that violates the website "TERMS OF USE"). -- Thanks!

So where do I add the "fcomp_id" parameter to?  
I've been working on this too long, I'm confused! 

Remember to mark as answer if this post answered your question.

Hi
Its not usefull to use the DataSource controls model when you want to manually execute a storedProcedure !
the datasoruce model can be used in conjunction with the Data Presentation controls (like FromsView and details view ... gridView ..)
but for this case , you need to manually declare a connection, and a command and add parameters to it , then you need to exceute the command and return the scalar value :
you need to use the classed inside System.Data.SqlClient Namespace : SqlConnection  SqlCommand and SqlParameter....
so please delete the SqlDatasource declaration , and use the method in this link :
http://aspnet.4guysfromrolla.com/articles/062905-1.aspx
Regards,

Anas Ghanem.


Note:Please Don't hesitate to click "Report Abuse" link if you noticed something wrong on the forums (like duplicate ,Off-topic,offensive,or any post that violates the website "TERMS OF USE"). -- Thanks!

Thanks for the help!  The link you mentioned worked out great!
Thanks again!

Remember to mark as answer if this post answered your question.

you welcome , I'm happy because it helps ,
Note that the sqlDatasource can be usefull if you used it with one of  data Presentaion controls , so that the control will be responsible for passing and settings the SqlDataSource Parameters,
But when you want to execute a stored procedure (or sql statment ) manually to get some value ,
then its better to use the  System.Data.SqlClient classes ,
 
Thanks , and good luck
Regards,

Anas Ghanem.


Note:Please Don't hesitate to click "Report Abuse" link if you noticed something wrong on the forums (like duplicate ,Off-topic,offensive,or any post that violates the website "TERMS OF USE"). -- Thanks!