C# and SQL Server: getting an error in stored procedure

Question

I'm working on a C# project with a SQL Server database. I have a stored procedure prcRole that performs INSERT, UPDATE, and DELETE operations on a tblRole table. The RoleID column is an identity column, so it’s not required for INSERT but is required for UPDATE and DELETE.

When I call the stored procedure from my C# code, I get the following error at sda.Fill(dt) :

System.Data.SqlClient.SqlException (0x80131904): Procedure or function 'prcRole' expects parameter '@RoleID', which was not supplied

Here's the stored procedure prcRole:

CREATE PROCEDURE [dbo].[prcRole]
    @Operation NVARCHAR(100), 
    @RoleID INT, 
    @RoleName NVARCHAR(255),  
    @User INT 
AS 
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;

        IF @Operation = 'INSERT'
        BEGIN
            INSERT INTO tblRole (RoleName, CreatedBy, DateCreated, LastEditedBy,DateEdited)
            VALUES (@RoleName, @User, GETDATE(), @User, GETDATE());
        END

        IF @Operation = 'UPDATE'
        BEGIN
            UPDATE tblRole
            SET RoleName = @RoleName, LastEditedBy = @User, DateEdited = GETDATE()
            WHERE RoleID = @RoleID;
        END

        IF @Operation = 'DELETE'
        BEGIN
            DELETE FROM tblRole WHERE RoleID = @RoleID;
        END

        SELECT * FROM tblRole;
        COMMIT;
    END TRY
    BEGIN CATCH
        ROLLBACK;
        THROW;
    END CATCH; END GO

Here's my C# code that calls the stored procedure:

   private DataTable prcRole(string Operation, Role role)
   {
       DataTable dt = new DataTable();

       try
       {
           using (SqlConnection con = new SqlConnection("Server=localhost\MSERVERSQL;Database=DBPharmacy;Trusted_Connection=True;"))
           {
               using (SqlCommand cmd = new SqlCommand("prcRole", con))
               {
                   cmd.CommandType = CommandType.StoredProcedure;

                   cmd.Parameters.AddWithValue("@Operation", Operation);
                   cmd.Parameters.AddWithValue("@RoleID", role.RoleID);
                   cmd.Parameters.AddWithValue("@RoleName", role.RoleName);

                   cmd.Parameters.AddWithValue("@User", 1); // 1 will be replaced with actual logged in user 

                   using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                   {
                       sda.Fill(dt);
                   }
               }
           }
       }
       catch (Exception ex)
       {
           MessageBox.Show(ex.ToString());
       }

       return dt;
   }

Here's the button1_Click event handler that triggers the INSERT operation - the user type in the Role name in a text box and click on ADD button, where it must be added to the database:

private void button1_Click(object sender, EventArgs e)
{
     try
     {
         string roleName = txtRoleName.Text.Trim(); // Get the role name from the text box

         if (string.IsNullOrEmpty(roleName))
         {
             MessageBox.Show("Please enter a role name.", "Validation Error", MessageBoxButtons.OK, MessageBoxIcon.Warning);
             return;
         }

         Classes.Role newRole = new Classes.Role(0, roleName);

         newRole.INSERT();

         MessageBox.Show("Role added successfully!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);

         txtRoleName.Clear(); 
     }
     catch (Exception ex)
     {
         MessageBox.Show($"Error: {ex.Message}", "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
     }
 }

C# and SQL Server: getting an error in stored procedure

Answers (1)

Related Questions