[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 380
  • Last Modified:

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

0
wjstarck
Asked:
wjstarck
  • 10
  • 9
  • 4
  • +2
2 Solutions
 
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
 
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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
 
pr_rCommented:
Surely thte datatype of db matters
0
 
Tony McCreathTechnical 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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 10
  • 9
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now