SQLDataReader not returning rows/populating list

Question

I'm trying to return all rows of a single column in my database to populate a list. When I execute the stored procedure in SQL, it works fine, but nothing gets returned when I try to do it in C#.

public static List<string> GetRows(string filter_one, string filter_two)
{
    var retrievedRows = new List<string>();
    var storedProc = "dbo.MyStoredProc";

    using (SqlConnection connection = new SqlConnection(MY_CONNECTION_STRING))
    using (SqlCommand command = new SqlCommand(storedProc, connection))
    {
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add("@FilterOne", SqlDbType.VarChar).Value = filter_one;
        command.Parameters.Add("@FilterTwo", SqlDbType.VarChar).Value = filter_two;
        connection.Open();

        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                retrievedRows.Add(reader.GetString(0));
            }
        }
    }
    return retrievedRows;
}

Any ideas? I get no errors in the console or when I run it on IIS Express either. When I watch retrievedRows, the size stays at 0 even though when I run the same query in SQL with the same passed parameters, it returns results for me.

EDIT: Please excuse me, my brain must be running a bit slow today. One of the parameters I was passing was pointed at the (empty) value of the wrong webcontrol. I don't know how I missed this.

Are you using the exact same values for filter_one and filter_two as used in the stored procedure to return records? — Taco
– Taco, Commented Oct 20, 2020 at 20:10
Also, if you put a breakpoint on the line where it's adding records (retrievedRows.Add(reader.GetString(0));), does it stop execution there? — Taco
– Taco, Commented Oct 20, 2020 at 20:11
You need to show us your SP definition, at least the parameters and the return part. — Dale K
– Dale K, Commented Oct 20, 2020 at 20:16
have you tried specifying parameter direction? parameter.Direction = ParameterDirection.Input; — Nonik
– Nonik, Commented Oct 20, 2020 at 20:16

Taco · Accepted Answer · 2020-10-20 20:45:34Z

1

There is only one issue with your posted snippet that I can see, which could pose a problem:

command.Parameters.Add("@FilterOne", SqlDbType.VarChar).Value = filter_one;
command.Parameters.Add("@FilterTwo", SqlDbType.VarChar).Value = filter_two;

In this section, you're adding two VARCHAR parameters but not specifying a length for them. Try changing your code to add a length specification:

var filterOne = new SqlParameter("FilterOne", System.Data.SqlDbType.VarChar, 50);

The constructor in use here is SqlParameter(string, SqlDbType, int):

Parameters -

parameterName (String): The name of the parameter to map.

dbType (SqlDbType): One of the SqlDbType values.

size (Int32): The length of the parameter.

When working with VARCHAR you must specify a length or anything outside of the default length (which is 1 byte for definitions and variables, and 30 bytes for CAST and CONVERT) will be truncated:

When n isn't specified in a data definition or variable declaration statement, the default length is 1. If n isn't specified when using the CAST and CONVERT functions, the default length is 30.

edited Oct 20, 2020 at 20:45

answered Oct 20, 2020 at 20:16

Taco

2,9531 gold badge20 silver badges55 bronze badges

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

2 Comments

Bryan Tran Over a year ago

Edited my post, although thank you for the response! I didn't know that you needed to specify the length for the parameters you add. Helpful to know!

Taco Over a year ago

@BryanTran glad to be of service and help a fellow developer out! My apologies if my initial verbiage sounded a bit blunt or negative, it wasn't meant that way at all. Also, welcome-ish to Stack Overflow! 🙋

Collectives™ on Stack Overflow

SQLDataReader not returning rows/populating list

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related