Looping through gridview to send to stored procedure is stuck in loop

Question

I think i have been looking at this to long.. I have 2 gridviews with data. I want to loop through them and send each item to a stored procedure. That stored procedure checks for duplicates and sends back 1 if they exists and 0 if they don't. Well when I do the for each loop.. I get stuck. I have tried moving things around with no luck.. Here is my code:

public int IsExists() 
{
    foreach (GridViewRow row in gvSerials.Rows) 
    {
        using (SqlConnection con = new SqlConnection(strConnString)) 
        {
            using (SqlCommand cmd = new SqlCommand("usp_InsertReceiptSerials", con)) 
            {
                cmd.CommandType = CommandType.StoredProcedure;
                con.Open();
                cmd.Parameters.Add("@ITEMNMBR", SqlDbType.Char).Value = OpenDescription.SelectedRow.Cells[5].Text.Trim();
                cmd.Parameters.Add("@RecLineID", SqlDbType.Int).Value = int.Parse(OpenDescription.SelectedRow.Cells[1].Text.Trim());
                cmd.Parameters.Add("@RCPTLNNM", SqlDbType.Int).Value = int.Parse(OpenDescription.SelectedRow.Cells[8].Text.Trim());
                cmd.Parameters.Add("@POPRCTNM", SqlDbType.Char).Value = OpenDescription.SelectedRow.Cells[4].Text.Trim();
                cmd.Parameters.Add("@SERLTNUM", SqlDbType.Char).Value = row.Cells[0].Text.Trim();
                SqlParameter parm = new SqlParameter("@IsExists", SqlDbType.Int);
                parm.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(parm);


                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
    }

    return IsExists();
    if (IsExists() == 1) {
        MessageBox.Show("Serials Already Exists!!");
        Response.Redirect("Index.aspx"); }
    else if (IsExists() == 0) {
        MessageBox.Show("Serial Numbers have been updated.", "Important Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
        Response.Redirect("Index.aspx"); }
}

Here is the stored proc:

 ALTER PROCEDURE [dbo].[usp_InsertReceiptSerials]
        @POPRCTNM CHAR(17), @ITEMNMBR CHAR(31), @SERLTNUM CHAR(21), @RecLineID INT, @RCPTLNNM INT, @IsExists INT OUTPUT

    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @RecCount INT
    --  DECLARE @IsExists INT

        IF EXISTS (SELECT * FROM dbo.vwSerialNumbers WHERE SERLNMBR = Right(@SERLTNUM,20) AND ITEMNMBR = @ITEMNMBR)
            BEGIN 
                SET @IsExists = 1
            END
        ELSE
            BEGIN
                INSERT INTO dbo.usr_ReceiptSerials(POPRCTNM, ITEMNMBR, SERLTNUM, FullSerialNumber, EntryDate, RecLineID, RCPTLNNM)
                VALUES (@POPRCTNM, @ITEMNMBR, Right(@SERLTNUM,20), @SERLTNUM,  GetDate(), @RecLineID, @RCPTLNNM)
                SET @RecCount = (SELECT COUNT(*) FROM dbo.usr_ReceiptSerials  WHERE RecLineID = @RecLineID)
                UPDATE dbo.usr_ReceiptLine SET QTYSHPPD = @RecCount
                WHERE RecLineID = @RecLineID
                SET @IsExists = 0
            END
    END

It adds them to the db and then gives me this error: An unhandled exception of type 'System.StackOverflowException' occurred in System.Data.dll I believe it is stuck in the loop — Deanna Viper, Sep 14 '15 at 17:08

score 1 · Accepted Answer · edited May 23 '17 at 12:14

1

I can see a simple recursion problem which is causing the stackoverflow.

=> return IsExists();

In your returns staement you're calling the same function. Remove this line if this is not part of any business logic.

Also, rather checking if (IsExists() == 1)

use:

int result = cmd.ExecuteNonQuery(); Then do the evaluation on result:

   if (result == 1) {
        MessageBox.Show("Serials Already Exists!!");
        Response.Redirect("Index.aspx"); }
    else if (result == 0) {
        MessageBox.Show("Serial Numbers have been updated.", "Important Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
        Response.Redirect("Index.aspx"); }

For build error you must return an integer value that method is expecting. E.g. It could be result itself.

Update:

In your case it seems you want to read the value of Output parameter. To read an output parameter value from Command object see this answer. Then result variable would be assigned by the Output parameter that you will read from SqlParameter after exeuction of Command.

edited May 23 '17 at 12:14

Community

1
1

answered Sep 14 '15 at 17:16

vendettamit

14,315
2
32
54

I just removed that line and now get a build error: not all paths return a value – Deanna Viper Sep 14 '15 at 17:18
`ExecuteNonQuery` only returns the number of rows affected. The OP has an output parameter for `exists`, so catching the return value doesn't help. – Dave Zych Sep 14 '15 at 17:20
@DaveZych Updated the answer! I got it after looking at the stored proc. Thanks. – vendettamit Sep 14 '15 at 17:24
Thank you! Thank you! That was driving me crazy! – Deanna Viper Sep 14 '15 at 17:34

score 0 · Answer 2 · answered Sep 14 '15 at 17:25

Building upon the other answer:

public int IsExists() 
{
    int returnInt =0;
    foreach (GridViewRow row in gvSerials.Rows) 
    {
        using (SqlConnection con = new SqlConnection(strConnString)) 
        {
            using (SqlCommand cmd = new SqlCommand("usp_InsertReceiptSerials", con)) 
            {
            // /.../
            if whatever your testing is true:
            returnInt = 1;
            or
            return returnInt;

            or
            return 1;
            or wait until the end of the method if you want to add more stuff.
            }
        }
    }
    // Do any more stuff here.

    return returnInt();
    // Nothing beyond here is executed.

}

Anything you do after you return in a method, will not be executed. i.e. the method ends with the return, that value is then taken back to the calling method.

Looping through gridview to send to stored procedure is stuck in loop

2 Answers2