Ok, I posted here recently and received helpful replies which allowed me to work around a problem. The original question was posted here:
http://forums.asp.net/t/1112669.aspx
But because I'm learning both asp.net 2.0 AND vb 2005 I sort of want to get to the bottom of stuff. I've found out what was going wrong, but I don't understand it.
The problem related to retrieving an output parameter to a stored procedure. I was adding the parameter to the command object I was using as follows:
cmd.Parameters.Add(New SqlParameter("@.memberid", Data.SqlDbType.Int, 0, Data.ParameterDirection.Output))
but it wasn't working (ie I wasn't seeing the return value). A helpful poster's work around was to instead do this:
Dim pMemberId As New SqlParameter("@.memberid", SqlDbType.Int)
pMemberId.Direction = ParameterDirection.Output
cmd.Parameters.Add(pMemberId)
Having poked around some more, I've discovered that if I use the original code and then type:
?cmd.Parameters("@.memberid").Direction
I get the value:
Input {1}
This even happens if I explicitly use 2 instead of Data.ParameterDirection.Output
Can anyone explain why this is happening? What's the point of allowing me to pass a parameter into a constructor if it's just going to ignore it?
This just in...
Ah. Might have found the answer...not sure.
I looked up the constructors for SqlParameter, and found that there are 7. As I was typing the parameters it popped up with Direction for the 4th one, so I thought `that's easy - it's output` and selected it. I then closed the brackets and that was that. But I think what might be happening is that the compiler is saying "ah - 4 parameters? That means the fourth parameter is a string called `sourcecolumn`".
http://msdn2.microsoft.com/en-us/library/f38c3x2s(VS.80).aspx
At the point that you're entering the 4th parameter you can click on little arrows to cycle through the 3 remaining possible contructors. To be able to enter the direction requires me to enter a load more parameters.
Perhaps it's getting confused and instead of raising an error about the fact that the number 2 isn't a string it's instead turning my value of 2 into a 1? I've turned on Option Strict but it hasn't made any difference.
|||
Can you give some context on the commands execution? From looking at the previous post, it appears as though you are execiuting a NonQuery, is this still the case?
I ran into this problem a week or so ago, and the something along the following lines solved it:
SqlParameter p = cmd.Parameters.Add(New SqlParameter("@.memberid", Data.SqlDbType.Int, 0, Data.ParameterDirection.InputOutput))
...//other param code here
p.Value = 666
...//execute
myIntVar = Integer.Parse(p.value.ToString())
Not having a reference to the param seemed to ensure that everything was lost (though we are using the Microsoft DAAB, which does some param cleanup that explained things).
HTH
Aaron
|||
aaroncollett:
Can you give some context on the commands execution? From looking at the previous post, it appears as though you are execiuting a NonQuery, is this still the case?
I ran into this problem a week or so ago, and the something along the following lines solved it:
SqlParameter p = cmd.Parameters.Add(New SqlParameter("@.memberid", Data.SqlDbType.Int, 0, Data.ParameterDirection.InputOutput))
...//other param code here
p.Value = 666
...//execute
myIntVar = Integer.Parse(p.value.ToString())
Not having a reference to the param seemed to ensure that everything was lost (though we are using the Microsoft DAAB, which does some param cleanup that explained things).
HTH
Aaron
With respect, as explained above, the problem is nothing to do with the call to execute the stored procedure - it's about the way the SqlParameter object is created. Do you have your exact code to hand - it looks like you've sort-of copied mine there! I imagine that part of your
...//other param code here
code is to set the direction, because the way your code is looking makes me think you'll have the same problem as me. Immediately after you've called the constructor to SqlParameter, try:
?p.direction
and I bet you'll see 1 (input) instead of 2(output) ! You can call the SqlParameter constructor with just the name of the parameter and the type, and then set the direction with
p.Direction = ParameterDirection.Output
So yes, I'm not passing the correct parameters/types to the constructor, but the question then is - why isn't the compiler complaining about it?
Here is some suggested code for you to review. I'll comment inline:
1-- I reviewed your stored procedure from the previous post2-- and came up withthis code to execute it3int memberID = -1;45using( SqlConnection conn =new SqlConnection( connString ) )6{7using( SqlCommand cmd =new SqlCommand("[dbo].[ValidateMemberUsrPwd]", conn ) )8 {9 cmd.CommandType CommandType.StoredProcedure;1011 -- we are adding parametersin order of the12 -- stored procedure signature13 -- not required, but good practice14 cmd.Parameters.Add("@.username", SqlDbType.NVarChar, 16 ).Value = UserNameVariable;15 cmd.Parameters.Add("@.password", SqlDbType.NVarChar, 16 ).Value = PasswordVariable;1617 --this parameter, not onlyis it an output parameter18 -- but by the signature of the stored procedure itis19 -- a'required' parameter. So, it needs avalue.20 --if you want it to not be required, then give it adefault21 --in the signature. Something like22 -- @.memberidint = -1 OUTPUT23 SqlParameter pMemberID =new SqlParameter("@.memberid", SqlDbType.Int );24 pMemberID.ParameterDirection = ParameterDirection.Output;25 pMemberID.Value = DBNull.Value;26 cmd.Parameters.Add( pMemberID );2728 conn.Open();29 cmd.ExecuteNonQuery();3031 memberID = (int) pMamberID.Value;32 }33}|||
So yes, I'm not passing the correct parameters/types to the constructor, but the question then is - why isn't the compiler complaining about it?
Because there is only runtime checking for the code - to - database relationship. The difference in the ParameterDirection is that it is not required to call a stored procedure with the right direction - it will honor it where appropriate. So, if you use ParameterDirection.Input on a parameter that is marked in the signature of a stored procedure as OUTPUT, it will just not assign the local parameter to the returned value.
So yes, I'm not passing the correct parameters/types to the constructor, but the question then is - why isn't the compiler complaining about it?
Because there is only runtime checking for the code - to - database relationship. The difference in the ParameterDirection is that it is not required to call a stored procedure with the right direction - it will honor it where appropriate. So, if you use ParameterDirection.Input on a parameter that is marked in the signature of a stored procedure as OUTPUT, it will just not assign the local parameter to the returned value.
No, I mean why isn't the compiler complaining at design time that I'm passing a Direction parameter to a constructor which requires either a string, or a direction followed by several other parameters? I've narrowed down this problem to the point where I no longer need a stored procedure as part of the test. I set the direction as Output and immediately after that, with a breakpoint on the next line, I can take a look at the value of the direction I've just stored and it's been changed to Input!
|||
Ahh! Sorry about that. You are not actually setting the ParameterDirection at all in your scenario. So, I would say that the designer is either casting that to a string and storing it in 'sourcecolumn'. If you want to set the ParameterDirection with the SqlParameter constructor, you must use a more complete signature:
public SqlParameter(string parameterName, SqlDbType dbType,int size, ParameterDirection direction,bool isNullable,byte precision,byte scale,string sourceColumn, DataRowVersion sourceVersion,object value);So, it would be something like this:
cmd.Parameters.Add(new SqlParameter("@.memberdid", SqlDbType.Int, 4, ParameterDirection.Output,true, 0, 0,null, DataRowVersion.Current, memberId ) );|||
Ahh! Sorry about that. I would say that the designer is either casting that to a string and storing it in 'sourcecolumn'. If you want to set the ParameterDirection with the SqlParameter constructor, you must use a more complete signature:
public SqlParameter(string parameterName, SqlDbType dbType,int size, ParameterDirection direction,bool isNullable,byte precision,byte scale,string sourceColumn, DataRowVersion sourceVersion,object value);So, it would be something like this:cmd.Parameters.Add(new SqlParameter("@.memberdid", SqlDbTypeInt, 4, ParameterDirection.Output,true, 0, 0,null, DataRowVersion.Current, memberId ) );
Exactly! I did try to enter some of the extra parameters after direction but I couldn't see an end to it - they just went on forever!
So: What I Learnt Today - don't trust the little pop-up `enter some parameters for the method` things because they'll not perform any type checking to ensure that you pass enough parameters of the correct type. Instead, they'll be ordered in alphabetical order. Check that what you're trying to pass is valid for what you want by typing the opening bracket for your parameters and then looking at the whole set of possible signatures. The .Net compiler silently casts ints to strings without so much as a warning, even in Option Strict mode. Be good to yourself...and each other.
