CODEWITHSUNDEEP
c#mysqlcastingodbcdatareader
user798080
user798080

Reputation:

MySQL Casting in C#

Okay, so I'm attempting to print out the contents of a table in a comma-separated file.

using (OdbcCommand com = new OdbcCommand("SELECT * FROM pie_data WHERE Pie_ID = ?", con)) {
    com.Parameters.AddWithValue("", Request.Form["pie_id"]);
    com.ExecuteNonQuery();
    using (OdbcDataReader reader = com.ExecuteReader()) {
        string finalstring = "";
        while (reader.Read()) {
            finalstring = reader.GetString(9) + ",";
            for (int i = 0; i <= 8; i = i + 1) {
                finalstring = finalstring + reader.GetString(i) + ",";
            }
        }
    }
    Response.Write(finalstring);
    noredirect = 1;
}

My table layout is:

CREATE TABLE `rent_data` (
`Pies` INT(10) UNSIGNED NOT NULL,
`Name` VARCHAR(85) NOT NULL,
`Email` VARCHAR(85) NOT NULL,
`Pie_Rent` DATE NOT NULL,
`Rent_To` DATE NOT NULL,
`Returned_Date` DATE NULL DEFAULT NULL,
`Place` VARCHAR(100) NOT NULL,
`Purpose` MEDIUMTEXT NOT NULL,
`Comments` MEDIUMTEXT NULL,
`Pie_ID` SMALLINT(5) UNSIGNED ZEROFILL NOT NULL,
INDEX `Pie_ID` (`Equipment_ID`)
)

The error I'm getting is this:

Exception Details: System.InvalidCastException: Unable to cast object of type 'System.Int64' to type 'System.String'.

On the line:

finalstring = finalstring + reader.GetString(i) + ",";

Upvotes: 0

Views: 970

Answers (3)

zmbq
zmbq

Reputation: 39013

As it says here, conversions are done by the ODBC driver, and if they're not supported, GetString doesn't convert to string. Since you have non-string columns, you're facing this problem.

Instead of GetString, use GetValue, and then convert to string using ToString():

finalstring += reader.GetValue(i).ToString() + ",";

Upvotes: 1

Wiktor Zychla
Wiktor Zychla

Reputation: 48240

GetString tries to cast the column value to string. This won't work with non-literal columns and you apparently have them in your table (Pies is of type long, Pie_Rent is a DateTime) etc. You have to use other method of extracting the data like for example:

  finalstring = finalstring + reader[i].ToString() + ",";

Upvotes: 0

Massimiliano Peluso
Massimiliano Peluso

Reputation: 26737

probably you are using GetString() to get an Int64. You should use .GetInt64 instead for that particular value

you should also call IsDBNull to look for null values before calling this method.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader_methods

Upvotes: 0

Related Questions