Procedure ''xyz'' expects parameter ''@abc'', which was not supplied
-
The actual error I'm getting is this:
Procedure 'usp_Update_Order_Header' expects parameter '@OrderID', which was not supplied
This is in ASP.NET in VB (.NET Framework v2), connecting to SQL Server 2000 SP4. I've definitely spelled the parameter correctly and if I run the procedure in Query Analyzer as the same user as the connection string, it works fine!
Here is the procedure; quite straightforward:
CREATE PROCEDURE dbo.usp_Update_Order_Header
@OrderID INT,
@CustomerReference VARCHAR(20),
@Description VARCHAR(100),
@RequiredBy DATETIME,
@ValidUntil DATETIME
AS
UPDATE [Order]
SET CustomerReference = @CustomerReference, [Description] = @Description,
RequiredBy = @RequiredBy, ValidUntil = @ValidUntil
WHERE OrderID = @OrderID
GO
And here is the VB code that executes it:
Protected Sub UpdateButton_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles UpdateButton.Click
Dim conn As OleDbConnection = New OleDbConnection("Provider=SQLOLEDB;Data Source=SHED\FINANCE;initial catalog=SOP;User ID=SOP;Password=<edited>")
Dim cmd As OleDbCommand = New OleDbCommand("usp_Update_Order_Header", conn)
Dim pOrderID As OleDbParameter = cmd.Parameters.Add("@OrderID", OleDbType.Integer)
pOrderID.Direction = Data.ParameterDirection.Input
Dim pCustomerReference As OleDbParameter = cmd.Parameters.Add("@CustomerReference", OleDbType.VarChar, 20)
pCustomerReference.Direction = Data.ParameterDirection.Input
Dim pDescription As OleDbParameter = cmd.Parameters.Add("@Description", OleDbType.VarChar, 100)
pDescription.Direction = Data.ParameterDirection.Input
Dim pRequiredBy As OleDbParameter = cmd.Parameters.Add("@RequiredBy", OleDbType.DBDate)
pRequiredBy.Direction = Data.ParameterDirection.Input
Dim pValidUntil As OleDbParameter = cmd.Parameters.Add("@ValidUntil", OleDbType.DBDate)
pValidUntil.Direction = Data.ParameterDirection.Input
pOrderID.Value = 1000030 'CInt(txtOrderID.Text)
pCustomerReference.Value = txtCustomerReference.Text
pDescription.Value = txtDescription.Text
pRequiredBy.Value = calRequiredBy.SelectedDate
pValidUntil.Value = calValidUntil.SelectedDate
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
cmd =
Nothing
conn =
Nothing
End Sub
Any ideas? I'm tearing my hair out here! No amount of Googling has helped (just obvious solutions). I've got countless other procedures across the app that work fine, including one almost identical on that same page, that retrieves the information for the Order (with the same parameters, except that @OrderID is input and the rest are output); this works fine!
I have also tried passing an explicit value through instead of using the control (as can be seen above), and the same thing happens. If I comment out the running of the procedure and just Response.Write the contents of the control, it is exactly as expected.
-
This is from an access background but hey, it's worth a shot :
try calling the sp with dbo.usp_Update_Order_Header.
Make sure SOP as execute permission also.
-
Didn't work - same error. Thanks anyway.
SOP has db_owner rights but I've tried giving it explicit EXEC permissions anyway, just in case, and it throws the same error.
-
Here's a complete shot in the dark but it might give you some light
.This is a computer generated code to execute your SP. This works 100% of the time for me. So let's hope it returns the favor to you :
Private Function exec_usp_Update_Order_Header(ByVal OrderID AS Int32, ByVal CustomerReference AS String, ByVal Description AS String, ByVal RequiredBy AS Date, ByVal ValidUntil AS Date) AS Int32Dim MyCmd AS New SqlClient.SqlCommand("dbo.usp_Update_Order_Header" MyCn)
MyCmd.CommandType = CommandType.StoredProcedure
Dim MyParam AS SqlClient.SqlParameter
MyParam = MyCmd.Parameters.ADD("@OrderID" OrderID)
MyParam.Size = 4
MyParam.Direction = ParameterDirection.Input
MyParam.SqlDbType = SqlDbType.INT
MyParam = MyCmd.Parameters.ADD("@CustomerReference" CustomerReference)
MyParam.Size = 20
MyParam.Direction = ParameterDirection.Input
MyParam.SqlDbType = SqlDbType.VARCHAR
MyParam = MyCmd.Parameters.ADD("@Description" Description)
MyParam.Size = 100
MyParam.Direction = ParameterDirection.Input
MyParam.SqlDbType = SqlDbType.VARCHAR
MyParam = MyCmd.Parameters.ADD("@RequiredBy" RequiredBy)
MyParam.Size = 8
MyParam.Direction = ParameterDirection.Input
MyParam.SqlDbType = SqlDbType.Datetime
MyParam = MyCmd.Parameters.ADD("@ValidUntil" ValidUntil)
MyParam.Size = 8
MyParam.Direction = ParameterDirection.Input
MyParam.SqlDbType = SqlDbType.Datetime
MyParam = MyCmd.Parameters.ADD("@Return" "0" )
MyParam.Size = 4
MyParam.Direction = ParameterDirection.ReturnValue
MyParam.SqlDbType = SqlDbType.INT
Dim RowsAffected AS Int32 = 0
Dim ReturnValue AS Int32 = 0
Try
MyCn.OPEN()
RowsAffected = MyCmd.ExecuteNonQuery()
MyCn.CLOSE()
ReturnValue = CType(MyCmd.Parameters("@Return".Value, Int32)
Catch MyEx AS Exception
MsgBox(MyEx.Message)
Finally
IF MyCn.State <> ConnectionState.Closed THEN
MyCn.CLOSE()
END IF
Dim Disp AS IDisposable
IF TypeOf MyCmd IS IDisposable THEN
Disp = MyCmd
Disp.Dispose()
END IF
END Try
RETURN ReturnValue
END Function
Any suggestion welcomed if you see a better way to call the commands!
-
did u notice this, may be this will help.. you r missing the ' ' for all varchars
ex: SET CustomerReference = '@CustomerReference'
Thanks,
-
I'm not following you. If you are talking about the SP code, it works fine as it is. The server takes care of adding the ' ' (assuming it needs them later on).
-
oh ok..! noted that..i dont see any problem why the code fails..just thought it could be with the SP's ''...thanks for letting me know
-
Thanx for the help... I just hope my code works for him
. -
It worked - thanks!
I had to modify it slightly to fit the syntax it was looking for but virtually the same:
Dim MyCn As SqlClient.SqlConnection = New SqlClient.SqlConnection("Data Source=SHED\FINANCE;initial catalog=SOP;User ID=SOP;Password=<edited>")
Dim MyCmd As SqlClient.SqlCommand = New SqlClient.SqlCommand("usp_Update_Order_Header", MyCn)
MyCmd.CommandType = CommandType.StoredProcedure
Dim MyParam As SqlClient.SqlParameter
MyParam = MyCmd.Parameters.Add("@OrderID", SqlDbType.Int)
MyParam.Size = 4
MyParam.Direction = ParameterDirection.Input
MyParam.Value = CInt(txtOrderID.Text)
MyParam = MyCmd.Parameters.Add("@CustomerReference", SqlDbType.VarChar)
MyParam.Size = 20
MyParam.Direction = ParameterDirection.Input
MyParam.Value = txtCustomerReference.Text
MyParam = MyCmd.Parameters.Add("@Description", SqlDbType.VarChar)
MyParam.Size = 100
MyParam.Direction = ParameterDirection.Input
MyParam.Value = txtDescription.Text
MyParam = MyCmd.Parameters.Add("@RequiredBy", SqlDbType.DateTime)
MyParam.Size = 8
MyParam.Direction = ParameterDirection.Input
MyParam.Value = calRequiredBy.SelectedDate
MyParam = MyCmd.Parameters.Add("@ValidUntil", SqlDbType.DateTime)
MyParam.Size = 8
MyParam.Direction = ParameterDirection.Input
MyParam.Value = calValidUntil.SelectedDate
MyParam = MyCmd.Parameters.Add("@Return", SqlDbType.Int)
MyParam.Size = 4
MyParam.Direction = ParameterDirection.ReturnValue
MyParam.Value = 0
Dim RowsAffected As Int32 = 0
Dim ReturnValue As Int32 = 0
Try
MyCn.Open()
RowsAffected = MyCmd.ExecuteNonQuery
MyCn.Close()
ReturnValue =
CType(MyCmd.Parameters("@Return").Value, Int32)
Catch MyEx As Exception
Response.Write(MyEx.Message)
Finally
If MyCn.State <> ConnectionState.Closed Then
MyCn.Close()
End If
Dim Disp As IDisposable
If TypeOf MyCmd Is IDisposable Then
Disp = MyCmd
Disp.Dispose()
End If
End Try
Definitely time to buy you a virtual pint - either British, which is bigger and more expensive for me, but warmer
or American which, whilst smaller, is much better! 
-
Glad you got this working... needless to say that I have this code made to fit my setup which includes a global connection string which is opened for the least amount of time possible.Now we can figure out the different between the 2 codes.
Can you fire out profiler and execute both calls. That'll give you the command send to the server by both statements and that should tell you (and all of us) why the first one is failing.
-
BTW the reason I use A function to run this is that I put all of this code in a single class which is also computer-generated. That way any application can call any procedure and not worry about the details. Just send the parameters and everything else is taken care of
. The only step missing is a centralized error handling to take care of common exceptions... but that's another project 
. -
>> Can you fire out profiler and execute both calls. That'll give you the command send to the server by both statements and that should tell you (and all of us) why the first one is failing. <<
Interesting. My original code just runs:
exec dbo.usp_Update_Order_Header
Your code runs:
exec usp_Update_Order_Header @OrderID = 1000036, @CustomerReference = '', @Description = 'Estate Management Module', @RequiredBy = 'Jan 1 2001 12:00:00:000AM', @ValidUntil = 'Dec 14 2006 12:00:00:000AM'
That's fairly obvious why it's failing then, but I've no idea why it's not passing the parameters! The odd thing is that I have a few other pieces of code virtually identical, just different procedures, and they all work absolutely fine.
Just for sh*ts and giggles, here's another piece of code which works fine (and the proc does have the "sp_" prefix in this case):
Dim conn As OleDbConnection = New OleDbConnection("Provider=SQLOLEDB;Data Source=SHED\FINANCE;initial catalog=SOP;User ID=SOP;Password=<edited>")
Dim cmd As OleDbCommand = New OleDbCommand("sp_Add_Line", conn)
cmd.CommandType = Data.CommandType.StoredProcedure
Dim pOrderID As OleDbParameter = cmd.Parameters.Add("@OrderID", OleDbType.Integer)
pOrderID.Direction = Data.ParameterDirection.Input
Dim pComponentID As OleDbParameter = cmd.Parameters.Add("@ComponentID", OleDbType.Integer)
pComponentID.Direction = Data.ParameterDirection.Input
Dim pLineID As OleDbParameter = cmd.Parameters.Add("@LineID", OleDbType.Integer)
pLineID.Direction = Data.ParameterDirection.Input
pOrderID.Value = CInt(OrderID.Text)
pComponentID.Value = AvailableLinesGrid.SelectedDataKey.Item("ComponentID")
pLineID.Value = AvailableLinesGrid.SelectedDataKey.Item("LineID")
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
cmd = Nothing
conn =
Nothing
Figure that one out!!!
I like the function idea. Most of the SPs are very specific to that part of the app, and it's only small, but I'll certainly bear that in mind for any more generic procs.
-
BTW I'm off in an hour and not back at work till next Wednesday, so if there are any more developments, I'm not hiding - I'll pick it up then!
Something tells me this is going to be something ridiculously obvious and simple. So much so that I'll end up cutting off my leg just so I can kick myself in the head.
Thanks again for the help.
-
I just don't see it... and I don't have .net installed to test so I'll leave that one in your hands unless someone else wants to try it out!!
-
In your post of the code that finally worked (using the SQL connection instead of an OLDEB connection, and SQL datat types instead as well), you said that the code was 'computer generated'.
If it's not off topic, I'd like to know what wonderful little gem you have that can generate that code, obviously by reading the stored proc...
???
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply