C＃中命令执行期间的致命错误 [英] Fatal error during command execution in C#

问题描述

使用以下功能使用存储过程将数据保存到我的mysql数据库时出错







IT在运行命令执行期间的致命错误时显示错误..



请帮助



我尝试过：

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;
        }

存储过程如下< br $>

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

推荐答案

确保 sqlQuery 是您的名字存储过程和 cmdtype 是 StoredProcedure 。



还要确保数组项的数量对应于过程参数的数量，它们以正确的顺序提供，并且它们的格式正确。



如果错误仍然存​​在，请检查存储过程。错误的可能原因可能是这一行：

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:

,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.

试试这个：

Try this:

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;
}

使用使用语句自动处理所有清理工作一次性对象需要，并且需要更少的代码来启动。



最后，学习如何使用调试器。为什么？因为您可能花费更多时间来调试代码而不是编写代码。这没什么可羞耻的，BTW。

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.

这篇关于C＃中命令执行期间的致命错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！