如何检查列的每一行是null还是值? [英] how to check each row of a column is null or with values?
问题描述
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需要从table1更新到table2
table1.Id = table2.Id
Table1.name = table2.name
如果建议不为null则
更新建议编号
否则addice编号将为null且chequeno将更新
输出:
table2 ---- Id name alldetails_cheque(更新后)
11 abc 1
22 bcx 2
31 tex A2093
54 yrt 3
52 th A3744
SQL:
1 。update table2将table1中的t2.alldetails_cheque = t1.adviceno设置为t1,将table2设置为t2
on t1.id = t2.id和t1.name = t2.name其中chequeno为null
2.更新table2将table1中的t2.alldetails_cheque = t1.chequeno设置为t1,将table2设置为t2
on t1.id = t2.id and t1.name = t2.name其中adviceno为空
当使用此查询时,两者都在更新..
你可以帮我这个。
i需要在Asp中做到这一点。净(CSHARP)。如果adviceno有值它会更新table2或chequeno将在table2中更新alldetails_cheque
使用asp.net
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
推荐答案
这段代码完美无缺工作...
非常感谢你帮我很多
protected void Button7_Click1(对象发送者) ,EventArgs e)
{
DataTable dt = new DataTable();
string query =select *来自table1;
使用(SqlConnection con = new SqlConnection(con_str))
{
using(SqlCommand cmd = new SqlCommand(query) ))
{
使用(SqlDataAdapter da = new SqlDataAdapter())
{
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
da.SelectCommand = cmd;
da.Fill(dt);
}
}
}
尝试
{
string query2;
// string query3;
usi ng(SqlConnection con = new SqlConnection(con_str))
{
con.Open();
foreach(dt.Rows中的DataRow行)
{
string str = row [AdviceNo]。ToString();
//条件
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和t1.name = t2.name其中t1.adviceno为null;
SqlCommand cmd2 = new SqlCommand(query2,con);
cmd2。 ExecuteNonQuery();
}
else
{
query2 =update table2 set t2.alldetails_cheque = t1 .adviceno从table1作为t1,table2作为t2
在t1.id = t2.id和t1.name = t2.name其中t1.chequeno为null;
SqlCommand cmd2 = new SqlCommand(query2,con);
cmd2.ExecuteNonQuery();
}
}
con.Close();
}
}
catch(exception ex)
{
Response.Write(ex.Message);
}
终于
{
Label2.Text =updated;
}
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";
}
这篇关于如何检查列的每一行是null还是值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!