user552769 user552769 - 2 months ago 11
SQL Question

Specified cast is not valid. SQL Parameter

I keep getting back specified cast is not valid on the

int result = myDataReader.GetInt32(0);
line when running the query using the parameter.
The thing is if I replace @Reg with text 'WM07 OGR' it works fine. However the string reg returns this so why the error?

string reg = RadComboBox1.SelectedValue;

//prepare sql statements
Command = new OleDbCommand(@"SELECT MAX(Fuel.NO_ODOMETER_LAST) AS PrevMiles
FROM (Fuel INNER JOIN Vehicle ON Fuel.TX_VIN = Vehicle.TX_VIN)
WHERE (Vehicle.TX_VEHNUMBER = '@Reg')", conn);
Command.Parameters.AddWithValue("@Reg", OleDbType.WChar);
Command.Parameters["@Reg"].Value = reg;

myDataReader = Command.ExecuteReader();

if (myDataReader.Read())
{
int result = myDataReader.GetInt32(0);
Prev_Mileage.Text = result.ToString();
}

//cleanup objects
myDataReader.Close();
conn.Close();

Answer

The thing is if I replace @Reg with text 'WM07 OGR' it works fine. However the string reg returns this so why the error?

It seems that you get the error if the query returns null because there is no matching TX_VEHNUMBER, then the cast to int fails.

So you have to check if it's null:

int result = 0; // insert default
if(!myDataReader.IsDbNull(0))
    result = myDataReader.GetInt32(0)

Apart from that it doesn't work because your parameter is interpreted as value, you have wrapped it in apostrophes here:

WHERE (Vehicle.TX_VEHNUMBER = '@Reg')

You just have to do this:

WHERE (Vehicle.TX_VEHNUMBER = @Reg)
Comments