ADO/ADO.NET - show the column that have value

Asked By Trisha on 26-Mar-11 03:45 AM

string commd="select id+total from table1 where name ='dvorkin'";

OleDbCommand cmd=new OleDbCommand(commd,con);

con.Open();

OleDbDataReader dr1 = cmd.ExecuteReader();

while (dr1.Read())

{

if (dr1[0] != DBNull.Value)

{

textBox1.Text = dr1[0].ToString();

}

else

{

MessageBox.Show("Null value found");

}

}

if null column  found from any column between the two i want to show the column that have value between the two in the text box when else part will run... hope u understand my question.....(show the column that have value )

Mash B replied to Trisha on 26-Mar-11 03:59 AM
As suggested in previous post by Reena ,, you have to change your select query which will handle null isuue.

select isnull ((rate),0)+ isnull((total),0) from table1 where name ='dvorkin';

the above select query at runtime watchins if rate column is null or not , if it is null it replaces it with 0 value else it will kepp original value, same applies to total column. So you dont have to worry about the null value in columns.

Try below code and let us know !!


string commd="select isnull ((rate),0)+ isnull((total),0) from table1 where name ='dvorkin';"
 
OleDbCommand cmd=new OleDbCommand(commd,con);
 
con.Open();
 
OleDbDataReader dr1 = cmd.ExecuteReader();
 
while (dr1.Read())
 
{
 
if (dr1[0] != DBNull.Value)
 
{
 
textBox1.Text = dr1[0].ToString();
 
}
Reena Jain replied to Trisha on 26-Mar-11 04:02 AM
hi,

here is the code for you

string commd="select id, total, id+total as Gtotal from table1 where name ='dvorkin'";
OleDbCommand cmd=new OleDbCommand(commd,con);
con.Open();
OleDbDataReader dr1 = cmd.ExecuteReader();
while (dr1.Read())
{
if (dr1[2] != DBNull.Value)
{
textBox1.Text = dr1[2].ToString();
}
else if(dr1[0]!=DBNull.value)
{
textBox1.Text = dr1[0].ToString();
}
else if(dr1[1]!=DBNull.value)
{
textBox1.Text = dr1[1].ToString();
}
else
{
 MessageBox.Show("Null value found");
}

Hope this will help you
dipa ahuja replied to Trisha on 26-Mar-11 04:15 AM
Here is the code : You can also take the label before textbox to recognize which value you get from DB 

private void button1_Click(object sender, EventArgs e)
{
  string conn = "<connection String>";
 
  OleDbConnection con = new OleDbConnection(conn);
 
  string q = "Select name, id, id+total as tot from table1 where name='dvorkin'";
 
  OleDbCommand cmd = new OleDbCommand(q, con);
 
  con.Open();
 
  OleDbDataReader dr1 = cmd.ExecuteReader();
 
  while (dr1.Read())
  {
 
    if (dr1[0] != DBNull.Value)
    {
      label1.Text = "Name";
      textBox1.Text = dr1[0].ToString();
 
    }
    else if (dr1[1] != DBNull.Value)
    {
      label1.Text = "Id";
      textBox1.Text = dr1[1].ToString();
    }
 
    else if (dr1[2] != DBNull.Value)
    {
      label1.Text = "Total";
      textBox1.Text = dr1[2].ToString();
    }
    else
    {
 
      MessageBox.Show("Null value found");
 
    }
 
  }
 
}

Hope this will help you
Trisha replied to Mash B on 26-Mar-11 04:15 AM

OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Users\\Harpreet\\Desktop\\empdetails.mdb");

string commd = "select isnull ((id),0)+ isnull((total),0) from table1 where name ='dvorkin'";

OleDbCommand cmd=new OleDbCommand(commd,con);

con.Open();

OleDbDataReader dr1 = cmd.ExecuteReader();

while (dr1.Read())

{

if (dr1[0] != DBNull.Value)

{

textBox1.Text = dr1[0].ToString();

}


}

con.Close();

i am using same code like above but it is still giving error ...can u plz try this code for me once....

Mash B replied to Trisha on 26-Mar-11 04:49 AM
Ohhh .. sorry for mistake , it didnt flash in my mind that you are using query to ms access database.  isnull function is use if you are using sql database.
My suggestion would be better read both the values seperately and then sum it up


        while (dr1.Read())
        {

          int val1 =  dr1[0] == null ? 0 : Convert.ToInt16( dr1[0] );
          int val2 = dr1[1] == null ? 0 : Convert.ToInt16(dr1[1]);

          textbox.Text = (val1 + val2).ToString();


        }