0

I have the followind Stored Procedure:

CREATE PROCEDURE [dbo].[SD_languagageDontMatch]
(@param1 NVARCHAR(150))
AS
BEGIN
SET NOCOUNT ON;

SELECT *
FROM Languages
WHERE Languages.ID_language not in (SELECT editorLanguage.IDlanguage FROM editorLanguage where editorLanguage.IDuser = @param1)
END

And the following code:

string strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
SqlConnection sqlConnection1 = new SqlConnection(strConnString);
SqlCommand cmd = new SqlCommand();

cmd.CommandText = "SD_languagageDontMatch";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@param1", HttpContext.Current.User.Identity.GetUserId());
SqlDataReader reader;


cmd.Connection = sqlConnection1;
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
reader = cmd.ExecuteReader();

//do stuff

reader.Close();
sqlConnection1.Close();
sqlConnection1.Dispose();

Everytime I run the code I get the following error:

Procedure or function 'SD_languagageDontMatch' expects parameter '@param1', which was not supplied.

I've worked with Stored Procedues before and I never faced this before. Everything seems to be correct. I executed the Stored Procedure directly on the server and it worked well. Is there anything missing in my code?

Thanks

UPDATE

I solved the probelm by opening the SQL connection before declaring the parameter:

sqlConnection1.Open();

cmd.CommandText = "SD_langDontMatch";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@param1", HttpContext.Current.User.Identity.GetUserId());
SqlDataReader reader;
reader = cmd.ExecuteReader();
Gloria
  • 1,305
  • 5
  • 22
  • 57
  • A user must be logged in for the code to run otherwise the page is not loaded.... – Gloria Oct 01 '14 at 08:12
  • 1
    In this situation it appears likely that `HttpContext.Current.User.Identity.GetUserId()` is returning null. There is no code included in your sample to verify that a user has logged in. If this is MVC, add the `AuthorizeAttribute` to the method and try again. I would also explicitly handle login verification in code. – Brett Wolfington Oct 01 '14 at 08:13
  • Also take a look to this post: http://stackoverflow.com/questions/9267078/when-executing-a-stored-procedure-what-is-the-benefit-of-using-commandtype-stor – Adriano Repetti Oct 01 '14 at 08:14
  • In the Page_Init I have the following code: if (!User.Identity.IsAuthenticated) { Response.Redirect "/Account/Login");} So since the page is loaded then the user is logged in.... – Gloria Oct 01 '14 at 08:19

0 Answers0