January 31, 2008 at 10:41 am
I have found that in order to get an Output Parameter from a call to a Stored Procedure I have to do 2 things that do not appear in Kathi Kellenbergers example ('Using Parameters with Stored Procedures')
FIrstly I have to set the Value of the Output Parameter to something other wise I get a 'parameter is not supplied' error.
Secondly I must NOT specify the length of the Ouput parameter otherwise it just returns the value I set. E.g.
Dim myAnswer As New SqlParameter("@Answer", SqlDbType.VarChar, 30)
does not work but
Dim myAnswer As New SqlParameter("@Answer", SqlDbType.VarChar)
does!
From my point of view these are horrendous gotchas that wasted hours but being a relative amateur I guess it means I have done something wrong somewhere else???
January 31, 2008 at 11:06 am
If you look at the overloaded versions of SQLParameter, the longest one allows you to specify nullability of the parameter.
looks something like
...
Dim p As New System.Data.SqlClient.SqlParameter("joe", Data.SqlDbType.Char, 30, Data.ParameterDirection.Input, True, , , , , "my value")
...
Also - are you SURE you're using SQLParameter and not just Parameter? The 3-value overload for Parameter specifies default value as part # 3, whereas SQLParameter, that would be length.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 1, 2008 at 2:22 am
What has nullability to do with this? I am not expecting null and the stored proc is not setting the output parameter to null. And I AM using SQLParameter and not just Parameter. Intellisense tells me the 3rd item is size.
February 1, 2008 at 9:30 am
I'm think of setting the output parameter to be nullable.... that way you won't have to supply anything to it on the way IN to the stored proc.
As to your other issue - I'm stumped:) By all rights - that SHOULD be size, so I don't know why it's not...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply