System.InvalidCastException - Specified cast is not valid exception

Asked Apr 02 '18 at 16:33

Active Apr 02 '18 at 16:57

Viewed 329 times

Working with a mysql dbase and c#,
Below is the method I created in database class to check if the row exists.

public int CheckRows(string query)
{
    try
    {
        openConnection();

    }
    catch (MySqlException ex)
    {
        MessageBox.Show(ex.Message);
    }
    cmd = new MySqlCommand(query, con);
    int read = (Int32)cmd.ExecuteScalar();
    return read;
    cmd.Dispose();
    closeConnection();
}

And the button click event,

private void button2_Click(object sender, EventArgs e)
{
    int invoice = Convert.ToInt32(textBox9.Text);
    string qry = "Select 1 from purchase where purchId='" + invoice + "'";
    int dub = db.CheckRows(qry);
}

Which gives the mentioned exception. What am I doing wrong here?

edited Apr 02 '18 at 16:57

Alexei Levenkov

98,904
14
127
179

asked Apr 02 '18 at 16:33

Upeka Fernando

what is the first column type of your table, 1 returns the first column value i think – Ehsan Sajjad Apr 02 '18 at 16:35
1

Can you print out `textBox9.Text` and share the result with us? – Mureinik Apr 02 '18 at 16:35
@EhsanSajjad it's integer. – Upeka Fernando Apr 02 '18 at 16:36
@Mureinik you mean the variable after converting to integer or textBox9.Text as it is? – Upeka Fernando Apr 02 '18 at 16:38
@UpekaFernando the text as is. The conversion to int is probably what causes the exception – Mureinik Apr 02 '18 at 16:38
Won't the query return multiple rows if there are many `purchId` with same `invoice` number. I don't think that the result of this query can be converted to `int`. – Atur Apr 02 '18 at 16:41
Could you please explain why it is necessary to provide SQL injection in your sample? For some reason a lot of people do that when posting sample code even if they know it leads to long unrelated discussion about BobyTables... If you read [MCVE] guidance it tells you to remove all unrelated code/operation - `string query = "Select 1 from purchase where purchId='a123'";` would be perfectly enough in this case. – Alexei Levenkov Apr 02 '18 at 16:42
@atur purchId is the primary key – Upeka Fernando Apr 02 '18 at 16:43
lines are not executed after `return read;` Note: this is not your answer but I want to say it – Adem Catamak Apr 02 '18 at 16:43
@AlexeiLevenkov I need to update the table purchase, if the invoice number input is not already inserted. – Upeka Fernando Apr 02 '18 at 16:45
`Select 1`? Are you sure you don't want `Select Top 1`? If you are expecting a single column result, you should be naming that column as well... – Ron Beyer Apr 02 '18 at 16:45
@AdemCatamak noted and thank you! – Upeka Fernando Apr 02 '18 at 16:45
Or maybe you really want `Select Count(*) from ...` which would be better suited to determining if there is an invoice in the table with that invoice number. – Ron Beyer Apr 02 '18 at 16:46
@RonBeyer tried the latter, but the same exception – Upeka Fernando Apr 02 '18 at 16:47
Please step through your code and tell us what line the exception is being thrown on. Also try running the query in MySql command line directly to see what you are returning. – Ron Beyer Apr 02 '18 at 16:49
purchId is nvarchar in your mysql??? If it is int, your query must be like: `string qry = "Select 1 from purchase where purchId= " + invoice;` – Adem Catamak Apr 02 '18 at 16:50
@RonBeyer command line returns 1 – Upeka Fernando Apr 02 '18 at 16:53
If it throws an error it will still try that query. What line is throwing the error? – paparazzo Apr 02 '18 at 16:58
Exception is thrown at `int read....` – Upeka Fernando Apr 02 '18 at 17:00
At `int read`, Try using Convert.ToInt32() instead of (Int32). https://stackoverflow.com/questions/1608801/what-is-the-difference-between-convert-toint32-and-int – Atur Apr 02 '18 at 17:08
@atur it worked, thanks and thanks everyone! – Upeka Fernando Apr 02 '18 at 17:16

System.InvalidCastException - Specified cast is not valid exception

0 Answers0