System.IndexOutOfRangeExceptio...

Question

0.00/5 (No votes)

See more:

Hi I am trying to return this line of code RoomNotAvailable = Convert.ToString(sdr["RoomNotAvailable"]) from IEnumerable if the first condition failes, the that one will execute But it is not working!

This is my stored procedure:

SQL

alter proc [dbo].[GetRoomsByDate]
@Checkin datetime = null
,@Checkout datetime = null
as
begin
   if exists(select * from Reservation Rv where (@Checkin between Rv.checkin and Rv.checkout) and
       (Rv.IsReserved = 1 and Rv.IsCanceled = 1))
       begin
         select R.* from [dbo].[Room] R
         inner join Reservation Rv on R.RoomId = Rv.RoomId
       end
   else
   begin
      select &amp;#39;Room is already Reserved&amp;#39; as RoomNotAvailable
      Return 1
   end

end

And this my code from cs file:

C#

public IEnumerable<RoomInfo> GetAvailableRoomByDate()//IEnumerable<T> is great for working with in-memory collections, but IQueryable<T> allows for a remote data source, like a database or web service.
        {
            using (SqlDataReader sdr = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), "[GetRoomsByDate]", Checkin, Checkout))
            {
                if (sdr.HasRows)
                {
                    while (sdr.Read())
                    {
                        yield return new RoomInfo
                        {

                            RoomId = Convert.ToInt32(sdr["RoomId"]),
                            RoomTitle = Convert.ToString(sdr["RoomTitle"]),
                            RoomName = Convert.ToString(sdr["RoomName"]),
                            RoomPrice = Convert.ToInt32(sdr["RoomPrice"]),
                            RoomDescription = Convert.ToString(sdr["RoomDescription"]),


                        };
                    }
                }
                else
                {
                    while (sdr.Read())
                    {
                        yield return new RoomInfo
                        {
                            RoomNotAvailable = Convert.ToString(sdr["RoomNotAvailable"])


                        };
                    }
                }
            }
        }

Posted 19-Nov-14 18:40pm

El Dev

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sinisa Hajnal · Answer 1 · 2014-11-19T20:12:00

It is not working because your condition is never false. You're asking sdr for the rows. And it always has at least one row.

What you need to do is one of:
1. Add RoomNotAvailable as a field in the first query and set it to empty. Change if exists to WHERE clause and then do union with the empty row. (see SQL below) In the code, hold a variable with a check if there was at least one available room and ignore empty row if there was.

2. check if the sdr contains the field RoomNotAvailable instead of checking it hasRows.

For the first solution, SQL would go like this:

SQL

         SELECT R.roomId, r.RoomTitle, RoomName, RoomPrice, RoomDescription, RoomNotAvailable = NULL
        FROM[dbo].[Room] R
         INNER JOIN Reservation Rv on R.RoomId = Rv.RoomId
        WHERE 
            @Checkin between Rv.checkin and Rv.checkout
            aND Rv.IsReserved = 1 
            and Rv.IsCanceled = 1
UNION ALL
      select RoomId = 0, RoomTitle = NULL, RoomPrice = NULL, RoomDescription = NULL, RoomNotAvailable = 'Room is already Reserved'
ORDER BY RoomNotAvailable -- this ensures that your available rooms are first

Suggestion: don't use * in selects, it can cause all sorts of trouble as number of tables in joins increases and you're returning data (increasing traffic) that you don't need.

If this helps please take time to accept the solution. Thank you.

/\jmot · Answer 2 · 2014-11-19T23:37:00

Solution 2

Try this..

C#

if (sdr.HasRows)
 {
while (sdr.Read())
{
bool res=false;
for (int i=0; i < sdr.FieldCount; i++)
 {
   if (sdr.GetName(i).Equals(RoomNotAvailable,StringComparison.InvariantCultureIgnoreCase))
   res=true;
 }
if(res)
{
yield return new RoomInfo
{
RoomNotAvailable = Convert.ToString(sdr["RoomNotAvailable"])


};
}
else
{
yield return new RoomInfo
{

RoomId = Convert.ToInt32(sdr[&amp;quot;RoomId&amp;quot;]),
RoomTitle = Convert.ToString(sdr[&amp;quot;RoomTitle&amp;quot;]),
RoomName = Convert.ToString(sdr[&amp;quot;RoomName&amp;quot;]),
RoomPrice = Convert.ToInt32(sdr[&amp;quot;RoomPrice&amp;quot;]),
RoomDescription = Convert.ToString(sdr[&amp;quot;RoomDescription&amp;quot;]),

 };
}
 }
 }
</pre>

Updated:

C#

Remove this code 
bool res=false;
for (int i=0; i < sdr.FieldCount; i++)
 {
   if (sdr.GetName(i).Equals(RoomNotAvailable,StringComparison.InvariantCultureIgnoreCase))
   res=true;
 }
and replace by the following code.
if (sdr.GetName(0).Equals(RoomNotAvailable,StringComparison.InvariantCultureIgnoreCase))
   res=true;

Posted 19-Nov-14 23:37pm

/\jmot

Updated 20-Nov-14 18:20pm

v4

Comments

El Dev 20-Nov-14 23:56pm

Hi Aajmost,

I have tried both your way but none of them are working!!
This how I am doing but now it is generating an error saying that:
"Invalid attempt to call FieldCount when reader is closed."

public IEnumerable<roominfo> GetAvailableRoomByDate()//IEnumerable<t> is great for working with in-memory collections, but IQueryable<t> allows for a remote data source, like a database or web service.
{
using (SqlDataReader sdr = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), "[GetRoomsByDate]", Checkin, Checkout))
{
if (sdr.HasRows)
{
while (sdr.Read())
{
bool res = false;
for (int i = 0; i < sdr.FieldCount; i++)
{
if (sdr.GetName(i).Equals(RoomNotAvailable, StringComparison.InvariantCultureIgnoreCase))
res = true;
sdr.Close();

}
if (res)
{
yield return new RoomInfo
{
RoomNotAvailable = Convert.ToString(sdr["RoomNotAvailable"])
};
}
else
{
yield return new RoomInfo
{

RoomId = Convert.ToInt32(sdr["RoomId"]),
RoomTitle = Convert.ToString(sdr["RoomTitle"]),
RoomName = Convert.ToString(sdr["RoomName"]),
RoomPrice = Convert.ToInt32(sdr["RoomPrice"]),
RoomDescription = Convert.ToString(sdr["RoomDescription"]),

};
}

}
}
}
}

Jörgen Andersson 21-Nov-14 1:17am

The error message helps a lot.
you're not opening the reader before using it.
Call sdr.open before first use, and don't forget to close it afterwards. sdr.close

/\jmot 21-Nov-14 1:32am

Jörgen Andersson : you got the point.
thank you.