Unable to insert Number into a text box

Question

I have a web application which uses Oracle as it's DB. I have a query which returns number to the front end which I need to insert into a text box.

When the number returned is a whole number, the textbox gets populated but when the number returned is a decimal, it throws an error "Specified cast is not valid."

Here is the code.

string isbn=Request.QueryString["code"];
OracleConnection con = new OracleConnection();
con.ConnectionString = connectionString;

con.Open();

string sql = "SELECT distinct B.TITLE,B.BOOK_CATEGORY,B.ISBN,A.AUTHOR_NAME,P.PUBLICATION_HOUSE_NAME,B.YEAR_OF_PUBLICATION,(SELECT AVG(AVERAGE_RATING) FROM RATING_TABLE GROUP BY ISBN HAVING ISBN = '" + isbn + "') AS AVERAGE from BOOK_TABLE B JOIN AUTHOR_TABLE A ON B.AUTHOR_ID = A.AUTHOR_ID JOIN PUBLISHER_TABLE P ON B.PUBLISHER_ID = P.PUBLISHER_ID JOIN RATING_TABLE R ON B.ISBN = R.ISBN WHERE B.ISBN = '" + isbn + "'";
OracleCommand cmd = new OracleCommand(sql, con);

cmd.CommandType = CommandType.Text;
OracleDataReader dr = cmd.ExecuteReader();

while (dr.Read())
{
    txtBook.Text = Convert.ToString(dr["TITLE"]);
    txtGenre.Text = Convert.ToString(dr["BOOK_CATEGORY"]);
    txtISBN.Text = Convert.ToString(dr["ISBN"]);
    txtAuthor.Text = Convert.ToString(dr["AUTHOR_NAME"]);
    txtPublisher.Text = Convert.ToString(dr["PUBLICATION_HOUSE_NAME"]);
    txtyop.Text = Convert.ToString(dr["YEAR_OF_PUBLICATION"]);
    txtRating.Text =dr["AVERAGE"].ToString();
}

dr.Close();

Am I missing something?

Answer 1

I found out the issue for the problem. My Average which was being calculated, in some cases would end up being a non recurring value and would not end. Hence there used to be an error while casting.

I solved it using TO_CHAR function in the query.

 string sql = "SELECT distinct B.TITLE,B.BOOK_CATEGORY,B.ISBN,A.AUTHOR_NAME,P.PUBLICATION_HOUSE_NAME,B.YEAR_OF_PUBLICATION,(SELECT TO_CHAR((AVG(AVERAGE_RATING)),'99.99') FROM RATING_TABLE GROUP BY ISBN HAVING ISBN = '" + isbn + "') AS AVERAGE from BOOK_TABLE B JOIN AUTHOR_TABLE A ON B.AUTHOR_ID = A.AUTHOR_ID JOIN PUBLISHER_TABLE P ON B.PUBLISHER_ID = P.PUBLISHER_ID JOIN RATING_TABLE R ON B.ISBN = R.ISBN WHERE B.ISBN = '" + isbn + "'";