Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# MySQL
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;
        }
Posted 11-Jan-13 9:19am
Edited 18-Jan-13 10:34am
v6
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

use COALESCE instead
 
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.
  Permalink  
Comments
NMMurugan1980 at 11-Jan-13 14:42pm
   
Again I gor the same error Message: FUNCTION .COALESCE does not exist
NMMurugan1980 at 11-Jan-13 15:35pm
   
can u share your method with me
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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

Also there's a potential problem when you concatenate a value directly to the SQL statement. You should rewrite it to use MySqlParameter[^]
  Permalink  
Comments
NMMurugan1980 at 11-Jan-13 15:46pm
   
i am using MySqlDataReader
Mika Wendelius at 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???
NMMurugan1980 at 11-Jan-13 16:16pm
   
one small request, first i used database MsSql now changed to MySql. Can you suggest which method is good...
Mika Wendelius at 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)



Advertise | Privacy | Mobile
Web04 | 2.8.1411022.1 | Last Updated 18 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100