select statement

[kanuri1]

hi
here i wants to show the room numbers by using the following code but it is not working.
here iam giving the code plese give the proper coding

Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("guest1").ConnectionString())
   conn.Open()
        Dim da As New SqlCommand("select room_no from guesthouse where room_no not in(select room_no from guesthouse where WHERE date_of_arr <= CONVERT(datetime,'" & arrivaldate.Text & "',101) and date_of_dept >= CONVERT(datetime,'" & arrivaldate.Text & "',101)", conn)
        Dim dr As System.Data.SqlClient.SqlDataReader
        dr = da.ExecuteReader
        If dr.HashRows Then
        dr.NextResult()
While dr.Read()
Response.Write(dr(o).ToString)
End While
Response.Write("available")
      Else
            Response.Write(" No room available! ")
        End If
        conn.Close()

[mith_cool]

try if dr.Read()
rather than using the while statement

[madmital]

If dr.HashRows Then
        dr.NextResult()
While dr.Read()
Response.Write(dr(o).ToString)
End While
Response.Write("available")
      Else
            Response.Write(" No room available! ")
        End If
        conn.Close()

here i wants to show the room numbers by using the following code but it is not working.

That's not really a helpful description. You should tell us HOW it's not working in order to get real help.

Anyway...NextResult() and Read() both advances the datareader to the next record.
So what you're telling the code is:
If dr.HashRows Then -- if there are any rows
dr.NextResult() -- advance dr by one row
While dr.Read() --- while you're able to move ahead one row. advance dr by one row
Response.Write(dr(o).ToString) -- output . If the datareader only contain one row total this line will throw an exception.

Loosing the NextResult() is probably gonna fix you're error:

Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("guest1").ConnectionString())
   conn.Open()
        Dim da As New SqlCommand("select room_no from guesthouse where room_no not in(select room_no from guesthouse where WHERE date_of_arr <= CONVERT(datetime,'" & arrivaldate.Text & "',101) and date_of_dept >= CONVERT(datetime,'" & arrivaldate.Text & "',101)", conn)
Dim dr As System.Data.SqlClient.SqlDataReader
dr = da.ExecuteReader
If dr.HasRows Then
        While dr.Read()
                Response.Write(dr(o).ToString)
        End While
        Response.Write("available")
Else
        Response.Write(" No room available!")
End If
conn.Close()