CODEWITHSUNDEEP
c#sqlasp.net
Newbie10
Newbie10

Reputation: 99

Get Cell Value From Different Row (Number of Row depends on user input)

I have this hotel reservation system, There is a dropdownlist which the user chooses their number of rooms on an specific room type to be reserved. Here is my Table_Room where I will get the Room ID to be inserted on Table_MyCart

Room_ID | Room_Type | Username | Arrival_Date | Departure_Date | Status
--------|-----------|----------|--------------|----------------|-------
CI-1    |Carneros   |myuser    |date          |date            |Available
CI-2    |Carneros   |myuser    |date          |date            |Available

My current code only gets the first Room_ID which is (CI-1) but what if the user used the dropdownlist and chose 2 rooms for the current type? How would I get the Room_ID (CI-2)? Here is my current code:

roomtype = "Carneros Inn";
SqlCommand cmd = new SqlCommand("FindRoom", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ROOM_TYPE", SqlDbType.VarChar).Value = roomtype;
conn.Open();
cmd.ExecuteNonQuery();
roomid = cmd.ExecuteScalar().ToString();
conn.Close();

My Sql Code for the FindRoom StoredProcedure is this:

CREATE PROCEDURE [dbo].[FindRoom]
@ROOM_TYPE VARCHAR (20)
AS
SELECT ROOM_ID FROM TBL_Room 
WHERE ROOM_TYPE = @ROOM_TYPE AND STATUS = 'AVAILABLE';

Any help would be appreciated

Upvotes: 1

Views: 98

Answers (1)

Steve
Steve

Reputation: 216273

Your stored procedure already returns all the rows that have the status Available for the RoomType choosen.
Instead your C# code uses ExecuteScalar that can only return the first column of the first row returned by your stored procedure.

If you want to read all the room_id available one approach could be to use ExecuteReader and loop over the returned rows 

int room_qty = 2; // <-- this comes from user input
List<string> rooms = new List<string>();
roomtype = "Carneros Inn";
SqlCommand cmd = new SqlCommand("FindRoom", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ROOM_TYPE", SqlDbType.VarChar).Value = roomtype;
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while(reader.Read())
{
    rooms.Add(reader["room_id"].ToString());
    Console.WriteLine("Selected room ID = " + roomID);

    // We have reached the requested quantity????
    if(rooms.Count == room_qty)
       break;
}
conn.Close();

if(rooms.Count < room_qty)
    Console.WriteLine("Not enough room available!");

Now all the room_id requested are stored in a List<string> that you can use for further processing.

For example (based on your comment below)

foreach(string roomid in rooms)
   cs.SaveCart(roomid, roomtype, qty, guest, arrival, departure, price1);

Upvotes: 2

Related Questions