Problem calling a stored procedure

Posted on 2007-10-09
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

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

    Expert Comment


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

    Author Comment

    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
    LVL 27

    Expert Comment

    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

    Sorry, it would be like this:

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

    LVL 21

    Expert Comment

    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

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


    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
    It’s quite interesting for me as I worked with Excel using 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 …
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    728 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

    17 Experts available now in Live!

    Get 1:1 Help Now