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

Question

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

You are reading results from `cmd` incorrectly. You do not expect a scalar result. Rather, you expect a rowset. Have a look @ [How can i retrieve a table from stored procedure to a datatable](http://stackoverflow.com/questions/1933855/how-can-i-retrieve-a-table-from-stored-procedure-to-a-datatable). — Serge, Nov 29 '16 at 13:42
So let me understand, the user can choose a room selecting the Room_ID? — Steve, Nov 29 '16 at 13:42
@Steve the user will choose, the quantity of rooms to be reserved, and the system will automatically give them the roomid as to the number of room they want to be reserved — Newbie10, Nov 29 '16 at 13:44
You current code only returns one item because you are using 'cmd.ExecuteScalar()'.. Use 'cmd.Execute() which returns a reader that will get all results. — jdweng, Nov 29 '16 at 13:46

Steve · Accepted Answer · 2016-11-29T14:11:08.607

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);

sorry for asking this, but this will be my first time using 'List rooms = new List();' how would I get the all the value stored on that code and transfer it to my TBL_MyCart? My current code for transfering data on my TBL_MyCart is this 'cs.SaveCart(roomid, roomtype, qty, guest, arrival, departure, price1);' — Newbie10, Nov 29 '16 at 14:05
You need a loop and call that code using rooms[0], rooms[1] etc for each element stored in rooms (replacing you roomid) — Steve, Nov 29 '16 at 14:10

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

1 Answers1