Click here to Skip to main content
15,884,472 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:
C#
command.CommandText = "select IFNULL(SL_NO,0) SL_NO, IFNULL(STUD_CODE,'') STUD_CODE, IFNULL(STUD_NAME,'') STUD_NAME from STUDENTREGISTER";

C#
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;
        }
Posted
Updated 18-Jan-13 9:34am
v6

use COALESCE instead

SQL
SELECT COALESCE(field_a, field_b)


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.
 
Share this answer
 
Comments
Murugan.NM 11-Jan-13 14:42pm    
Again I gor the same error Message: FUNCTION <database>.COALESCE does not exist
[no name] 11-Jan-13 14:52pm    
share your complete code bunch, here at my machine both ifnull and coalesce are working fine
Murugan.NM 11-Jan-13 15:35pm    
can u share your method with me
I'm not sure if you can use coalesce with only one parameter. So should the start of the statement be
C#
command.CommandText = "select COALESCE(SL_NO, 0) SL_NO,...



Also there's a potential problem when you concatenate a value directly to the SQL statement. You should rewrite it to use MySqlParameter[^]
 
Share this answer
 
Comments
Murugan.NM 11-Jan-13 15:46pm    
i am using MySqlDataReader
Wendelius 11-Jan-13 16:07pm    
Yes, I can see that, but I don't quite understand your comment. Using the data reader has no effect on how parameters should be used or how COALESCE works???
Murugan.NM 11-Jan-13 16:16pm    
one small request, first i used database MsSql now changed to MySql. Can you suggest which method is good...
Wendelius 11-Jan-13 16:26pm    
If you mean replacing the null values, I'd personally use COALESCE which is similar in several database products since it's part of the SQL standard.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900