If SQL DB column is NULL insert into datagridview 0

Question

Hello guys I have got this query that might have in columns klisluz.cena and klisluz.pocet null or DBNull (not sure) values.

May I ask how can I prevent in this sQuery for those columns if there was null so I can insert into datagridview 0 ?

As this is JOIN query I'm not sure where should I implement ISNULL(klisluz.pocet,0) and ISNULL(klisluz.cena)

DataTable dt = new DataTable();
string sZakce = string.Empty;
if (zakce != null && zakce.Text != null)
{
    sZakce = zakce.Text;
}

string sQuery = string.Format("SELECT zajsluz.akce, zajsluz.text, klisluz.cena,
  klisluz.pocet, klisluz.subkey, zajsluz.ID FROM zajsluz LEFT JOIN klisluz ON
  zajsluz.ID = klisluz.IDzajsluz WHERE zajsluz.akce= '{0}' and 
  ISNULL(klisluz.subkey, '" + vyberradek + "') = '" + vyberradek + "'
  GROUP BY klisluz.subkey, zajsluz.akce, klisluz.cena, zajsluz.text,
  klisluz.pocet, zajsluz.ID", sZakce);
SqlDataAdapter SDA = new SqlDataAdapter(sQuery, spojeni);
SDA.Fill(dt);
dtg_ksluzby.DataSource = dt;

Thank you for your time.

So far I tried this:

for (int i = 0; i < (dtg_ksluzby.Rows.Count - 0); i++)
{

    if (dtg_ksluzby.Rows[i].Cells["pocet"].Value == null ||
       (string)dtg_ksluzby.Rows[i].Cells["pocet"].Value == string.Empty)
    {
    dtg_ksluzby.Rows[i].Cells["pocet"].Value = 0;
    }
    if (dtg_ksluzby.Rows[i].Cells["cena"].Value == null ||
      (string)dtg_ksluzby.Rows[i].Cells["cena"].Value == string.Empty)
    {
    dtg_ksluzby.Rows[i].Cells["cena"].Value = 0;
    }
}

But got exception that those column names cannot be found.

You have to do it in the SELECT clause. Check the answer by @Soner — Nilesh
– Nilesh, Commented Sep 12, 2013 at 11:15

Soner Gönül · Accepted Answer · 2013-09-12 11:03:46Z

3

You can use ISNULL (Transact-SQL)

Replaces NULL with the specified replacement value.

Syntax;

ISNULL ( check_expression , replacement_value )

For example;

SELECT ...., ISNULL(klisluz.cena, 0), ISNULL(klisluz.pocet, 0)

answered Sep 12, 2013 at 11:03

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ronak Patel · Accepted Answer · 2013-09-12 11:32:15Z

1

Try below query

string sQuery = string.Format("SELECT zajsluz.akce, zajsluz.text, klisluz.cena,
  ISNULL(klisluz.pocet,0) as [pocet], klisluz.subkey, zajsluz.ID FROM zajsluz LEFT JOIN klisluz ON
  zajsluz.ID = klisluz.IDzajsluz WHERE zajsluz.akce= '{0}' and 
  ISNULL(klisluz.subkey, '" + vyberradek + "') = '" + vyberradek + "'
  GROUP BY klisluz.subkey, zajsluz.akce, klisluz.cena, zajsluz.text,
  ISNULL(klisluz.pocet,0), zajsluz.ID", sZakce);

answered Sep 12, 2013 at 11:32

Ronak Patel

2,60219 silver badges20 bronze badges

Collectives™ on Stack Overflow

If SQL DB column is NULL insert into datagridview 0

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related