Sql Parameters mysteriously disappear

Ernst

Hey,

I have a little bit of a problem with my project. When I try to use SQL parameters in the way C# intended them, they don't work

In the following code, I try to get the parameters in, however, it hangs on "SqlException: Required parameter @Name was not supplied".

string command = "exec AuthPlayer";
            SqlCommand Sql = new SqlCommand(command, MSSQL);
            MSSQL.Open();
            Sql.Parameters.AddWithValue("@Name", Name);
            Sql.Parameters.AddWithValue("@Pass", DoSha1(Pass));

            int PlayerID;
            try
            {
                PlayerID = (int)Sql.ExecuteScalar();
            }
            catch (Exception x)
            {
                MSSQL.Close();
                throw x;
            }

            MSSQL.Close();
            return PlayerID;

Does anyone know what my SQL server is smoking?

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ashley Argile18y ago
Accepted answer
Try the following, taking note of the bold lines.

string command = "AuthPlayer"; // Removed exec from this line
            SqlCommand Sql = new SqlCommand(command, MSSQL);
            Sql.CommandType = CommandType.StoredProcedure; // Add this line
            MSSQL.Open();
            Sql.Parameters.AddWithValue("@Name", Name);
            Sql.Parameters.AddWithValue("@Pass", DoSha1(Pass));

            int PlayerID;
            try
            {
                PlayerID = (int)Sql.ExecuteScalar();
            }
            catch (Exception x)
            {
                MSSQL.Close();
                throw x;
            }

            MSSQL.Close();
            return PlayerID;

Hope this helps

Regards

Ashley.
Ashley Argile18y ago
No problem! Interestingly if your Stored Procedure only had one parameter then your original method would work...

Regards

Ashley
Ernst18y ago
OK, that did the trick, thank you very much!