Store SQL return parameter in VB.NET variable

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>

View Brenden Kehren's profile on LinkedIn
Remember to mark as answer if this post answered your question.
0
b471code3
3/30/2008 7:51:03 PM
asp.net.web-forms 93655 articles. 6 followers. Follow

7 Replies
1529 Views

Similar Articles

[PageSpeed] 5

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!

0
anas
3/30/2008 9:49:09 PM

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

 


View Brenden Kehren's profile on LinkedIn
Remember to mark as answer if this post answered your question.
0
b471code3
3/30/2008 10:22:09 PM

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!

0
anas
3/30/2008 10:35:00 PM

So where do I add the "fcomp_id" parameter to? 

I've been working on this too long, I'm confused! Confused


View Brenden Kehren's profile on LinkedIn
Remember to mark as answer if this post answered your question.
0
b471code3
3/31/2008 1:25:49 PM

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!

0
anas
4/1/2008 5:13:56 PM

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

Thanks again!


View Brenden Kehren's profile on LinkedIn
Remember to mark as answer if this post answered your question.
0
b471code3
4/2/2008 6:03:07 PM

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!

0
anas
4/2/2008 6:17:01 PM
Reply: