With reference to the following link: http://www.aspforums.net/Threads/154663/No-duplicate-value-during-insertion-of-record/
I have a stored procedure that inserts to two different tables. What i'd like to know is how to retrieve just inserted identity column and insert it to another table along with textbox, dropdownlist value
Stored Procedure:
ALTER PROCEDURE [dbo].[InsertSalesOrder]
@SONumber bigint
,@FirstName Varchar(50)
,@SODate datetime
,@OrderStatus Varchar(50)
,@CreatedBy Varchar(50)
,@DateCreated datetime
,@ApproveBy Varchar(50)
,@DateApproved datetime
,@QTY bigint, @USP money, @DiscountPercent bigint, @DiscountAmt money,@Amount money
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CustID bigint
SET @CustID =(Select CustID FROM Customer WHERE FirstName = @FirstName)
INSERT INTO Sales_Order(SONumber,CustID, SODate, OrderStatus,CreatedBy,DateCreated,ApproveBy,DateApproved)
VALUES(@SONumber, @CustID, @SODate, @OrderStatus,@CreatedBy,@DateCreated,@ApproveBy,@DateApproved)
Declare @SalesOrderID Bigint
SELECT @SalesOrderID = SCOPE_IDENTITY();
INSERT INTO Sales_OrderDetails(SalesOrderID,QTY,
USP,DiscountPercent,DiscountAmt,Amount)
VALUES(@SalesOrderID,@QTY,
@USP,@DiscountPercent,@DiscountAmt,@Amount)
END
Code Behind:
Protected Sub Save2DB(ByVal sender As Object, ByVal e As EventArgs)
Dim con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("SY_InventoryConnectionString").ConnectionString)
Dim cmd As SqlCommand = New SqlCommand("InsertSalesOrder", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@SONumber", SqlDbType.BigInt).Value = txtSOno.Text
cmd.Parameters.Add("@FirstName", SqlDbType.BigInt).Value = drpCustomer.SelectedValue
cmd.Parameters.Add("@SODate", SqlDbType.DateTime).Value = DateTime.Parse(txtSOdate.Text)
cmd.Parameters.Add("@OrderStatus", SqlDbType.VarChar).Value = drpOrderStatus.Text
cmd.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = txtCreatedBy.Text
cmd.Parameters.Add("@DateCreated", SqlDbType.DateTime).Value = DateTime.Parse(txtDateCreated.Text)
cmd.Parameters.Add("@ApproveBy", SqlDbType.VarChar).Value = txtApprovedBy.Text
cmd.Parameters.Add("@DateApproved", SqlDbType.DateTime).Value = DateTime.Parse(txtDateApproved.Text)
For Each row As GridViewRow In Me.SalesGView.Rows
If (row.RowType = DataControlRowType.DataRow) Then
Me.SaveInfo(row)
Label9.Text = "Record inserted successfully"
End If
Next
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Sub
Private Sub SaveInfo(ByVal row As GridViewRow)
Dim con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("SY_InventoryConnectionString").ConnectionString)
Dim cmd As SqlCommand = New SqlCommand("InsertSalesOrder", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@QTY", row.Cells(3).Text)
cmd.Parameters.AddWithValue("@USP", row.Cells(4).Text)
cmd.Parameters.AddWithValue("@DiscountPercent", row.Cells(5).Text)
cmd.Parameters.AddWithValue("@DiscountAmt", row.Cells(6).Text)
cmd.Parameters.AddWithValue("@Amount", row.Cells(7).Text)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Sub
Do you think i should create two stored procedure for this?