使用MySql在C＃中出现IFNULL错误 [英] IFNULL error in C# with MySql

问题描述

我在MySQL和C＃中使用以下查询，如果我在MySQL中运行此查询，它列出了所有学生的详细信息。

但是，当我在C＃中运行此查询时，它会抛出错误

I am using the following Query in MySQL and C#, if I run this Query in MySQL it listed all students details.
But, when I run this Query in C# it throws an error

"Message: FUNCTION <databasename>.IFNULL does not exist"

我试过的查询：

Query which I tried:

command.CommandText = "select IFNULL(SL_NO,0) SL_NO, IFNULL(STUD_CODE,'') STUD_CODE, IFNULL(STUD_NAME,'') STUD_NAME from STUDENTREGISTER";

public List GetStudentCodeInGrid(string StudCode)
        {
            List studDetailsList = new List();
            MySqlConnection connection = new MySqlConnection(connectionString);
            connection.Open();

            MySqlCommand command = new MySqlCommand();
            command.CommandText = "select COALESCE(SL_NO) SL_NO, COALESCE(STUD_CODE,'')STUD_CODE, COALESCE(STUD_NAME,'') STUD_NAME from STUD_CODE WHERE STUDENTREGISTER LIKE '" + studCode + "%' ORDER BY STUD_CODE";
            command.Connection = connection;

            MySqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                StudDetails studDetails = new StudDetails();

                studDetails.SNO = Convert.ToInt32(reader["SL_NO"]);
                studDetails.StudCode = reader["STUD_CODE"].ToString();
                studDetails.StudName = reader["STUD_NAME"].ToString();

                studDetailsList.Add(studDetails);
            }

            return studDetailsList;
        }

推荐答案

使用COALESCE代替

use COALESCE instead

SELECT COALESCE(field_a, field_b)

COALESCE是ANSI标准函数，它从指定的列列表中返回第一个非空值，从左到右处理列。因此，在示例中，如果field_a为null，则将显示field_b值。但是，如果指定的列中没有非空值，则此函数将返回NULL。

COALESCE is an ANSI standard function that returns the first non-null value from the list of columns specified, processing the columns from left to right. So in the example, if field_a is null, field_b value will be displayed. However, this function will return NULL if there is no non-null value from the columns specified.

我不确定是否可以仅使用一个参数进行合并。那么声明的开头应该是

I''m not sure if you can use coalesce with only one parameter. So should the start of the statement be

command.CommandText = "select COALESCE(SL_NO, 0) SL_NO,...

当你将一个值直接连接到SQL语句时，还有一个潜在的问题。你应该重写它以使用 MySqlParameter [ ^ ]

这篇关于使用MySql在C＃中出现IFNULL错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！