Retrieve data from table and give to grid

Question

I'm trying to create a grid that contains all the information of a a certain ticket,that has alot of status(Rows in the table),i need to put in a grid,this is the methods i'm using:

Call the method to populate the grid:

 private void AssemblyGrid()
        {
            //Set Grid
            gridTStatus.DataSource = null;
            gridTStatus.DataBind();

            User loggedUser = (User)HttpContext.Current.Session[SESSIONNAME.LOGGEDUSER];
            // Checa se o usuario tem permissão para utilizar essa funcionalidade
            if ((loggedUser.Login.ToUpper() != ConfigurationManager.AppSettings[APPSETTINGS.SUPPORTUSERS].ToUpper()))
            {
                Response.Redirect("AccessDenied.aspx");

            }
            Clic objClic = GetClic();
            Status returnStatus = new Status();
            List<StatusClicComplete> lstClic = new List<StatusClicComplete>();
            lstClic = ClicManager.SelectStatusClic(objClic,out returnStatus);




            //Set Grid
            gridTStatus.DataSource = lstClic;
            gridTStatus.DataBind();
        }

The method the retrieve the information from the datebase:

public List<StatusClicComplete> SelectStatusClicDB(Clic objClic , out Status returnStatus)
        {


            const string strStoredProcedure = "spSearchClicStatus";

            List<StatusClicComplete> complete = new List<StatusClicComplete>();

            try
            {


                Database database = DatabaseFactory.CreateDatabase(DATABASESETTINGS.CLICDB);
                using (DbCommand dbCommand = database.GetStoredProcCommand(strStoredProcedure))
                {
                    database.AddInParameter(dbCommand, "@iClic", DbType.Int32, objClic.ID);

                    using (IDataReader dataReader = database.ExecuteReader(dbCommand))
                    {
                        while (dataReader.Read())
                        {
                            complete.Add(new StatusClicComplete()
                                             {
                                                 iClic = dataReader.GetInt32(Convert.ToInt32("iClic")),
                                                 iStatus = dataReader.GetInt32(Convert.ToInt32("iStatus")),
                                                 dtDateCreated = dataReader.GetDateTime(Convert.ToInt32("dtDateCreated")),
                                                 iEDV = dataReader.GetInt32(Convert.ToInt32("iEDV")),
                                                 sComments = dataReader.GetString(Convert.ToInt32("sComments"))

                                             }
                                );

                        }
                        dataReader.Close();
                        returnStatus = StatusBuilder.BuildStatus("Success", string.Format("{0} - {1}", MethodBase.GetCurrentMethod().Name), true);

                    }
                }
            }
            catch(Exception exception)
            {

                returnStatus = StatusBuilder.BuildStatus("Error", string.Format("{0} - {1}", MethodBase.GetCurrentMethod().Name, exception.Message), false);

            }
            //TODO NAV8CA   - Escrever tratativa de objeto nulo
            return complete;




        }

The StatusClicClass ,that is the list type

namespace RB.LA.TER.CLIC.TRANSPORT
{
    [Serializable]
    public class StatusClicComplete
    {
        public int iClic { get; set; }
        public int iStatus { get; set; }
        public DateTime dtDateCreated { get; set; }
        public int iEDV { get; set; }
        public string sComments { get; set; }
    }
}

And the Procedure:

PROCEDURE [dbo].[spSearchClicStatus]


@iClic int = NULL

AS

SELECT
        dtDateCreated, iEDV, sComments, sDescription, SC.iStatus
    FROM
        T_STATUS_CLIC SC INNER JOIN T_STATUS S
            ON SC.iSTATUS = S.iSTATUS
    WHERE
        SC.iClic = @iClic
    ORDER BY 
        dtDateCreated



GO

I'm trying to run this code,but i'm getting a error (Input string was not in a correct format.) when it enter the complete while,someone know what i'm doing wrong?

Answer 1

The line dataReader.GetInt32(Convert.ToInt32("iStatus")) is wrong. It should be either

dataReader.GetInt32(4)

or

dataReader.GetInt32(dataReader.GetOrdinal("iStatus"));

In the first case, ordinal is directly specified. In the second case we calculate the ordinal using the column name. Also, you must ideally be checking for IsDBNull before using this method, otherwise you will get an InvalidCastException. So a more optimised method will be,

iClic = objClic.ID, // we already have that
iStatus = dataReader.IsDBNull(dataReader.GetOrdinal("iStatus")) ? dataReader.GetInt32(dataReader.GetOrdinal("iStatus")) : 0

P.S: Ordinal position is the position of the columns in the SELECT clause. Its a zero based index. In your select query iClic has 5^th position, hence the ordinal position is given as 4.