SqlDataReader.ExecuteReader(); returning null object even if all parameters are provided

Question

SqlConnection connection = Connect();

SqlCommand command = new SqlCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = sprocName;
command.Connection = connection;
//loop through the dictionary
foreach (string key in paramList.Keys)
{
        command.Parameters.AddWithValue(key, paramList[key]);
}
SqlDataReader dataReader = command.ExecuteReader();

I am trying read data from database, when I am passing same parameters and execute the SP then I am getting proper result.But here SqlDataReader dataReader = command.ExecuteReader(); when I am checking (dataReader.HasRows) it returns false!

That's not possible ... check if you are running against same DB/Table — Rahul
– Rahul, Commented Feb 12, 2019 at 6:48
have you checked that your query actually returns value from the DB? — styx
– styx, Commented Feb 12, 2019 at 6:48
@styx yes I have checked query actually returns value from db. — Mitali Patil
– Mitali Patil, Commented Feb 12, 2019 at 7:02

Mohit Raiyani · Accepted Answer · 2019-02-12 06:56:32Z

0

Does you key contains '@', for example if key parameter name EmployeeId then needs to add parameter as below

commnad.Parameters.AddWithValue('@EmployeeId', 1);

answered Feb 12, 2019 at 6:56

Mohit Raiyani

213 bronze badges

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

1 Comment

Mitali Patil Over a year ago

Yes It contains @.

Daniel Brughera · Accepted Answer · 2019-02-13 08:17:30Z

0

Exec up_GetAtt @pageNumber=1, @pagesize=10, @PID=1000, @DeID=NULL, @DeName =NULL, @SortByColumn='ReleaseDate', @SortOrder='DESC' this are the parameters I am passing

You need to convert the C# null values into DBNull

SqlConnection connection = Connect();

SqlCommand command = new SqlCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = sprocName;
command.Connection = connection;
//loop through the dictionary
foreach (string key in paramList.Keys)
{
        command.Parameters.AddWithValue(key, paramList[key] == NULL? DBNull.Value : paramList[key]);
}
SqlDataReader dataReader = command.ExecuteReader();

Given the lack of information to help you better, check also for common mistakes

Typos in proc and parameter names
Sending a query with CommandType.StoredProcedure: sprocName value must be just "up_GetAtt", is a common mistake to include the EXEC word or parameters.
Check Value Types, your paramList should be a dictionary, and the value type of the items that you put in that list should match with the sql equivalent type, not all types have implicit conversion.
Check nullability of paramList items, use nullable int and datetime
Check that the parameter names start with @
Omitting a parameter does not mean that it is null, unless your store procedure definition default it as null
Omitting a parameter with no default value in procedure definition
Unnecessary quotation of values, you are sending parameter values, it is not a concat

edited Feb 13, 2019 at 8:17

answered Feb 12, 2019 at 11:36

Daniel Brughera

1,6511 gold badge9 silver badges14 bronze badges

3 Comments

Mitali Patil Over a year ago

Yes I have already did that I am passing dbnull values only for c# null values.

Mitali Patil Over a year ago

When I am trying to view Result View of SqlDataReader it shows error "Enumaration yielded no result"

Daniel Brughera Over a year ago

Troubleshot the list I posted in the last edition, nothing else come to my mind if you do not post more details

Collectives™ on Stack Overflow

SqlDataReader.ExecuteReader(); returning null object even if all parameters are provided

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related