CODEWITHSUNDEEP
c#replacedatagridviewnullcell
Shiva Debrown
Shiva Debrown

Reputation: 177

How to set some text to datagridview cell when its cell contains 0 value in c#?

I have a datagridview and its data from sql database.

My datagridview some cells filled with 0(zero) value.
I want to set a NULL text at where the 0 value is filled in the datagridview.

enter image description here

//I'm using this code but not working
 private void dataGridView4_DefaultValuesNeeded(object sender, DataGridViewRowEventArgs e)
    {
        for (int i = 0; i < dataGridView4.Rows.Count; i++)
        {
            for (int j = 7; j < dataGridView4.Rows[i].Cells.Count; j++)
            {
                if (Convert.ToInt32(dataGridView4.Rows[i].Cells[j].Value) == 0)
                {
                    dataGridView4.Rows[i].Cells[j].Value = "Null";
                }

            }
        }

    }

My SQL Query is :

select l.admission_number,l.student_class ,l.student_name,hindi,maths,social,l.telugu,l.english , '' as english_1,'' as english_2,l.science, '' as ns,'' as ps from lkg_to_seventh_marks as l union all select e.admission_number ,e.student_class , e.student_name ,hindi,maths,social,e.telugu,'' as english,e.english_1 ,e.english_2,'' as science,e.ns,e.ps from eighth_to_ninth_marks as e

Upvotes: 1

Views: 2552

Answers (3)

Shiva Debrown
Shiva Debrown

Reputation: 177

As alireza said I am replacing any 0 values with 'NULL' in my SQL query with casting.for 3 tables having some different columns and some same columns 

select l.admission_number,l.student_class ,l.student_name,cast(l.telugu as char(4)) as telugu,
'NULL' as telugu_1,'NULL' as telugu_2,l.hindi,cast(l.english as CHAR(4)) as english,'NULL' as english_1,'NULL' as english_2,
cast(l.maths as CHAR(4)) as maths,'NULL' as maths_1,'NULL' as maths_2,
cast(l.science as CHAR(4)) as science,'NULL' as ns,'NULL' as ps,
cast(l.social as CHAR(4)) as social,'NULL' as social_1,
'NULL' as social_2,l.total
from lkg_to_seventh_marks as l 

union all 

select e.admission_number ,e.student_class ,e.student_name ,
cast(e.telugu as char(4)) as telugu,'NULL'as telugu_1,'NULL' as telugu_2,e.hindi,
'NULL' as english,cast(e.english_1 as CHAR(4)) as english_1,
CAST(e.english_2 as CHAR(4)) as english_2,CAST(e.maths as CHAR(4)) as maths,'NULL' as maths_1,
'NULL' as maths_2,'NULL' as science,cast(e.ns as CHAR(4)) as ns,CAST(e.ps as CHAR(4)) as ps,
'NULL' as social,'NULL' as social_1,'NULL' as social_2,e.total
from eighth_to_ninth_marks as e 

union all

select t.admission_number ,t.student_class ,t.student_name,'NULL'as telugu, 
cast(t.telugu_1 as char(4)) as telungu_1,CAST(t.telugu_2 as CHAR(4)) as telugu_2,
t.hindi,'NULL' as english,CAST(t.english_1 as CHAR(4)) as english_1,
CAST(t.english_2 as CHAR(4)) as english_2,'NULL' as maths,CAST(t.maths_1 as CHAR(4)) as maths_1,
CAST(t.maths_2 as CHAR(4)) as maths2,'NULL' as science,CAST(t.ns as CHAR(4)) as ns,CAST(t.ps as CHAR(4))as ps,
'NULL' as social,CAST(t.social_1 as CHAR(4)) as social_1,CAST(t.social_2 as CHAR(4)) as social_2,t.total
from tenthclass_marks as t

Upvotes: 0

Alireza
Alireza

Reputation: 5056

You can return 'NULL' instead of 0 directly from your query. Beware that the returned value won't be a number after this change, so use this query ONLY for filling the grid:

SELECT l.admission_number,l.student_class,
    l.student_name, 
    CASE hindi WHEN 0 THEN 'NULL' ELSE CONVERT(varchar(15), hindi), 
    CASE maths WHEN 0 THEN 'NULL' ELSE CONVERT(varchar(15), maths), 
    CASE social WHEN 0 THEN 'NULL' ELSE CONVERT(varchar(15), social),
    CASE l.telugu WHEN 0 THEN 'NULL' ELSE CONVERT(varchar(15), l.telugu),
    CASE l.english WHEN 0 THEN 'NULL' ELSE CONVERT(varchar(15), l.english),
    '' as english_1, '' as english_2,l.science, '' as ns,'' as ps 
FROM lkg_to_seventh_marks as l 
UNION ALL 
SELECT e.admission_number ,e.student_class , 
    e.student_name, 
    CASE hindi WHEN 0 THEN 'NULL' ELSE CONVERT(varchar(15), hindi), 
    CASE maths WHEN 0 THEN 'NULL' ELSE CONVERT(varchar(15), maths), 
    CASE social WHEN 0 THEN 'NULL' ELSE CONVERT(varchar(15), social),
    CASE e.telugu WHEN 0 THEN 'NULL' ELSE CONVERT(varchar(15), e.telugu),
    '' as english, 
    CASE e.english_1 WHEN 0 THEN 'NULL' ELSE CONVERT(varchar(15), e.english_1),
    CASE e.english_2 WHEN 0 THEN 'NULL' ELSE CONVERT(varchar(15), e.english_2),
    '' as science,
    CASE e.ns WHEN 0 THEN 'NULL' ELSE CONVERT(varchar(15), e.ns),
    CASE e.ps WHEN 0 THEN 'NULL' ELSE CONVERT(varchar(15), e.ps)
FROM eighth_to_ninth_marks as e

I hope you get the idea

Upvotes: 2

furkle
furkle

Reputation: 5059

Assuming you'd like to replace every instance of 0 with a string, one way to do this would be to use the DataGridView's RowsAdded event. Get a reference to the row through the DataGridViewRowEventArgs, iterate through each DataGridViewItem in the row, and change any 0 values you encounter to whatever you'd like to replace it with.

Here's an example usage of the event:

private void dataGridView1_NewRowNeeded(object sender,
        DataGridViewRowEventArgs e)
{
    foreach (DataGridViewCell cell in e.Row.Cells)
    {
        if (cell.Value == "0" || cell.Value == 0) // not sure which you're using
        {
             cell.Value = "your replacement value here";
        }
    }
}

MSDN has an article on RowsAdded here: http://msdn.microsoft.com/en-us/library/system.windows.forms.datagridview.rowsadded%28v=vs.110%29.aspx

Note that RadioSpace and AliReza are right - modifying elements in a UI control after you've already fed it your data is a little backwards and overcomplex.

Upvotes: 1

Related Questions