Method not returning a value

This method is not returning anything. The column QtyOnHand is the second column in the mySQL table, and has data.

Any suggestions?
public static int GetQtyOnHand(string aMed)
		{
 
			MySqlCommand cmd = new MySqlCommand();
			con = new MySqlConnection("server=localhost;database=mydb;uid=root;pwd=;pooling=false;");
			cmd.CommandText = "SELECT QtyOnHand FROM medstable WHERE AnesthMedName = '" + aMed + "'" ;
			cmd.Connection = con;
			con.Open();
			int qtyOnHand = Convert.ToInt32(cmd.ExecuteScalar());
 
			return qtyOnHand;
			
 
		}

Open in new window

wjstarckAsked:
Who is Participating?
 
pr_rConnect With a Mentor Commented:
Surely thte datatype of db matters
0
 
gdupadhyayCommented:
use int.Parse(cmd.ExecuteScalar());
0
 
wjstarckAuthor Commented:
thanks -

that generates the following errors in the IDE:

Error 9:              The best overloaded method match for 'int.Parse(string)' has some invalid arguments      Error 10:          Argument '1': cannot convert from 'object' to 'string'      

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Snarf0001Commented:
If you step through the code, is it returning "null" or "DBNull.Value"?

If null, I would guess it's just not finding a matching record (WHERE AnesthMedName = '" + aMed + "'").

If DBNull.Value, it found a matching record, but the QtyOnHand field was null in the database.
0
 
wjstarckAuthor Commented:
Well, my guess was null, but there doesn't seem to be anything wrong with the SELECT statement, and the database has values in it...
0
 
Snarf0001Commented:
Can you step through the code?  If so, change it to the following:

MySqlCommand cmd = new MySqlCommand();
con = new MySqlConnection("server=localhost;database=mydb;uid=root;pwd=;pooling=false;");
cmd.CommandText = "SELECT QtyOnHand FROM medstable WHERE AnesthMedName = '" + aMed + "'";
cmd.Connection = con;
con.Open();

object result = cmd.ExecuteScalar();
int qtyOnHand = Convert.ToInt32(result);

return qtyOnHand;

and set a breakpoint after the ExecuteScalar().
THat way you can at least see what is coming back from the db without throwing an error.
0
 
wjstarckAuthor Commented:
The code change gives me the same result.

A breakpoint there is  indeed returning null
0
 
pr_rCommented:
If you excutesclar is returning null means your query is wrong. Try giving some other test query and see.
0
 
Tony McCreathTechnical SEO ConsultantCommented:
Returning null implies that it is not finding a matching row.

Are you sure the aMed exactly matches an AnesthMedName?

One gotcha is that your aMed may contain characters that are not encoded as they should be for mySql
0
 
wjstarckAuthor Commented:
Thanks for all the suggestions.

If I put a breakpoint on the cmd.CommandText line, the query string shown by VS is "SELECT QtyOnHand FROM medstable where AnesthMedName = 'a'" (using a in the db as a med name)

The program I am working on has an area where one can query the mySQL db by entering query strings. If I enter that exact query string it returns results as expected.

Is it possible the connection to the mySQL db is not working properly? How would I test this
0
 
pr_rCommented:
Just after con.open check
con.state to see whther your connection is alright. Now try execute reader instead of executesclaar and see whther the problem is wiht scalar function?
0
 
pr_rCommented:
Your mysql doucmentation will say whther it supports executescalar. Some versions it was not supportin
Any how executereader will work.
try....also tnhis
long qtyOnHand = (long) cmd.ExecuteScalar();  
0
 
wjstarckAuthor Commented:
OK, tested that and connection is OK.

What is suggested syntax for ExecuteReader on that last line?
0
 
pr_rCommented:
Execute sclar will return only int64. So ideally  this stement should work

long qtyOnHand = (long) cmd.ExecuteScalar();  
0
 
Tony McCreathTechnical SEO ConsultantCommented:
If there is a connection problem or syntax problem you should have got an exception and never reached the line you have a break point on.

As an experiment, remove the where clause so all rows are returned. The ExecuteScalar should pick up the data for the first row returned. What this returns should give us another clue. e.g.

"SELECT QtyOnHand FROM medstable"

Another experiment is to hard code the return value:

"SELECT 999 FROM medstable WHERE AnesthMedName = '" + aMed + "'"

does this return 999?


0
 
Tony McCreathTechnical SEO ConsultantCommented:
ExecuteScalar returns any object.

If there is data to return it will return the .Net type that relates to the type in the database. probably Int32 in this case.

If a row is found but the specified field is set to NULL then System.DBNull.Value is returned

If no row is found then null is returned
0
 
pr_rCommented:
Hi wjstarck:
Did you try that Long systax given by me?
0
 
pr_rCommented:
Mysqlreader rea= cmd.executereader("urselct");

if (rea.HasRows)
{

//reading from datareader
rea.Read();
Int64yourdata= Convert.ToInt64(rea[0]);
}
 
0
 
wjstarckAuthor Commented:
pr_r:

Sorry, somehow my response got clipped off. Trying that long syntax throws an exception "invalid cast type"
0
 
pr_rCommented:
try Response.Write(typeof cmd.ExecuteScalar());
you will get the exact type whihc it returns.
Now try to cast it to the exact .Net type
0
 
pr_rCommented:
Where did you get the exception?
0
 
wjstarckAuthor Commented:
BTW, if I recode it like so:

            string QtyOnHand = Convert.ToString(cmd.ExecuteScalar());
                  int qtyOnHand = Convert.ToInt32(QtyOnHand);

QtyOnHand shows '999' for a value when I hover over it but qtyOnHand shows '0'

That's for the mySQL string where I hard code QtyOnHand as 999

QtyOnHand is saved as type char(32) in the db, if that matters....
0
 
pr_rCommented:
999 is the value you were expecting???
refer this link also http://forums.asp.net/p/1337375/2700267.aspx
0
 
Tony McCreathConnect With a Mentor Technical SEO ConsultantCommented:
999 should convert to an int no probs.

A common mistake I do is I forget that the line I break on has not been executed yet. If your break is on the int line then it will still be zero!

If the 999 worked and you still had the where clause, I would suspect your problem is in the field you are trying to read.

0
 
wjstarckAuthor Commented:
OK, making progress. I changed the field type to int in the db and now values are returning correctly. Thanks guys.
0
 
wjstarckAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.