Missing SP Parameter?

I've got a completely goofy problem on one development machine running Vista, SQL 2005 sp2, and Orcas Beta 2. I'm calling a stored proc from within my C# and my ExecuteDataReader() command fails saying I'm missing a parameter (which I'm not).

The c# looks like this:

if (SqlContext.IsAvailable)

{

using (System.Data.SqlClient.SqlConnection cnx =

new System.Data.SqlClient.SqlConnection("context connection=true"))

{

cnx.Open();

using (SqlCommand xCmd = new SqlCommand("dbo.TestMe", cnx))

{

xCmd.CommandType = CommandType.StoredProcedure;

xCmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("myParam", SqlDbType.Int));

xCmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("myStr",

SqlDbType.VarChar, 150));

xCmd.Parameters["myParam"].Value = 123;

xCmd.Parameters["myStr"].Value = "Hello";

xCmd.ExecuteNonQuery();

}

}

}

My Stored Proc look like this:

CREATE PROCEDURE [dbo].[TestMe]

@.myParam as int,

@.myStr as nvarchar(150)

AS

BEGIN

SELECT 1 as myCol

END

The result is a an exception that looks like this:

A .NET Framework error occurred during execution of user-defined routine or aggregate "StoredProcedure1":

System.Data.SqlClient.SqlException: Procedure or function 'TestMe' expects parameter '@.myParam', which was not supplied.

If I run the SP from the Sql Workbench, it looks fine. Any ideas? I just repaved SQL and Orcas on this machine to see if I can fix the problem -- no luck.

The problem is with how you have created the parameters in your C# code. When you are using the Sql provider and you are clling parameterized queries or stored proc with queries, the parameters are sent in by name. Your T-SQL code has two parameters: @.myParam and @.myStr, in your T-SQL code you create a param called myParam and myStr - you need the @. as well in the parameter name.

Niels
|||

Well that certainly fixed the problem. Thanks!

But has something changed in the SqlClient plumbing? I went back to look at our test machine, which is a Windows Server 2003 R2 / SQL 2005 SP2 (64-bit) system. The C# code works without the "@." in the param names as it does on other similarly configured systems. I even took the little routine from my code sample and deployed it -- sans "@." -- on our test machines and it works. Hmm.

Again thanks for the reply.

-Erik