CODEWITHSUNDEEP
c#asp.net
Miguel
Miguel

Reputation: 63

Unable to cast object of type 'System.Data.SqlClient.SqlDataReader' to type 'System.IConvertible'

I have encountered an error with my code below:

Unable to cast object of type 'System.Data.SqlClient.SqlDataReader' to type 'System.IConvertible'

Why am I encountering this error and how can I fix it?

public DataTable getAllLoanInfoDT()
{ 
    using (SqlConnection conn = new SqlConnection(connString))
    {
        SqlCommand cmd2 = new SqlCommand();
        cmd2.Connection = conn;
        // cmd.CommandType = CommandType.StoredProcedure;
        cmd2.CommandText = "SELECT DISTINCT loanUpdateDate FROM LoanPortfolio WHERE (custID LIKE 'OH00002') AND (loanType LIKE 'Personal Loan')"; 
        cmd2.Parameters.AddWithValue("@custID", "OH00002");
        cmd2.Parameters.AddWithValue("@loanType", "Personal Loan");
        conn.Open();
        SqlDataReader myReader = cmd2.ExecuteReader();
        DateTime loanUpdateDate = Convert.ToDateTime(myReader);
        DateTime currDateTime = DateTime.Now;

        int loanToBeAdded = (((currDateTime.Year - loanUpdateDate.Year) * 12) + currDateTime.Month - loanUpdateDate.Month) * 500;
        if (loanToBeAdded > 0)
        {
            String sql = "UPDATE LoanPortfolio SET loanPaid = loanPaid + " + loanToBeAdded.ToString() + ", loanUpdateDate = " + DateTime.Now.ToString();
            sql += " WHERE (loanType LIKE 'Personal Loan') AND (custID LIKE 'OH00002')";

            //Execute the above query here
        }
        conn.Close();

        using (SqlDataAdapter dAd = new SqlDataAdapter("SELECT * FROM LoanPortfolio where custID like 'OH00002'", conn))
        {
            DataTable dTable = new DataTable();
            dAd.Fill(dTable);
            return dTable;
        }
    }
}

Upvotes: 1

Views: 15445

Answers (2)

samjudson
samjudson

Reputation: 56853

A SqlDataReader is used to read your data - you can't just pass it into Convert.ToDateTime and hope for the best.

First you would call myReader.Read() and check it returns true (i.e. you have a row of data).

Then you would call Convert.ToDateTime(myReader[0]) to get the first field out of the data reader and convert it to a date.

Upvotes: 4

JLRishe
JLRishe

Reputation: 101700

It's exactly what the error says. You can't convert an SqlDataReader to a DateTime. If you want to query for just a single value, you should use:

cmd2.ExecuteScalar();

If you are looking to retrieve a single value, I would suggest using TOP 1 in your query as well:

SELECT TOP 1 DISTINCT loanUpdateDate

Also note that ExecuteScalar() can return DbNull.Value if no value is found, so be sure to do a null check before trying to convert the value to anything.

Upvotes: 2

Related Questions