Fatal error during command execution in C#

Question

0.00/5 (No votes)

See more:

I have an error while saving data to my mysql database using Stored Procedure using a function below

IT shows the Error while running "Fatal Error during Command Execution"..

Please help

What I have tried:

public int Save(string sqlQuery, CommandType cmdtype, ArrayList arrIN)
        {
            int recAffected = 0;
            //SqlConnection con = new SqlConnection(connectionString);
            MySqlCommand sqlCmd = CreateCommand();
            try
            {
                sqlCmd.CommandText = sqlQuery;
                sqlCmd.CommandType = cmdtype;
                MySqlCommandBuilder.DeriveParameters(sqlCmd);
                for (int i = 0; i < arrIN.Count; i++)
                {
                    sqlCmd.Parameters[i].Value = arrIN[i];
                }
                recAffected = sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception("Exception occured @ save " + ex.Message);
            }
            finally
            {
                sqlCmd.Dispose();
            }
            return recAffected;
        }

The Stored Procedure is as

CREATE DEFINER = 'root'@'localhost'
PROCEDURE escort.sp_escortreqdets_ins(
  IN intRqstN int(10), 
  IN dtofEscor DATE, 
  IN tmofEscor TIME, 
  IN varCour VARCHAR(50), 
  IN varCourtPlac VARCHAR(50), 
  IN intCountPrsnr int(5), 
  IN varTyp VARCHAR(30), 
  IN intStron INT(2), 
  IN varRmk VARCHAR(255), 
  IN varStatu VARCHAR(20), 
  IN yrYea YEAR, 
  IN intUse int(5), 
  IN dtofRe DATE)
BEGIN
  IF EXISTS(select intRqstN from tb_escortreq_dets where intRqstNo=intRqstN and yrYear=yrYea) THEN
              BEGIN
                UPDATE escort.tb_escortreq_dets SET
                     intRqstNo=intRqstN
                     ,dtofEscort=dtofEscor
                     ,tmofEscort=tmofEscor
                     ,varCourt=varCour
                     ,varCourtPlace=varCourtPlac
                     ,intCountPrsnrs=intCountPrsnr
                     ,varType=varTyp
                     ,varStatus=varStatu
                     ,intStrong=intStron
                     ,varRmks=varRmk
                     ,varStatus=varStatu
                     ,yrYear=yrYea
                     ,intUser=intUse
                     ,dtofReg=dtofReg;
                END;
              ELSE
                BEGIN
                    INSERT INTO escort.tb_escortreq_dets
                    (
                      intRqstNo
                     ,dtofEscort
                     ,tmofEscort
                     ,varCourt
                     ,varCourtPlace
                     ,intCountPrsnrs
                     ,varType
                     ,intStrong
                     ,varRmks
                     ,varStatus
                     ,yrYear
                     ,intUser
                     ,dtofReg
                    )
                    VALUES
                    (
                     intRqstN
                     ,dtofEscor
                     ,tmofEscor
                     ,varCour
                     ,varCourtPlac
                     ,intCountPrsnr
                     ,varTyp
                     ,intStron
                     ,varRmk
                     ,varStatu
                     ,yrYea
                     ,intUse
                     ,dtofRe
                    );
                    END;
  END IF;
END

Posted 21-Mar-18 2:13am

Member 11071741

Updated 21-Mar-18 2:40am

Add a Solution

Comments

Maciej Los 21-Mar-18 8:21am

Have you tried to debug your code to find out that number of parameters and their names match to SQL statement?

2 solutions

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)

Jochen Arndt · Answer 1 · 2018-03-21T02:33:00

Solution 1

Ensure that sqlQuery is the name of your stored procedure and that cmdtype is StoredProcedure.

Ensure also that the number of array items corresponds to the number of procedure parameters, that they are provided in the correct order, and that they are in correct format.

If the error still exists, check your stored procedure. A possible reason for the error might be in this line:

SQL

,varRmks=varRmk

where the field name differs from the parameter name.

As last resort you might use a "normal" command to update and when that works copy the command to the stored procedure. The advantage of doing so is that you get a more detailed error message indicating also where in the command string an error occured.

Posted 21-Mar-18 2:33am

Jochen Arndt

Comments

Member 11071741 21-Mar-18 9:22am

Thanks for your valuable comment.
The stored procedure is working fine in mysql query window but not in my application.

Jochen Arndt 21-Mar-18 9:44am

Then the most probable reason are invalid parameters.
I suggest to check them by printing out or logging.

Checking for the correct number of parameters can be done by comparing sqlCmd.Parameters.Count with arrIn.Count.

Member 11071741 24-Mar-18 3:22am

Thank you.
I found the reason.
The problem is in the line
ArIn.Add(Convert.ToDateTime(mtTime.Text).ToString("HH:mm:ss"))
In mysql stored procedure this field is in the TIME format.
While avoiding this field its working.
please help

Jochen Arndt 24-Mar-18 4:39am

The TIME field uses that format. So it should work. To be sure you should print out the string.

#realJSOP · Answer 2 · 2018-03-21T02:41:00

Try this:

C#

public int Save(string sqlQuery, CommandType cmdtype, ArrayList arrIN)
{
    int recAffected = 0;
    try
    {
        using (SqlConnection con = new SqlConnection(connectionString))
        {
            using (MySqlCommand sqlCmd = CreateCommand())
            {
                sqlCmd.CommandText = sqlQuery;
                sqlCmd.CommandType = cmdtype;
                MySqlCommandBuilder.DeriveParameters(sqlCmd);
                for (int i = 0; i < arrIN.Count; i++)
                {
                    sqlCmd.Parameters[i].Value = arrIN[i];
                }
                recAffected = sqlCmd.ExecuteNonQuery();
            }
        }
    }
    catch (Exception ex)
    { // put a breakpoint on this bracket and examine <code>ex</code>. It will tell you what 
      // went wrong. You may have to look at the InnerException to find out the root cause 
      // of the problem.
        throw new Exception(string.Format("Exception occured @ save {0}", ex.Message), ex);
    }
    return recAffected;
}

Using the using statement automatically handles all the cleanup required by disposable objects, and requires less code to boot.

Lastly, learn how to use the debugger. Why? Because you probably spend more time debugging code than writing it. That's nothing to be ashamed of, BTW.