0

I have written a webservice to access my SQL database.

[WebMethod]

    public void getRoomByBuildingID(int buildingID)
    {
        string cs = ConfigurationManager.ConnectionStrings["vetDatabase_Wizard"].ConnectionString;
        List<Room> rooms = new List<Room>();
        using (SqlConnection con = new SqlConnection(cs))
        {
            SqlCommand cmd = new SqlCommand("spGetRoomsByBuildingID", con);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter param = new SqlParameter()
            {
                ParameterName = "@buildingID",
                Value = buildingID
            };

            cmd.Parameters.Add(param);

            con.Open();
            SqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                Room room = new Room();
                room.ID = Convert.ToInt32(rdr["ID"]);
                room.Name = rdr["Room"].ToString();
                room.buildingID = Convert.ToInt32(rdr["Building"]);
                rooms.Add(room);
            }
        }
        JavaScriptSerializer js = new JavaScriptSerializer();

        //Serialize list object into a JSON array and write in into the response stream
        Context.Response.Write(js.Serialize(rooms));
    }

I keep getting an indexOutOfRangeException at 

room.building = Convert.ToInt32(rdr["Building"]);

I've been looking at my sql table, and the column names and types are correct (i.e. ID, Room, Building). I am totally clueless at this point and would greatly appreciate the community's feedback. Thank you!

EDIT:

ALTER PROCEDURE spGetRoomsByBuildingID 
@buildingID int
AS
BEGIN
    SELECT ID, Room, Building FROM tblRooms
    WHERE Building = @buildingID
END
EXEC spGetRoomsByBuildingID
Johnathan
  • 1,877
  • 4
  • 23
  • 29
  • Pls post the content of your `spGetRoomsByBuildingID` stored procedure – BNK Jun 01 '16 at 03:42
  • You should read http://stackoverflow.com/questions/7711807/system-indexoutofrangeexception-on-sqldatareader-value-using-c-sharp – BNK Jun 01 '16 at 03:50
  • @BNK Thank you for your answer: I have posted my stored procedure. The interesting thing is that when I run this stored procedure in SQL management studio, I don't get the building number as well. Thanks! – Johnathan Jun 01 '16 at 04:09
  • Ok, see the link I posted above, you should check if some values are NULL or not – BNK Jun 01 '16 at 04:10
  • Maybe you do not have select permissions for that column in SQL? – Denys Wessels Jun 01 '16 at 04:55

0 Answers0