Indifferent behaviour when using Execute Reader in C#

Question

I have a stored procedure it returns a name so i need to get the name in in C#

so i just execute the Sp from C# and read the out put using ExecuteReader() like below

 using (var objCommand = new SqlCommand("SpName", objConnection))
                    {
                        objCommand.Parameters.AddWithValue("@Param1", Param);
                        objCommand.Parameters.AddWithValue("@Purpose", Purpose);
                        objConnection.Open();
                        objCommand.CommandType = CommandType.StoredProcedure;
                        using (var reader = objCommand.ExecuteReader())
                        {

                                while (reader.Read())
                                {

                                    objemailsend.Name = Convert.ToString(reader["Name"]);
                                }

                        }
                    }

         objConnection.Close();

It gives an exception

Index Out of range exception

I am sure that the index names are same i mean the Sp also returns the same name

Name
----
name1

like above

then i tried something like below and now the exception disappears and it returns a numeric value i don't know from where the value coming from and it is not same as my SP result

objexample.Name = reader[0].ToString();

also tried

 using (var reader = objCommand.ExecuteReader())
                        {
                             if (reader.HasRows)
                        {
                                while (reader.Read())
                                {

                                    objemailsend.Name = Convert.ToString(reader["Name"]);
                                }
}

                        }

But the same error

Can you somebody help me to solve my issue.

*One* name? If so ExecuteScalar or an output param would be simpler — Alex K., Jun 30 '15 at 11:49
@AlexK. yes i agree,this is only a sample code and it not a single value it contain more than one, i will insert that values in to a list. this is only a simple example , but when i use this too i did not get result — Arunprasanth K V, Jun 30 '15 at 11:52
@DmitryBychenko sure i will keep in mind that point thank you — Arunprasanth K V, Jun 30 '15 at 11:53
Have you checked there is a "Name" column in the returned data? — PaulF, Jun 30 '15 at 11:54
@TimSchmelter objemailsend.Name = Convert.ToString(reader["Name"]); this line , the line that assigns the out put value to local variable — Arunprasanth K V, Jun 30 '15 at 11:54
@PaulF from sql profiler take the query and manuvaly executed in sql management studio — Arunprasanth K V, Jun 30 '15 at 11:55
@ArunprasanthKV: that exception is thrown if ["no column with the specified name was found"](https://msdn.microsoft.com/en-us/library/f01t4cfy(v=vs.110).aspx). So you should show us your stored-procedure. — Tim Schmelter, Jun 30 '15 at 11:57
Not whether it has rows - but the actual results - the exception would be expected if there is not a "Name" field in the row read. — PaulF, Jun 30 '15 at 11:58
@TimSchmelter yes i agree with you,many times i had searched internet for a solution and the same thing is all are saying , i am sure the field name is exact same. but it throws exeption , i dont know what is happening here that is why i posted the question here — Arunprasanth K V, Jun 30 '15 at 11:59
@PaulF agreed , but y sp returns the column with same name . — Arunprasanth K V, Jun 30 '15 at 12:00
@ArunprasanthKV: you can use this to see all columns: `String.Join(",", Enumerable.Range(0, reader.FieldCount).Select(i => reader.GetName(i)))` I'm pretty sure that you don't find a `Name`-column — Tim Schmelter, Jun 30 '15 at 12:04
To be sure what your SP return check on database with parameters: `exec SpName param1, param2` — Svmurvj, Jun 30 '15 at 12:09
@TimSchmelter yes you are right , there is no column named NAME and the colum names comming here is my sp's catch error deatils column names , but it is strange , when i directly run the sp in sql management studio it results the name — Arunprasanth K V, Jun 30 '15 at 12:13
@TimSchmelter i had check that , and when ever i run the Sp directly in sql management studio it gives correct out put , when i call it through C# it outputs the catch error .i don't know why it happends , you have any idea about this ? please help me — Arunprasanth K V, Jun 30 '15 at 12:37
@TimSchmelter finally i have fixed it , it my query i had included SET ARITHABORT ON on and now the problem solved — Arunprasanth K V, Jun 30 '15 at 12:53

score 0 · Answer 1 · edited May 23 '17 at 12:21

0

Please check this answer out

var returnParameter = cmd.Parameters.Add("RetVal", SqlDbType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery();
int id = (int) returnParameter.Value;

edited May 23 '17 at 12:21

Community

1
1

answered Jun 30 '15 at 12:02

sm14

119
2

without the stored proc we were all guessing – sm14 Jul 06 '15 at 17:12

score 0 · Answer 2 · answered Jun 30 '15 at 12:05

Try retrieving the value using index like objemailsend.Name = reader[1].ToString(). Where I have assumed 1 to be the index for name column. As you are getting the Index Out of range execption this will help because here you are explicitly providing the index for the name column.

using (var objCommand = new SqlCommand("SpName", objConnection))
                    {
                        objCommand.Parameters.AddWithValue("@Param1", Param);
                        objCommand.Parameters.AddWithValue("@Purpose", Purpose);
                        objConnection.Open();
                        objCommand.CommandType = CommandType.StoredProcedure;
                        using (var reader = objCommand.ExecuteReader())
                        {

                                while (reader.Read())
                                {

                                    objemailsend.Name = Convert.ToString(reader[1]);
                                }

                        }
                    }

         objConnection.Close();

Indifferent behaviour when using Execute Reader in C#

2 Answers2