C# ADO.NET - Could Not Find Stored Procedure

Question

I am trying to execute a stored procedure through C#, ADO.NET and below is the code I am trying to execute:

using (SqlConnection conn = new SqlConnection(".;Initial Catalog=MyDB;User ID=sa;Password=***"))
            {
                try
                {
                    string cmdText = "dbo.sp_Create_FlaggedItemEntry @URI, @ID";
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    conn.Open();
                    cmd.CommandText = cmdText;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@URI", value1);
                    cmd.Parameters.AddWithValue("@ID", value2);

                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
            }

Now when I try to debug it, I got an error at the line - cmd.ExecuteNonQuery(); - "Could Not Find Stored Procedure dbo.sp_Create_FlaggedItemEntry"

I verified that the Connection String is all correct and Stored Procedure exists. Further, If I change the line - cmd.CommandType = CommandType.StoredProcedure; to cmd.CommandType = CommandType.Text; it get executed successfully and as expected.

Can someone suggest what I am missing and doing wrong here - Please pardon me if it is something very basic as it is quite long since I last worked with ADO.NET

Answer 1

CommandType.StoredProcedure means that the CommandText should only contain the name of the stored procedure.

Remove the parameter names from the string.

Answer 2

Take the parameters out of the command text. Also, you don't need to specify dbo.

The reason it's working with CommandType.Text is because it's a legitimate SQL command like that - if you were to open up SSMS and type that in it'd work as long as you also create the variables @URI and @ID

Documentation here