How to combine Insert and Updates?

[lithium1976]

Hello I am trying to merge my insert and update Sprocs into one single insert/update Sproc -

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 -

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()

   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.

[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.

[lithium1976]

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.

[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.

[lithium1976]

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.

[jmcilhinney]

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.

[lithium1976]

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.

  '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 -

 BookingsItemsDataAdapter.Update(ds, "tblbookingsItems")

[lithium1976]

any ideas?