Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • Last Modified:

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?
0
grwallace
Asked:
grwallace
1 Solution
 
crshekharamCommented:
You did not pass values to parameters Units, price, etc.  Pass value to them
0
 
grwallaceAuthor Commented:
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???
0
 
Pratima PharandeCommented:
Yiou have not provided the values for SQL parameters

For Example

 Dim connectionstring as string
    connectionstring = "server=MyServer;" & _
        "database=MyDatabaseDB;uid=superuser;password=superpwd"
    Dim sqlparams(3) as SqlClient.SqlParameter
    sqlparams(0) = new SqlClient.SqlParameter("@inputone", SqlDbType.Int)
    sqlparams(0).Value = Ctype(ddlInputOne.SelectedItem.Value,Int32)
    sqlparams(1) = new SqlClient.SqlParameter("@inputtwo",SqlDbType.TinyInt)
    sqlparams(1).Value = Ctype(ddlInputtwo.SelectedItem.Value,Int16)
    sqlparams(2) = new SqlClient.SqlParameter("@inputthree",SqlDbType.SmallInt)
    sqlparams(2).Value = Ctype(ddlInputthree.SelectedItem.Value,Int16)


refer
http://www.codeproject.com/Articles/8180/Using-SQLParameters-with-VB-NET-C
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now