Member Avatar for 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()
  • 3 Contributors
  • 2 Replies
  • 94 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by madmital

Dani AI

Generated

The original post from shows several separate problems that together prevent any useful result: a malformed SQL string (extra WHERE, mismatched parentheses), building SQL by concatenating a date string (culture/format and SQL injection risk), VB typos (HashRows vs HasRows, dr(o) uses the letter o instead of zero), and misuse of NextResult() which advances to a new result set rather than the next row. 's suggestion to use If dr.Read() only fits the single-row case; is correct that NextResult() should be removed if only one result set is expected.

A safer, clearer approach:

  • Parse the arrival date server-side with DateTime.TryParse to avoid format surprises.
  • Use a parameterized query (no string concatenation) and pass a DateTime parameter.
  • Prefer a master rooms list and exclude rooms that have a booking covering the arrival date (example uses NOT EXISTS / BETWEEN).
  • Use Using blocks so connections and readers are disposed automatically.

Example (adapt table names to the schema):

' parse arrival
Dim arrival As DateTime
If Not DateTime.TryParse(arrivaldate.Text, arrival) Then
    Response.Write("Invalid date")
    Exit Sub
End If

' parameterized command, open/close handled by Using blocks...
' (see code comments above for placement and schema adjustments)

Use dr.HasRows to test for results and While dr.Read() to iterate rows (or If dr.Read() for a single-row result). Avoid NextResult() unless the command returns multiple result sets. Check for DBNull before converting column values. For reference on reader behavior and parameters consult the Microsoft docs: SqlDataReader.Read and SqlParameter.

Also test edge cases (arrival at exactly a departure date—decide if that should count as available) and log exceptions so any remaining runtime errors show where to fix them.

Recommended Answers

All 2 Replies

Member Avatar for mith_cool

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

Member Avatar for 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()
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.