[Last Call] Learn how to a build a cloud-first strategyRegister Now


Problem calling a stored procedure

Posted on 2007-10-09
Medium Priority
Last Modified: 2011-04-14
I am trying to call an SP:-

ALTER PROCEDURE [dbo].[CreateSohed]
            @SalesOrder char(10), @OrdDate datetime, @CustRef nvarchar(20), @SlmasId Int,
            @Subsystem Smallint, @Provisional bit, @CustInsts nvarchar(300), @PackInsts nvarchar(300),
            @DeliveryDate datetime, @SlrepId Int, @SohReplantOrd bit, @Identity Int Out
AS INSERT INTO Dbo.Sohed ([Sales Order], OrdDate, Cust_Ref, SlmasId, Subsystem,
            Provisional, CustInsts, PackInsts, DeliveryDate, SlrepId, SohReplantOrd)

VALUES (@SalesOrder, @OrdDate, @CustRef, @SlmasId, @Subsystem,
            @Provisional, @CustInsts, @PackInsts, @DeliveryDate, @SlrepId, @SohReplantOrd)

using this code:-

Dim Command1 As SqlCommand = New SqlCommand("CreateSohed", CNPost)
            Command1.CommandType = CommandType.StoredProcedure
            Command1.Transaction = sqlTran
            Command1.Parameters.Add("@SalesOrder", SqlDbType.Char, 10, ThisOrdNo)
            Command1.Parameters.Add("@OrdDate", SqlDbType.DateTime, 8, OpData.OrdDate)
            Command1.Parameters.Add("@CustRef", SqlDbType.Char, 20, OpData.Reference)
            Command1.Parameters.Add("@SlmasId", SqlDbType.Int, OpData.AcntMasId)
            Command1.Parameters.Add("@Subsystem", SqlDbType.TinyInt, 1, OpData.SubSystem)
            Command1.Parameters.Add("@Provisional", SqlDbType.Bit, 1, OpData.Provisional)
            Command1.Parameters.Add("@CustInsts", SqlDbType.NVarChar, 1, OpData.PublicComment)
            Command1.Parameters.Add("@PackInsts", SqlDbType.NVarChar, 1, OpData.PrivateComment)
            Command1.Parameters.Add("@DeliveryDate", SqlDbType.DateTime, 8, OpData.DelDate)
            Command1.Parameters.Add("@SlrepId", SqlDbType.SmallInt, OpData.SlrepId)
            Command1.Parameters.Add("@ReplantOrd", SqlDbType.Bit, 1, OpData.Replant)
            Dim myParm As SqlParameter = Command1.Parameters.Add("@Identity", SqlDbType.Int, 0, "SohedIdID")
            myParm.Direction = ParameterDirection.Output
            '   Command1.CommandText = "CreateSohed"

but all that i get back is the message :-
Procedure of Function 'CreateSohead' expects parameter '@SalesOrder', which was not supplied.

Am I doing something silly?
Question by:grwallace
LVL 16

Expert Comment

ID: 20041142
Command1.Parameters.Add("@SalesOrder", SqlDbType.Char, 10, OpDate.ThisOrdNo)
LVL 16

Expert Comment

ID: 20041147

Command1.Parameters.Add("@SalesOrder", SqlDbType.Char, 10, OpData.ThisOrdNo)

Author Comment

ID: 20041230
Thanks for this, but it is not the problem OpData is a structure passed from a calling form whereas ThisOrdNo is generated in this form. Because of this there is no prefix on thisordno
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 27

Expert Comment

ID: 20041333
Try using the AddWithValue method and see if that helps, like this:

    Command1.Parameters.AddWithValue("@SalesOrder", SqlDbType.Char, 10, OpDate.ThisOrdNo)

LVL 27

Accepted Solution

VBRocks earned 2000 total points
ID: 20041341
Sorry, it would be like this:

    Command1.Parameters.AddWithValue("@SalesOrder", OpDate.ThisOrdNo)

LVL 21

Expert Comment

ID: 20041564
Ditto VBRocks as long as you're using 2005.  If you look at the help on the Add method you are using, you'll notice the 4th parameter is not "value" but is "sourceColumn"
LVL 27

Expert Comment

ID: 20041752
You're right.  That's the first thing I caught.


Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

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 …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

829 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