Hey Tim,
I've always used the ParameterDirection.InputOutput rather than just .Output and sure enough, that's what the problem is. If you look at a trace in SQL Profiler to see what actually gets passed to SQL, you'll see that that the parameter is defined as nvarchar(1) when using just the .Output direction (consequently it's truncated), but correctly set to nvarchar(10) when using .InputOutput.
The culprit is actually the .AddWithValue() in combination with using the .Output direction I think. If you use the older methodology for setting the parameter, it will work with the .Output direction:
cmd.Parameters.Add("@NextNumber", SqlDbType.VarChar, 10);
cmd.Parameters["@NextNumber"].Direction = ParameterDirection.Output;I like to use .AddWithValue(), but this little gotcha is something to keep in mind ... always use ParameterDirection.InputOutput instead of ParameterDirection.Output and you will be ok.
I don't know if you were using this, but Sql Profiler is a handy little tool ... it really helps troubleshoot.
~~Bonnie
>Hi Bonnie,
>
>>>It appears to be something with the way SQL works and the stored Procedure. When I run the stored procedure directly in SSMS I can see the value of the parameter in addition to the return value of 0, but in code I can't get that returned. I eventually tried using just native dot net stuff without the mm layer and I get the same thing so ruled out any mm implementation of it. I am really not sure right now what is causing the issue and I am hoping maybe some others from the SQL group will see something about the way the SP is constructed.
>>
>>Hi Tim,
>>
>>It could be the SP or the code used to retrieve the OUTPUT parameter ... it's hard to say because you guys have posted a bunch of different versions of SP and code in this thread, and I can't say for sure anymore which is the final version that isn't working for you.
>>
>>If you can post it again, SP and code, then maybe something will jump out at me ...
>>
>>EDIT ... what I mean, if it wasn't clear, is to post the native .NET code, not the MM-based code.
>>
>>~~Bonnie
>
>Yes, we were trying lots of things and I tried to use non MM code to verify the issue. Here is the code and the stored procedure.
>
>
>public string GetNextNumber()
>{
> string query = "dbo.GetNextNumber";
> string connString = this.GetConnectionString("PantryWare");
> string newNumber = string.Empty;
>
> using (SqlConnection conn = new SqlConnection(connString))
> {
> using (SqlCommand cmd = new SqlCommand(query, conn))
> {
> cmd.CommandType = CommandType.StoredProcedure;
> cmd.Parameters.AddWithValue("@NextNumber", "0000000000");
> cmd.Parameters["@NextNumber"].Direction = ParameterDirection.Output;
> conn.Open();
> cmd.ExecuteNonQuery();
> newNumber = (string)cmd.Parameters["@NextNumber"].Value;
> }
> }
> return newNumber;
>}
>>
>Stored Procedure
>
>USE [PantryWare]
>GO
>/****** Object: StoredProcedure [dbo].[GetNextNumber] Script Date: 06/27/2010 08:15:25 ******/
>SET ANSI_NULLS ON
>GO
>SET QUOTED_IDENTIFIER ON
>GO
>-- =============================================
>-- Author: <Author,,Name>
>-- Create date: <Create Date,,>
>-- Description: <Description,,>
>-- =============================================
>ALTER PROCEDURE [dbo].[GetNextNumber]
> -- Add the parameters for the stored procedure here
> @NextNumber varchar(10) Output
>AS
>BEGIN
> -- SET NOCOUNT ON added to prevent extra result sets from
> -- interfering with SELECT statements.
> SET NOCOUNT ON;
>
> Set @NextNumber = '0000001045'
>END
>
>
>
>I would like to figure it out although it wasn't my problem to start with. I got interested in this because I am trying to up my level of knowledge with SQL and using it.
>Thanks
>Tim
