Problem Update or insert mySQL from Visual C#

Question

0.00/5 (No votes)

See more:

Hai. I'm having problem in Update or insert into mySQL from Visual C#. The situation is that user written inside specific row,column of datagridview and I'm using cell end edit event to insert the data inside database. Here is the code of insert and update.

C#

class UpdateMetabolite: UpdateDatabase
    {
        public UpdateMetabolite(frmMain mainClass)
            : base(mainClass)
        {
            int rowIndex = mainClass.dbMetName.CurrentCell.RowIndex;
            try
            {
                string metNo = mainClass.dbMetName.Rows[rowIndex].Cells[0].Value.ToString();
                string metName = mainClass.dbMetName.Rows[rowIndex].Cells[1].Value.ToString();

                conn.Open();
                if (!string.IsNullOrEmpty(mainClass.dbMetName.Rows[rowIndex].Cells[1].Value.ToString()))
                {
                    command.CommandText = "SELECT COUNT(*) FROM " + database +
                                          ".Metabolites " +
                                          "WHERE MetaboliteID = " +
                                           "'" + metNo + "'";
                    command.ExecuteNonQuery();
                    decimal count = (decimal)command.ExecuteScalar(); //the problem occur here
                    if (count > 0)
                    {
                        command.CommandText = "UPDATE " + database +
                                              ".Metabolites " +
                                              "SET Metabolite_Name = " +
                                              "'" + metName + "'" +
                                              "WHERE MetaboliteID = " +
                                              "'" + metNo + "'";
                        command.ExecuteNonQuery();
                    }
                    else
                    {
                        command.CommandText = "INSERT INTO " + database +
                                              ".Metabolites " +
                                              "(MetaboliteID, Metabolite_Name)" +
                                              "VALUES " +
                                              "('" + metNo + "','" + metName + "');";
                        command.ExecuteNonQuery();
                    }
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
    }

I want to capture the count before enter if else statement. but the error stated specific cast is not valid. If I declare int count = (int)command.ExecuteNonQuery() before enter if else statement, it returns -1 value no matter what.

Posted 23-Jun-14 2:34am

arave0521

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)

_Asif_ · Answer 1 · 2014-06-23T03:17:00

Solution 1

Why you are twice executing this query? In addition problem seems that your query returning NULL causing exception. Try this

C#

command.CommandText = "SELECT COUNT(*) FROM " + database +
                                          ".Metabolites " +
                                          "WHERE MetaboliteID = " +
                                           "'" + metNo + "'";
  //command.ExecuteNonQuery();
  object tCount = command.ExecuteScalar(); //the problem occur here</pre>
  int count = 0;
  if (tCount != null)
      count = (int)tCount;

Posted 23-Jun-14 3:17am

_Asif_

Comments

arave0521 23-Jun-14 10:23am

the error still the same. but i tried figure it out. and it works fine. but not sure the algorithm can be considered proper or not. here is my code

command.CommandText = "SELECT COUNT(*) FROM " + database +
".Metabolites " +
"WHERE MetaboliteID = " +
"'" + metNo + "'";
command.ExecuteNonQuery();
int count = int.Parse(command.ExecuteScalar().ToString());

if (count > 0)
{

}
else
{

}