How to combine Insert and Updates?
Hello I am trying to merge my insert and update Sprocs into one single insert/update Sproc -
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
I was previously using two buttons in vb.net, one for inserting, one for updates -
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()
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")
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.
Re: How to combine Insert and Updates?
First up, don't ask the same question in multiple threads. If you wanted to shift the discussion from the Database Development forum to the VB.NET forum then you should have asked a moderator to move the thread.
As to the question, why would you need two Buttons anyway? You just put all your data into a DataTable and call Update on the DataAdapter. That will execute the adapter's InsertCommand on all the DataRows with a RowState of Added and the UpdateCommand on all the DataRpws with a RowState of Modified. One method call, ergo one Button, to save all the data.
Re: How to combine Insert and Updates?
Quote:
Originally Posted by jmcilhinney
First up, don't ask the same question in multiple threads. If you wanted to shift the discussion from the Database Development forum to the VB.NET forum then you should have asked a moderator to move the thread.
As to the question, why would you need two Buttons anyway? You just put all your data into a DataTable and call Update on the DataAdapter. That will execute the adapter's InsertCommand on all the DataRows with a RowState of Added and the UpdateCommand on all the DataRpws with a RowState of Modified. One method call, ergo one Button, to save all the data.
Sorry about the asking same questions part...next time I will ask a moderator.
So Ive put both the quoted Insert and Update commands in a single button. but the insert still trying to insert when I am just trying to update the @quantityRequested. The update IS working, but the insert is STILL trying to insert as well, which throws up the error "Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index". For @bookingsID parameter.
Re: How to combine Insert and Updates?
Why are you adding parameter values explicitly for your insert? The insert should be EXACTLY like the update. The data should already be in the DataTable. There's no calling ExecuteNonQuery. The Update call saves ALL the data.
Re: How to combine Insert and Updates?
Quote:
Originally Posted by jmcilhinney
Why are you adding parameter values explicitly for your insert? The insert should be EXACTLY like the update. The data should already be in the DataTable. There's no calling ExecuteNonQuery. The Update call saves ALL the data.
If I use just the update parameter values without the insert values I am unable to perform any insert.
Re: How to combine Insert and Updates?
Of course you can insert, as long as you've actually added some new rows to your DataTable, just like you can update as long as you've edited some existing rows in your DataTable. I suggest that you follow the Data Access link in my signature and pay special attention to the fourth code snippet in post #1 and the code snippet in post #3.
Re: How to combine Insert and Updates?
Im still having trouble with the single button. I cannot get the insert to work when selecting a row from dgvStock eg "stockID 1" to add it to tblBookingsItems. I can type in the stockId "1" into tblbookings stockID field, but I want to be able to select the stockID from tblStock itself. I thought I had it sorted the other day, but Ive just come back to it and hey-presto, it aint working for me.
Code:
'Preparing tblBookingsItems-tblBookings Relationship
BookingsItemsDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
BookingsItemsDataAdapter.Fill(ds, "tblbookingsItems")
datatable = ds.Tables("tblbookingsitems")
Dim relationsBookingsItems As New DataRelation("BookingsItems", _
ds.Tables("tblBookings").Columns("BookingsID"), _
ds.Tables("tblbookingsItems").Columns("BookingsID"))
ds.Relations.Add(relationsBookingsItems)
BookingsItemsBindingSource.DataSource = BookingsBindingSource
BookingsItemsBindingSource.DataMember = "BookingsItems"
dgvbookingsitems.DataSource = BookingsItemsBindingSource
dgvbookingsitems.Columns("BookingsItemsID").ReadOnly = True
dgvbookingsitems.SelectionMode = DataGridViewSelectionMode.FullRowSelect
'Preparing tblStock
'stockDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
stockDataAdapter.Fill(ds, "tblstock")
datatable = ds.Tables("tblStock")
stockBindingSource.DataSource = ds
stockBindingSource.DataMember = "tblstock"
dgvStock.DataSource = stockBindingSource
' dgvStock.SelectionMode = DataGridViewSelectionMode.FullRowSelect
Dim TESTinsert As New SqlCommand("INSERT INTO tblBookingsItems (stockID, bookingsID) VALUES(@stockID, @bookingsID)", conn)
TESTinsert.Parameters.Add("@stockID", SqlDbType.Int, 100, "stockID")
TESTinsert.Parameters.Add("@bookingsID", SqlDbType.Int, 100, "bookingsID")
TESTinsert.Parameters.Add("@quantityRequested", SqlDbType.Int, 100, "bookingsID")
BookingsItemsDataAdapter.InsertCommand = TESTinsert
Dim TESTupdate As New SqlCommand("UPDATE tblBookingsItems SET quantityRequested = @quantityRequested, stockID = @stockID, BookingsID = @bookingsID WHERE bookingsItemsID = @bookingsItemsID ", conn)
TESTupdate.Parameters.Add("@quantityRequested", SqlDbType.Int, 100, "quantityRequested")
TESTupdate.Parameters.Add("@bookingsItemsID", SqlDbType.Int, 100, "bookingsItemsID")
TESTupdate.Parameters.Add("@stockID", SqlDbType.Int, 100, "stockID")
TESTupdate.Parameters.Add("@bookingsID", SqlDbType.Int, 100, "bookingsID")
BookingsItemsDataAdapter.UpdateCommand = TESTupdate
Dim TESTdelete As New SqlCommand("DELETE FROM tblBookingsItems WHERE BookingsItemsID = @BookingsItemsID", conn)
TESTdelete.Parameters.Add("@BookingsItemsID", SqlDbType.Int, 100, "BookingsItemsID")
BookingsItemsDataAdapter.DeleteCommand = TESTdelete
Save the changes with -
Code:
BookingsItemsDataAdapter.Update(ds, "tblbookingsItems")
Re: How to combine Insert and Updates?
