how to check each row of a column is null or with values?

Question

1.00/5 (1 vote)

See more:

table1---- Id name adviceno chequeno
11 abc 1 null
22 bcx 2 null
31 tex null A2093
54 yrt 3 null
52 ths null A3744

table2--- Id name alldetails_cheque
11 abc null
22 bcx null
31 tex null
54 yrt null
52 ths null

i need to update from table1 to table2
table1.Id=table2.Id
Table1.name=table2.name
if advice is not null then
update advice number
else addice number will be null and chequeno will update

output:

table2---- Id name alldetails_cheque (after update)
11 abc 1
22 bcx 2
31 tex A2093
54 yrt 3
52 ths A3744

SQL:
1. update table2 set t2.alldetails_cheque=t1.adviceno from table1 as t1 and table2 as t2
on t1.id=t2.id and t1.name=t2.name where chequeno is null

2. update table2 set t2.alldetails_cheque=t1.chequeno from table1 as t1 and table2 as t2
on t1.id=t2.id and t1.name=t2.name where adviceno is null

when this query is used then both is updating..

can you help me with this.

i need to do this in Asp.net(CSharp). if adviceno is has value it will update in table2 or chequeno will update in table2 alldetails_cheque
using asp.net

Posted 8-Sep-14 23:51pm

10923679

Add a Solution

Comments

Prasad Avunoori 9-Sep-14 5:56am

Just point out your issue. What have you tried?

Prasad Avunoori 9-Sep-14 5:58am

You may use ISNULL of SQL server.

10923679 9-Sep-14 5:59am

okay
i want to use this update query in asp.net(csharp)

10923679 9-Sep-14 6:00am

i have found my result in sql. it is working..
but i dont know how to use this in asp.net

10923679 9-Sep-14 6:03am

protected void Button7_Click1(object sender, EventArgs e)
{

DataTable dt = new DataTable();
string query = "select * from table1";
using (SqlConnection con = new SqlConnection(con_str))
{
using (SqlCommand cmd = new SqlCommand(query))
{
using (SqlDataAdapter da = new SqlDataAdapter())
{
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
da.SelectCommand = cmd;
da.Fill(dt);
}
}
}

try
{

string query2;
// string query3;
using (SqlConnection con = new SqlConnection(con_str))
{
con.Open();

foreach (DataRow row in dt.Rows)
{
string str = row["AdviceNo"].ToString();

//The conditions

if (str.Equals(null))
{

query2 = "update table2 set t2.alldetails_cheque=t1.chequeno from table1 as t1 and table2 as t2
on t1.id=t2.id and t1.name=t2.name where t1.adviceno is null";
SqlCommand cmd2 = new SqlCommand(query2, con);
cmd2.ExecuteNonQuery();
}
else
{
query2 = "update table2 set t2.alldetails_cheque=t1.adviceno from table1 as t1 and table2 as t2
on t1.id=t2.id and t1.name=t2.name where t1.chequeno is null ";
SqlCommand cmd2 = new SqlCommand(query2, con);
cmd2.ExecuteNonQuery();

}

}

con.Close();
}

}
catch (Exception ex)
{
Response.Write(ex.Message);

}
finally
{

Label2.Text = "updated";
}

i have tried this code.. but it is updating only the adviceno in alldetails_cheque..

Prasad Avunoori 9-Sep-14 6:11am

Find my solotion below.

10923679 9-Sep-14 6:17am

yeah i know that ... the SQL Query is working ..

but i want it in ASP.Net(Csharp)

check my code above...

this is working for me
query2 = "update t2 set t2.alldetails_cheque=t1.adviceno from table1 as t1 and table2 as t2
on t1.id=t2.id and t1.name=t2.name where t1.chequeno is null ";
SqlCommand cmd2 = new SqlCommand(query2, con);
cmd2.ExecuteNonQuery();

but this is not working
query2 = "update table2 set t2.alldetails_cheque=t1.chequeno from table1 as t1 and table2 as t2
on t1.id=t2.id and t1.name=t2.name where t1.adviceno is null ";

i guess the is some mistake in if condition ..
or shall i use something else to solve this problem..
please help me with this..

Prasad Avunoori 9-Sep-14 6:20am

What do you mean? Control is not going inside if (str.Equals(null))?

10923679 9-Sep-14 6:23am

yeah i think so ... that is why it is updating after
else
{
// condition

it is not updating the above query ..
in table1
both adviceno and chequeno has null value and has fields...
and i need to update all those fields to table2 alldetails_cheque

Prasad Avunoori 9-Sep-14 7:03am

Replace you if condition with following code.
if(string.IsNullOrEmpty(str))
{
// Your code.

}

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

10923679 · Accepted Answer · 2014-09-09T01:15:00

This code is perfectly working...

Thank You Friends for helping me a lot

protected void Button7_Click1(object sender, EventArgs e)
{

DataTable dt = new DataTable();
string query = "select * from table1";
using (SqlConnection con = new SqlConnection(con_str))
{
using (SqlCommand cmd = new SqlCommand(query))
{
using (SqlDataAdapter da = new SqlDataAdapter())
{
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
da.SelectCommand = cmd;
da.Fill(dt);
}
}
}

try
{

string query2;
// string query3;
using (SqlConnection con = new SqlConnection(con_str))
{
con.Open();

foreach (DataRow row in dt.Rows)
{
string str = row["AdviceNo"].ToString();

//The conditions

if (string.IsNullOrEmpty(str))
{

query2 = "update table2 set t2.alldetails_cheque=t1.chequeno from table1 as t1 and table2 as t2
on t1.id=t2.id and t1.name=t2.name where t1.adviceno is null";
SqlCommand cmd2 = new SqlCommand(query2, con);
cmd2.ExecuteNonQuery();
}
else
{
query2 = "update table2 set t2.alldetails_cheque=t1.adviceno from table1 as t1 and table2 as t2
on t1.id=t2.id and t1.name=t2.name where t1.chequeno is null ";
SqlCommand cmd2 = new SqlCommand(query2, con);
cmd2.ExecuteNonQuery();

}

}

con.Close();
}

}
catch (Exception ex)
{
Response.Write(ex.Message);

}
finally
{

Label2.Text = "updated";
}