locked
why is this stored procedure not working? RRS feed

  • Question

  • I'm sure it'e something simple that i'm missing:)

     

    stored procedure

    ALTER PROCEDURE UpdateItemQty1

    @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;

     

    works great in the query analyzer. 

    But, when I try to call it in vb

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

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

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

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

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

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

    itemqtyCommand.CommandType = CommandType.StoredProcedure

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

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

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

    connection.Open()

    itemqtyCommand.ExecuteNonQuery()

    connection.Close()

     

    Me.Validate()

    InvoicesBindingSource.EndEdit()

    InvoiceDetailsBindingSource.EndEdit()

    InventoryBindingSource.EndEdit()

    InvoicesTableAdapter.Update(RCSDataSet.Invoices)

    InvoiceDetailsTableAdapter.Update(RCSDataSet.InvoiceDetails)

    InventoryTableAdapter.Update(RCSDataSet.Inventory)

    RCSDataSet.AcceptChanges()

    InventoryTableAdapter.Fill(RCSDataSet.Inventory)

    End Sub

    It doesn't show any changes to Inventory "instock"

    Sunday, January 7, 2007 6:11 PM

Answers

  • just refill the dataset with the data and then bind it to the datagridview :-) not sure of the exact code to be honest, since you are using the wizard approach.
    Sunday, January 7, 2007 6:46 PM
    Moderator

All replies

  • are you sure there are no changes being made? how do you know? how have you checked? Can you explain how you checked to see if there have been any changes made?

    in addition, if you included the database in your solution project, then be sure that its "copy to output" property has been set to "do not copy", then initially copy the database to the project output directory, this will prevent it from overwritting everytime you run your project within the IDE. you should also not call acceptchanges as it can cause a bit of a confusion and will also not show any changes to be made to the database when calling the update command after calling acceptchanges

    Sunday, January 7, 2007 6:14 PM
    Moderator
  • Thank God it's ahmedilyas!

    I am selecting rows from "inventorydatagridview" and adding them to "invoicedetails datatable" with the following

     

    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

     

    Now in the "invoicedetailsdatagrid" I have a coloumn "qty" that I want the user to be able to input the qty

    when the user clicks the save button

    1. the invoice details items should be saved to "invoice details" (this works)

    2. the inventory datagridview should be refreshed to show the updated quantity from the executed stored procedure. ( I'm having trouble with this part)

     

     

    Sunday, January 7, 2007 6:23 PM
  • a little bit confused - have you tried to rebind the data (updated data that the SPROC does when executed) to the datagridview?
    Sunday, January 7, 2007 6:36 PM
    Moderator
  • How would I do that?
    Sunday, January 7, 2007 6:42 PM
  • just refill the dataset with the data and then bind it to the datagridview :-) not sure of the exact code to be honest, since you are using the wizard approach.
    Sunday, January 7, 2007 6:46 PM
    Moderator
  • Ok, get this

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

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

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

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

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

    itemqtyCommand.CommandType = CommandType.StoredProcedure

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

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

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

    connection.Open()

    itemqtyCommand.ExecuteNonQuery()

    connection.Close()

    For some reason this updates the "instock" value by subtracting the "customerID" value from the "instock" value

     

    Sunday, January 7, 2007 7:09 PM
  • ok, very wierd.

    if i change

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

    to

    dim qty as integer = invoicedetailsdatagridview.item(2, invoicedetailsdatagridview.currentrow.index).value

    It works.

    But It only updates the "instock" of the first Item added to Invoicedetails. 

    Say I add

    Item1,Item2 and Item3

    It is only changing the "instock" value for item1 when I hit save. 

    Any idea how to get it to change all the items "instock" value?

    Sunday, January 7, 2007 9:54 PM
  • Cammyr,

    How is your stored procedure now? I'm afraid the God's ahmedilyas is busy with some other questions but I'm still care about your stored procedure. It seems a little complicated and what about now? Hope it works fine :-)

    Waiting for your reply.

    Monday, January 15, 2007 9:45 AM
  • Actually I decided to change it to a trigger and all is fine.
    Monday, January 15, 2007 8:10 PM