Specified cast is not valid asp.ent using mysql.

Question

1.50/5 (2 votes)

See more:

I m getting the following error when i execute my code...please suggest..
Error-Specified cast is not valid

C#

long count;
string myScalarQuery = "SHOW TABLES LIKE 'table1';";
MySqlCommand myCommand = new MySqlCommand(myScalarQuery, conn);
conn.Open();
count = (long)myCommand.ExecuteScalar();
conn.Close();
if (count == 0)
  Response.Write("no such table exists");
else
  Response.Write("  "+ count );

Posted 14-Mar-15 23:27pm

Somesh Dhal

Updated 14-Mar-15 23:28pm

Kornfeld Eliyahu Peter

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kornfeld Eliyahu Peter · Answer 1 · 2015-03-14T23:30:00

Solution 1

ExecuteScalar[^] returns the 'first column of the first row' - why do you think it is a long?

[UPDATE AS RESPONSE TO OP COMMENT]
To see only how many records a query returns use ExecuteNonQuery[^] - it has a integer return value, the number of rows.

Posted 14-Mar-15 23:30pm

Kornfeld Eliyahu Peter

Updated 14-Mar-15 23:41pm

v2

Comments

Somesh Dhal 15-Mar-15 5:33am

i wanna see if it results any rows so that i can i can check whether a table with the same name already exists..how to solve this?

Kornfeld Eliyahu Peter 15-Mar-15 5:41am

See updated solution...

Somesh Dhal 15-Mar-15 7:35am

i changed the code to following...

int count;
string myScalarQuery = "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'test' AND table_name = 'login';";

MySqlCommand myCommand = new MySqlCommand(myScalarQuery, conn);
conn.Open();
count = (int)myCommand.ExecuteNonQuery();
conn.Close();
if (count == 0)
Response.Write("no such table exists");
else
Response.Write(" "+ count );
still i i always get the error saying no such table exists even if the table 'login' exists..what to do?

Kornfeld Eliyahu Peter 15-Mar-15 7:42am

Why did you changed you query? You current query using aggregate function and has a totally different behavior!!!
A select count(*) from table will always return exactly 1 row regardless it found something or not, the only different is the value of count(*) - 0 or more!

Somesh Dhal 15-Mar-15 7:49am

int count;
string myScalarQuery = "SHOW TABLES LIKE 'table1';";

MySqlCommand myCommand = new MySqlCommand(myScalarQuery, conn);
conn.Open();
count = (int)myCommand.ExecuteNonQuery();
conn.Close();
if (count == 0)
Response.Write("no such table exists");
else
Response.Write(" "+ count );
even this gives the same result "no such table exists" even if it exists...

Kornfeld Eliyahu Peter 15-Mar-15 7:51am

And there is? notice that once you are looking for 'login' and once for 'table1'!!!

Somesh Dhal 15-Mar-15 7:57am

both are in my database

Kornfeld Eliyahu Peter 15-Mar-15 8:00am

To be honest it makes no sense...
Those queries - each in its own way -are work perfectly...
Please open workbench and run those queries in it...