Link to home
Start Free TrialLog in
Avatar of grwallace
grwallace

asked on

Ado.net VB stored procedure update issue

I am filling a table from an SP with the following columns:-

SELECT     Sotrn.SotrnId,  InvoiceName + ' (' + crctl.CrcAbrev + ')' AS Customer
, Sotrn.No_Units as Units, Sotrn.Unit_Price AS Price, Slrep.RepName, Hauliers.Haul_Desc AS Haulier, [Depot Name] as [Dispatch_From],
                  sohed.KeyDate as Dispatch, Potrn.PotrnId, Potrn.Ord_Units
FROM         Sotrn INNER JOIN Etc Etc

This works fine.

I then created an Sqlcommand to update this as follows:-


        dim cmdUpdateOrders as New SqlCommand
        Try
            cmdUpdateOrders.Parameters.AddRange(New SqlParameter() _
              {New SqlParameter("@Units", SqlDbType.SmallMoney), _
               New SqlParameter("@Price", SqlDbType.SmallMoney), _
               New SqlParameter("@SotrnId", SqlDbType.Int), _
              New SqlParameter("@PotrnId", SqlDbType.Int)})
            cmdUpdateOrders.CommandType = CommandType.StoredProcedure
            cmdUpdateOrders.Connection = CON
            cmdUpdateOrders.CommandText = "CtsWtUpdateSOrds"
        Catch ex As Exception
            MsgBox("Error " & ex.Message & " in Setup_StockOrder_Query - CmdUpdateOrders ")
        End Try


The SP for the update is as follows:-

ALTER PROCEDURE [dbo].[CtsWtUpdateSOrds] @Units SmallMoney, @Price SmallMoney, @SotrnId Integer, @PotrnId Integer
AS
UPDATE Sotrn SET No_Units = @Units, Unit_Price = @Price WHERE SotrnId = @Sotrnid
UPDATE Potrn SET Ord_Units = @Units  WHERE PotrnId = @Potrnid

but when I try the following code:-

 Try
            AD.UpdateCommand = cmdUpdateOrders
            AD.Update(DS, "StockOrderAlloc")
        Catch ex As Exception
            MsgBox("Error " & ex.Message & " in cmdUpdateChanges_Click")
        End Try

I get :-

Error Procedure or Function CtswtUpdateSOrds expects parameter '@Units', which was not supplied.

I think I am nearly there.

Any ideas anyone?
Avatar of crshekharam
crshekharam
Flag of India image

You did not pass values to parameters Units, price, etc.  Pass value to them
Avatar of grwallace
grwallace

ASKER

Does that mean that to use an SP for this I need to write an itterative update something like:-

dim DT as DataTable = DS.tables("StockOrderAlloc")
For RowNo as Short = 0 to dt.rows.count = 1
      dim DRow as DataRow = dt.rows(drow)
      cmdUpdateOrders.parameters("@Units").value = drow("Units")
     etc
     AD.UpdateCommand = cmdUpdateOrders
     AD.Update(DS, "StockOrderAlloc")
next

???

or is there any way I can get it to update the entire table in one go as a "Normal" updatecommand would do???
ASKER CERTIFIED SOLUTION
Avatar of Pratima
Pratima
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial