execute stored procedure on only selected rows

execute stored procedure on only selected rows

• Aing_d

  Hi,   Forget the new procedure.   The letter “x” is were you have put the column index of the column you want.

   

   

   

   

   

   

   

  Dim ItemNo As Integer = inventorydatagridview.item(x, inventorydatagridview.currentrow.index).value

  Dim Qty As Integer = Invoicedetailsdatagridview.item(x, Invoicedetailsdatagridview.currentrow.index).value

   

  Dim connection As New SqlConnection(My.Settings.RCSConnectionString)

  connection.Open()

  Dim itemqtyCommand As SqlCommand = New SqlCommand( "UpdateItemQty", connection)

  itemqtyCommand.CommandType = CommandType.StoredProcedure

  itemqtyCommand.Parameters.Add(New SqlParameter("@itemno", SqlDbType.Int)).Value = ItemNo

  itemqtyCommand.Parameters.Add(New SqlParameter("@qty", SqlDbType.Int)).Value = Qty

   

  ‘This will fill the Inventory Dataset displaying the updated data

  InvoicesTableAdapter.Fill(RCSDataSet.Inventory)

  
  

• Vinodonly

  Hi,

  This is other store procedure.

  CREATE PROCEDURE UpdateItemQty2

  @ItemNo INT,

  @InvoiceID INT

  AS

  BEGIN

  DECLARE @SoldQty INT;

  DECLARE @InStock INT;

  -- Here in the field units you changed if is units or qty

  SET @SoldQty=(SELECT Units FROM InvoiceDetails WHERE InvoiceID=@InvoiceID AND ItemNumber=@ItemNo);

  IF (@SoldQty IS NOT NULL)

  SET @InStock=(SELECT InStock FROM Inventory WHERE ItemNo=@ItemNo);

  UPDATE Inventory SET InStock=@InStock-@SoldQty WHERE ItemNo=@ItemNo;

  END;
  

• Timo81

  Ok, we're close.

  But,

  Itemno value has to be from selected row on inventorydatagridview

  and

  qty value has to be from Invoicedetailsdatagridview

  
  

• VB Overlord

  ok if i execute this sp in the query analyzer

  ALTER PROCEDURE UpdateItemQty2

  @ItemNo INT,

  @invoiceid int,

  @qty int

  AS

  BEGIN

  declare @itemnumber int;

  DECLARE @InStock INT;

  set @instock=(select instock from inventory where itemno=@itemno and invoiceid=@invoiceid);

  IF (@Qty IS NOT NULL)

  SET @InStock=(SELECT InStock FROM Inventory WHERE ItemNo=@ItemNo);

  UPDATE Inventory SET InStock=@InStock-@Qty WHERE ItemNo=@ItemNo;

  SELECT InStock, ItemNo

  FROM Inventory

  END;

  I get

  Running [dbo].[UpdateItemQty2] ( @ItemNo = 1, @invoiceid = 2, @qty = 1 ).

  InStock ItemNo

  ----------- --------------------------------------------------

  20 1

  (1 row(s) affected)

  (1 row(s) returned)

  @RETURN_VALUE = 0

  Finished running [dbo].[UpdateItemQty2].

  which is exactly what i want it to do, but it doesn't happen in my app

  Private Sub InvoicesBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles InvoicesBindingNavigatorSaveItem.Click

  Dim ItemNo As Integer = InventoryDataGridView.Item(ItemNo, InventoryDataGridView.CurrentRow.Index).Value

  Dim invoiceid As Integer = InvoiceDetailsDataGridView.Item(invoiceid, InvoiceDetailsDataGridView.CurrentRow.Index).Value

  Dim Qty As Integer = InvoiceDetailsDataGridView.Item(Qty, InvoiceDetailsDataGridView.CurrentRow.Index).Value

  Dim connection As New SqlConnection(My.Settings.RCSConnectionString)

  connection.Open()

  Dim itemqtyCommand As SqlCommand = New SqlCommand("UpdateItemQty2", connection)

  itemqtyCommand.CommandType = CommandType.StoredProcedure

  itemqtyCommand.Parameters.Add(New SqlParameter("@itemno", SqlDbType.Int)).Value = ItemNo

  itemqtyCommand.Parameters.Add(New SqlParameter("@qty", SqlDbType.Int)).Value = qty

  itemqtyCommand.Parameters.Add(New SqlParameter("@invoiceid", SqlDbType.Int)).Value = invoiceid

  itemqtyCommand.ExecuteNonQuery()

  connection.Close()

  Me.Validate()

  InvoicesBindingSource.EndEdit()

  InvoiceDetailsBindingSource.EndEdit()

  InventoryBindingSource.EndEdit()

  InvoicesTableAdapter.Update(RCSDataSet.Invoices)

  InvoiceDetailsTableAdapter.Update(RCSDataSet.InvoiceDetails)

  InventoryTableAdapter.Update(RCSDataSet.Inventory)

  InventoryTableAdapter.Fill(RCSDataSet.Inventory)

  RCSDataSet.AcceptChanges()

  End Sub

  
  

• garyf

  Hi,

  The letter “x” is were you have put the column index of the column you want.

  Dim ItemNo As Integer = inventorydatagridview.item(x, inventorydatagridview.currentrow.index).value

  Dim Qty As Integer = Invoicedetailsdatagridview.item(x, Invoicedetailsdatagridview.currentrow.index).value

  
  

• Gary Thorne Jr

  Hi,

   

  You have some logical errors in your code.

   

  Private Sub InvoicesBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles InvoicesBindingNavigatorSaveItem.Click

  ‘In the yellow marks what it goes there is the index of the column (Example: 1, 2, 3 or 4), the number of the column you want. Not the variable you are just declaring.

  Dim ItemNo As Integer = InventoryDataGridView.Item(ItemNo, InventoryDataGridView.CurrentRow.Index).Value

  Dim invoiceid As Integer = InvoiceDetailsDataGridView.Item(invoiceid, InvoiceDetailsDataGridView.CurrentRow.Index).Value

  Dim Qty As Integer = InvoiceDetailsDataGridView.Item(Qty, InvoiceDetailsDataGridView.CurrentRow.Index).Value

  Dim connection As New SqlConnection(My.Settings.RCSConnectionString)

  connection.Open()

  Dim itemqtyCommand As SqlCommand = New SqlCommand("UpdateItemQty2", connection)

  itemqtyCommand.CommandType = CommandType.StoredProcedure

  itemqtyCommand.Parameters.Add(New SqlParameter("@itemno", SqlDbType.Int)).Value = ItemNo

  ‘In the blue mark you have to flip the parameters, the “invoiceid” have to go first of “qty”, because that’s how they are declare in the procedure (itemno, invoiceid, qty) and you have them in your code (itemno, qty, invoiceid).

  itemqtyCommand.Parameters.Add(New SqlParameter("@qty", SqlDbType.Int)).Value = qty

  itemqtyCommand.Parameters.Add(New SqlParameter("@invoiceid", SqlDbType.Int)).Value = invoiceid

   

  itemqtyCommand.ExecuteNonQuery()

  connection.Close()

   

  Me.Validate()

  InvoicesBindingSource.EndEdit()

  InvoiceDetailsBindingSource.EndEdit()

  InventoryBindingSource.EndEdit()

  InvoicesTableAdapter.Update(RCSDataSet.Invoices)

  InvoiceDetailsTableAdapter.Update(RCSDataSet.InvoiceDetails)

  InventoryTableAdapter.Update(RCSDataSet.Inventory)

  InventoryTableAdapter.Fill(RCSDataSet.Inventory)

  RCSDataSet.AcceptChanges()

  End Sub

   

  Good luck, I’m done here.

  
  

• Eugene Zakhareyev

  Hi,

   

  What is that you want to do Because in your thread it seems that you want to do something and in your response of my solution other.

   

  You have to provide all the details of your problem if you want some body to help you.

   

  That store procedure what it does is update the stock quantity of the item from the table inventory subtracting the quantity sold of the current item. You add in the where clause the field InvoiceID and I’m guessing that the table inventory doesn’t have that field and you add the variable @InvoiceID in the procedure and you didn’t declared either.

  
  

• Shyam Amrawat

  ok, with "updateitemqty2"

  getting

  Cannot insert the value NULL into column 'InStock', table 'C:\DOCUMENTS AND SETTINGS\S. ROSS\MY DOCUMENTS\VISUAL STUDIO 2005\PROJECTS\RCS\RCS.MDF.dbo.Inventory'; column does not allow nulls. UPDATE fails.

  The statement has been terminated.

  No rows affected.

  (0 row(s) returned)

  @RETURN_VALUE = -6

  Finished running [dbo].[UpdateItemQty2].

  any suggestions

  
  

• Jabber

  Ok, I understand creating the stored procedure part. When I do this, I'm having problems with the parameters.

  If I change the sp to

  CREATE PROCEDURE UpdateItemQty

  @ItemNo INT,

  @Qty INT

  AS

  BEGIN

  DECLARE @InStock INT;

  SET @InStock=(SELECT InStock FROM Inventory WHERE InvoiceID=@InvoiceID);

  UPDATE Inventory SET InStock=@InStock-@Qty WHERE InvoiceID=@InvoiceID;

  END;

  Then it gives me an error "function expects parameter "@InvoiceID" that was not supplied"

  
  

• BobTheBuild

  I'm adding datarows to "invoicedetails" table from "Inventory datagridview" with the following code.

  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

  For Each dgvRow As DataGridViewRow In Me.InventoryDataGridView.SelectedRows()

  Dim dt As DataTable = Me.RCSDataSet.Tables("invoicedetails")

  Dim dtRow As DataRow = dt.NewRow()

  dtRow("CustomerID") = dgvRow.Cells("CustomerID").Value

  dtRow("InvoiceID") = dgvRow.Cells("InvoiceID").Value

  dtRow("ItemNumber") = dgvRow.Cells("ItemNo").Value

  dtRow("ItemID") = dgvRow.Cells("ItemID").Value

  dtRow("Description") = dgvRow.Cells("ItemDescription").Value

  dtRow("Units") = dgvRow.Cells("Units").Value

  dtRow("Price") = dgvRow.Cells("Price").Value

  dt.Rows.Add(dtRow)

  Next

  end sub

  Now, when the user adds an amount in the qty column of "invoicedetailsdatagridview" and hits Save I want the save button to

  1. update invoicedetails table with the added rows. (that works)

  2. Update instock column of the Inventory datatable to show newinstock value of

  instock-qty

  I can execute the stored procedure in the query manager and it works. But when I try to call it with the following code the changes do not appear in the app

  Me.Validate()

  InvoicesBindingSource.EndEdit()

  InvoiceDetailsBindingSource.EndEdit()

  InventoryBindingSource.EndEdit()

  InvoicesTableAdapter.Update(RCSDataSet.Invoices)

  Dim itemid As Integer

  Dim instock As Integer

  InventoryTableAdapter.UpdateItemQty1(itemid, instock)

  InvoiceDetailsTableAdapter.Update(RCSDataSet.InvoiceDetails)

  InventoryTableAdapter.Update(RCSDataSet.Inventory)

  RCSDataSet.AcceptChanges()

  my stored procedure is

  ALTER PROCEDURE UpdateItemQty1

  @ItemNo INT,

  @Qty INT

  AS

  DECLARE @InStock INT;

  SET @InStock=(SELECT InStock FROM Inventory WHERE ItemNo=@ItemNo);

  UPDATE Inventory SET InStock=@InStock-@Qty WHERE ItemNo=@ItemNo;

  SELECT InStock, ItemNo

  FROM Inventory

  If I try to call the procedure with commands using

  Using connection As New SqlConnection(My.Settings.RCSConnectionString)

  connection.Open()

  Dim itemqtyCommand As SqlCommand = New SqlCommand( _

  "UpdateItemQty", connection)

  itemqtyCommand.CommandType = CommandType.StoredProcedure

  Dim itemno As SqlParameter = itemqtyCommand.Parameters.Add( _

  "@itemno", SqlDbType.Int)

  itemno.Value = "itemno"

  Dim qty As SqlParameter = itemqtyCommand.Parameters.Add( _

  "@qty", SqlDbType.Int)

  qty.Value = "qty"

  itemqtyCommand.ExecuteNonQuery()

  connection.Close()

  End Using

  I get this error

  Failed to convert parameter value from a String to a Int32.

  
  

• fReShJiViN

  I'm sorry, let me give you the details.:)

   

   

  
  

• MrSock

  Thank You!

  I'm not able to try it right now, I'm trying to install sqlexpress with advanced services and running into some trouble. As soon as I get it sorted out I'll try your solution.

  Thanks

  
  

• LukeSkywalker

  Hi,

  You can't join two tables in an update statement like that. You can create a store procedure that does what you want:

  CREATE PROCEDURE UpdateItemQty

  @ItemNo INT,

  @SoldQty INT

  AS

  BEGIN

  DECLARE @InStock INT;

  SET @InStock=(SELECT InStock FROM Inventory WHERE ItemNo=@ItemNo);

  UPDATE Inventory SET InStock=@InStock-@SoldQty WHERE ItemNo=@ItemNo;

  END;

  I’m guessing that you controls are bind with this data and this is going to be a problem for you to use the procedure if you want to see the data changes in the moment.

  If you are using a bindingnavigator I suggest that you execute the procedure in a command and fill again the tableadapter and refresh the bindingnavigator, if you are not using a bindingnavigator just fill again the tableadapter.

  There are a lot of ways of doing this but with just the description you have given this is only what I can provide.

  
  

• georgeob

  Hi,

  Try this:

  ‘Here you have to assign the values

  Dim ItemNo As Integer = 1

  Dim Qty As Integer = 1

   

  Dim connection As New SqlConnection(My.Settings.RCSConnectionString)

  connection.Open()

  Dim itemqtyCommand As SqlCommand = New SqlCommand( "UpdateItemQty", connection)

  itemqtyCommand.CommandType = CommandType.StoredProcedure

  itemqtyCommand.Parameters.Add(New SqlParameter("@itemno", SqlDbType.Int)).Value = ItemNo

  itemqtyCommand.Parameters.Add(New SqlParameter("@qty", SqlDbType.Int)).Value = Qty

   

  ‘This will fill the Inventory Dataset displaying the updated data

  InvoicesTableAdapter.Fill(RCSDataSet.Inventory)

   

  
  
• execute stored procedure on only selected rows