Solved

Ado.net VB stored procedure update issue

Posted on 2012-12-27
3
223 Views
Last Modified: 2012-12-27
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
Comment
Question by:grwallace
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 3

Expert Comment

by:crshekharam
ID: 38723299
You did not pass values to parameters Units, price, etc.  Pass value to them
0
 

Author Comment

by:grwallace
ID: 38723321
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
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 500 total points
ID: 38723358
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question