MySQL语句中的未定义参数 [英] Undefined Parameter in MySQL statement

问题描述

大家好，

我有一个程序可以从串行端口收集数据，并将其存储在MySQL数据库中.为了存储数据，我使用如下语句:

Hi all,

I have a program which collects data from the serial port, and stores them in a MySQL database. For storing the data I use a statement as follows:

//SQL Skript zusammenbauen
sql = "SET @artnumsn:=(SELECT sn FROM artnum WHERE dkwnum='" + artikel + "');" +
      "SET @lfdnrsn:=(SELECT MAX(sn) FROM messzähler WHERE artikel=@artnumsn AND lfdnr_int=" + lfdnr_int + ");" +
      "SET @versionn:=(SELECT MAX(versionn) FROM magnetwerte_soll WHERE artikel=@artnumsn);" + 
      "SET @mpsnnord:=(SELECT sn FROM magnetwerte_soll WHERE mp_text='Nord' AND versionn=@versionn);" +
      "SET @mpsnnull:=(SELECT sn FROM magnetwerte_soll WHERE mp_text='0' AND versionn=@versionn);" +
      "SET @mpsnsued:=(SELECT sn FROM magnetwerte_soll WHERE mp_text='Süd' AND versionn=@versionn);" +
      "INSERT INTO magnetwerte_ist (artikel,lfdnr_int,mp,ist,abw,io) VALUES " +
         "(@artnumsn, @lfdnrsn, @mpsnnord, " + istnord + ", " + abwnord + ", " + ionord + ");" +
      "INSERT INTO magnetwerte_ist (artikel,lfdnr_int,mp,ist,abw,io) VALUES " +
         "(@artnumsn, @lfdnrsn, @mpsnnull, " + istnull + ", " + abwnull + ", " + ionull + ");" +
      "INSERT INTO magnetwerte_ist (artikel,lfdnr_int,mp,ist,abw,io) VALUES " +
         "(@artnumsn, @lfdnrsn, @mpsnsued, " + istsüd + ", " + abwsüd + ", " + iosüd + ");";

之后，我

After this I do

MySqlCommand cmd;

cmd = mysqlcon.CreateCommand();
cmd.CommandText = sql;
try
{
    this.OpenConnection();
    cmd.ExecuteNonQuery();
    this.CloseConnection();
}

执行时发生以下错误:必须定义参数@artnumsn".如果我将语句放入MySQL Workbench，则一切正常.为什么在C#中不起作用?

提前谢谢.

问候
Heiko

While executing the following error occurs: "Parameter @artnumsn must be defined". If I put the statement into the MySQL Workbench, everything works fine. Why doesn''t it work in C#?

Thanks in advance.

Regards
Heiko

推荐答案

Heiko

我认为您缺少每个mysql临时变量的声明语句.检查一下
http://dev.mysql.com/doc/refman/5.0/en/clarify-local-variable.html [ ^ ]

所以在
之前

hi Heiko

I think you are missing the declare statements for each mysql temporary variable. check this out
http://dev.mysql.com/doc/refman/5.0/en/declare-local-variable.html[^]

So before

SET @artnumsn:=(SELECT sn FROM artnum WHERE dkwnum=''" + artikel + "'');

您应该放置

you should be placing

DECLARE @artnumsn INT;

对于每个变量，依此类推.我建议您为什么不使用存储过程.

问候
帕万

如果满足您的要求，请将答案标记为解决方案

and so on for each variable. I suggest why don''t you use stored procedure.

Regards
Pawan

please mark the answer as solution if it fulfills your critirea

Pawan，

谢谢回复.我用存储过程解决了它.

问候
平子

Hi Pawan,

thanks for the reply. I solved it with a stored procedure.

Regards
Heiko

这篇关于MySQL语句中的未定义参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！