Solved

Ado.net VB stored procedure update issue

Posted on 2012-12-27
3
211 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
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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 …
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

863 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now