Hello I am trying to merge my insert and update Sprocs into one single insert/update Sproc -
I was previously using two buttons in vb.net, one for inserting, one for updates -Code:ALTER PROCEDURE [dbo].[TEST] ( @bookingsItemsID as int, @quantityRequested as int, @stockID as int, @bookingsID as int ) AS BEGIN If @bookingsItemsId < 0 BEGIN INSERT INTO tblBookingsItems (stockID, bookingsID) VALUES(@stockID, @bookingsID) END ELSE BEGIN If @bookingsItemsId > 0 DECLARE @CHANGE int SELECT @CHANGE = quantityRequested - @quantityRequested FROM tblBookingsItems WHERE bookingsItemsID = @bookingsItemsID UPDATE tblBookingsItems SET quantityRequested = @quantityRequested WHERE bookingsItemsID = @bookingsItemsID UPDATE tblStock SET numberInStock = numberInStock + @CHANGE WHERE StockID = @StockID end
Code:Dim insertCommand As New SqlCommand("test", conn) insertCommand.CommandType = CommandType.StoredProcedure BookingsDataAdapter.InsertCommand = insertCommand insertCommand.Parameters.AddWithValue("@bookingsID", Me.dgvBookings.SelectedRows(0).Cells(0).Value.ToString) insertCommand.Parameters.AddWithValue("@stockID", Me.dgvStock.SelectedRows(0).Cells(0).Value.ToString) insertCommand.ExecuteNonQuery()How should I combine the two commands into one single insert/update command button? It works fine with two buttons, one for each but I would love to try using just one button for both commands. The update will update the fields QuantityRequested and NumberInStock. The insert will insert BookingsID and StockID into TblBookingsItems.Code:Dim updateCommand As New SqlCommand("test", conn) updateCommand.CommandType = CommandType.StoredProcedure BookingsItemsDataAdapter.UpdateCommand = updateCommand updateCommand.Parameters.Add("@bookingsItemsID", SqlDbType.Int, 100, "bookingsItemsID") updateCommand.Parameters.Add("@quantityRequested", SqlDbType.Int, 100, "quantityRequested") updateCommand.Parameters.Add("@stockID", SqlDbType.Int, 100, "stockid") updateCommand.Parameters.Add("@bookingsID", SqlDbType.Int, 100, "BookingsID") BookingsItemsDataAdapter.Update(ds, "tblbookingsItems")




Reply With Quote