C# application with SQL Server stored procedure

Question

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)

The error is clear - you are creating and passing a parameter, and the stored proc doesn't have any. If you are expecting it to contain the returned value, look up how to return a value from a stored procedure. There are a couple of well documented ways to do it. — TomC
– TomC, Commented May 29, 2020 at 1:50
Also: SQL Server 2005 is waaaaaaaay out of support and should NOT be used for any development anymore - same goes for 2008 and 2008 R2. You should really use something more recent - like SQL Server 2017 or 2019 — marc_s
– marc_s, Commented May 29, 2020 at 4:21

marc_s · Accepted Answer · 2020-05-29 04:23:00Z

1

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.

answered May 29, 2020 at 4:23

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pramu Over a year ago

C# code does not any respond, And SQL server gave ma Error message as 'Procedure or function 'Proc_New_LoadTest1' expects parameter '@No', which was not supplied.'

Collectives™ on Stack Overflow

C# application with SQL Server stored procedure

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related