Stored procedure having unspecified values altough value is passed

Question

Stored procedure executes fine if executed in SQL Server Management Studio.

In C# (Winforms) I have the following code:

InsertWarning.Parameters.AddWithValue("@idUser", userIDAuth);
InsertWarning.Parameters.AddWithValue("@idPass", idPass);

if (Privileged)
    MessageWarning += " gave you privileged access to note " + Description;
else
    MessageWarning += " gave you access to note " + Description;

InsertWarning.Parameters.AddWithValue("@Message", MessageWarning);
InsertWarning.ExecuteNonQuery(); 
InsertWarning.Parameters.Clear();

When ExecuteNonQuery() runs it stops saying the @idUser has no value.

Stored procedure in C#:

SqlCommand InsertWarning = new SqlCommand("_spInsertWarnings", TeamPWSecureBD);  

InsertAuths.CommandType = CommandType.StoredProcedure;

Stored procedure in SQL:

[dbo].[_spInsertWarnings] 
    @idUser int, @idPass int, @Message nvarchar(MAX)
AS
    INSERT INTO Warnings 
    VALUES(@idUser, @idPass, @Message)

Based on the formatting of the original post, it looks like this was part of a logical branch. Is there anywhere else that this proc is called? Also, what datatype is `@idUser` in the stored proc. Can you also post the header of the stored proc? — Maurice Reeves, Jul 29 '16 at 13:18
What does Sql Profiler show when you execute the stored procedure? — Chris Dunaway, Jul 29 '16 at 14:06
// Can you put Clear method on top of the add... And try once again? InsertWarning.Parameters.Clear(); InsertWarning.Parameters.AddWithValue("@idUser", userIDAuth); InsertWarning.Parameters.AddWithValue("@idPass", idPass); if (Privileged) MessageWarning += " gave you privileged access to note " + Description; else MessageWarning += " gave you access to note " + Description; InsertWarning.Parameters.AddWithValue("@Message", MessageWarning); InsertWarning.ExecuteNonQuery(); — sarathkumar, Aug 02 '16 at 09:26

score 1 · Answer 1 · answered Jul 29 '16 at 13:29

 using (SqlConnection con = new SqlConnection(dc.Con))
 {
    using (SqlCommand cmd = new SqlCommand("_spInsertwarnings", con))
    {
      cmd.CommandType = CommandType.StoredProcedure;

//Please Make SqlDataType as per your Sql ColumnType
      cmd.Parameters.Add("@idUser", SqlDbType.VarChar).Value = userIDAuth;
      cmd.Parameters.Add("@idPass", SqlDbType.VarChar).Value = idPass;


      con.Open();
      cmd.ExecuteNonQuery();
    }
  }

score 0 · Answer 2 · edited May 23 '17 at 12:24

0

The question in this post looks similar to yours: Stored procedure or function expects parameter which was not supplied

Have you tried using the .Parameters.Add("fieldname", type, value) instead? I'm wondering if even though you are seeing the value 8 in a debug session, it's not being recognized when you do a stored procedure call.

edited May 23 '17 at 12:24

Community

1
1

answered Jul 29 '16 at 13:22

silvenwolf

70
2
11

score 0 · Answer 3 · answered Jul 29 '16 at 13:29

0

Thinking about this again, my guess is you're missing a different parameter than @idUser, and that parameter does not have a default value assigned. Sometimes SQL Server reports the wrong name back for a parameter missing a value.

Look at your proc header and confirm that you're passing all the required parameters that the proc expects, or that you have sensible defaults assigned for the ones you don't always want to pass.

answered Jul 29 '16 at 13:29

Maurice Reeves

1,572
13
19

Thanks! So, actually, now I'm wondering if the missing datatype isn't an issue. I would try what @sarathkumar is suggesting. Make sure you're explicitly passing in the datatype as well. – Maurice Reeves Jul 29 '16 at 13:40
Look at @sarathkumar's example above: `InsertWarning.Parameters.Add('@idUser', SqlDbType.Int).Value = userIdAuth;` – Maurice Reeves Jul 29 '16 at 13:47
Used InsertWarning.Parameters.Add("@idUser", SqlDbType.Int).Value=userIDAuth; Same error... – walkman Jul 29 '16 at 13:48
Are you sure that your stored proc on the server matches the header you posted above? If you go on to the server and look at that stored proc directly, does it look like you expect? – Maurice Reeves Jul 29 '16 at 13:58

score 0 · Answer 4 · answered Jul 29 '16 at 13:58

I guess this might work, i have posted the code from where you are adding.

    InsertWarning.Parameters.Add("@idUser", SqlDbType.Int);
    InsertWarning.Parameters["@idUser"].Value = userIDAuth;
    InsertWarning.Parameters.AddWithValue("@idPass", idPass);
   try
   {
        connection.Open();
        InsertWarning.ExecuteNonQuery()
   }
  catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }

Stored procedure having unspecified values altough value is passed

4 Answers4