C# application with SQL Server stored procedure

My C# Windows form has a combobox and textbox. The combobox has 2 records which are ‘Novel’ & ‘Poem’.

I need to write code that if I select ‘Poem’, it should be auto generate ‘PO-00001’ and if I select ‘Novel’, it should be auto generate ‘NV-00001’.

So then I wrote a stored procedure to do that and it looks like this:

ALTER PROCEDURE [dbo].[Proc_New_LoadTest]
AS
BEGIN
    DECLARE @MaxNo int, @No Varchar(50);

    SELECT @MaxNo = ISNULL(MAX(CAST(RIGHT(ISBN, 5) AS int)), 0) + 1 
    FROM bookstock 
    WHERE category = @No            

    SELECT RIGHT('00000' + CAST(@MaxNo AS varchar(50)), 5);
    RETURN @MaxNo
END

This is my C# code:

void loadisbn()
{
    Connection con = new Connection();

    SqlCommand cmd = new SqlCommand("[Proc_New_LoadTest]", con.ActiveCon());
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@No", SqlDbType.VarChar).Value = cmbcategory.Text.Trim();

    cmd.ExecuteNonQuery();
}

I tried to do here as if select ‘Novel’ by combobox, so it should find maximum isbn number from isbn column and if it is ‘Poem’ so related maximum isbn to it (these 2 records are contained in a one column).

But my code throws an error:

Procedure Proc_New_LoadTest has no parameters and arguments were supplied.

Please help me to debug this error (my SQL Server version is SQL Server 2005 Express)

Answer

If you want the @no to be a parameter for the stored procedure, you need to define it like this:

ALTER PROCEDURE [dbo].[Proc_New_LoadTest]
    @No Varchar(50);
AS
BEGIN 
    DECLARE @MaxNo int;

    SELECT @MaxNo = ISNULL(MAX(CAST(RIGHT(ISBN, 5) AS int)), 0) + 1 
    FROM bookstock 
    WHERE category = @No            

    SELECT RIGHT('00000' + CAST(@MaxNo AS varchar(50)), 5);

    RETURN @MaxNo
END

and in your C# code, you have a funny way of combining two approaches to providing a value – the best practice would be to use this line of code:

cmd.Parameters.Add("@No", SqlDbType.VarChar, 50).Value = cmbcategory.Text.Trim();

Use the .Add() method (not .AddWithValue()), and explicitly define the length of your varchar parameter.