如果一个字段不为null，我想插入数据 [英] I want to insert data if one field is not null

问题描述

这是我的代码，如果testheader不为空，我想将数据插入数据库。



我收到错误

Here is my code where i want to insert data into the database if "testheader" is not null.

and I get error

"

ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.

当我尝试没有任何验证然后成功插入数据。



我尝试过：



c＃code ....



public bool InsertCustomizeRowtwo（string cocode，string dept，string testcode，string testname， string testheader，string sub1，string sub2，string sub3，string sub4，string sub5，string sub6，string sub7，string sub8，string sub9，string sub10）



{

if（testheader ==）

testheader = null;

试试

{

//连接

theConnection = new SqlConnection（）;

if （conString！=）

{

theConnection.ConnectionString = conString;

theConnection.Open（）;



}

//命令

theCommand = new SqlCommand（）;

theCommand.Connection = theConnection ;

if（testheader！= null）

{

theCommand.CommandText =插入PH_TestMaster_CustRowtwo（COMPCODE，DEPARTMENT，TESTCODE，TESTNAME， TEST_HEADER，TEST_SUB1，TEST_SUB2，TEST_SUB3，TEST_SUB4，TEST_SUB5，TEST_SUB6，TEST_SUB7，TEST_SUB8，TEST_SUB9，TEST_SUB10）VALUES（'+ cocode +'，'+ dept +'，'+ testcode +'，' + testname +'，'+ testheader +'，'+ sub1 +'，'+ sub2 +'，'+ sub3 +'，'+ sub4 +'，'+ sub5 +'，'+ sub6 +'，'+ sub7 +'，'+ sub8 +'，'+ sub9 +'，'+ sub10 +'）;



}

其他

{

theConnection.Dispose（）;

theCommand .Dispose（）;

}

theCommand.CommandType = CommandType.Text;

theCommand.ExecuteNonQuery（）;

返回true;





}

catch

{

返回false;

}

最后

{

theConnection.Dispose（）;

theCommand.Dispose（）;



}





}

"
when I tried it without any validation then data inserted successfully.

What I have tried:

c# code....

public bool InsertCustomizeRowtwo(string cocode,string dept, string testcode,string testname,string testheader,string sub1,string sub2,string sub3,string sub4,string sub5,string sub6,string sub7,string sub8, string sub9,string sub10)

{
if (testheader == "")
testheader = null;
try
{
//connection
theConnection = new SqlConnection();
if (conString != "")
{
theConnection.ConnectionString = conString;
theConnection.Open();

}
//command
theCommand = new SqlCommand();
theCommand.Connection = theConnection;
if (testheader != null)
{
theCommand.CommandText = "insert into PH_TestMaster_CustRowtwo(COMPCODE,DEPARTMENT,TESTCODE,TESTNAME,TEST_HEADER,TEST_SUB1,TEST_SUB2,TEST_SUB3,TEST_SUB4,TEST_SUB5,TEST_SUB6,TEST_SUB7,TEST_SUB8,TEST_SUB9,TEST_SUB10) VALUES('" + cocode + "','" + dept + "','" + testcode + "','" + testname + "','" + testheader + "','" + sub1 + "','" + sub2 + "','" + sub3 + "','" + sub4 + "','" + sub5 + "','" + sub6 + "','" + sub7 + "','" + sub8 + "','" + sub9 + "','" + sub10 + "')";

}
else
{
theConnection.Dispose();
theCommand.Dispose();
}
theCommand.CommandType = CommandType.Text;
theCommand.ExecuteNonQuery();
return true;


}
catch
{
return false;
}
finally
{
theConnection.Dispose();
theCommand.Dispose();

}


}

推荐答案

查看你的代码（我将删除不相关的内容）：

Look at your code (I'll rip out the irrelevancies):

if (testheader != null)
    {
    ...
    }
else
    {
    theConnection.Dispose();
    theCommand.Dispose();
    }
theCommand.CommandType = CommandType.Text;
theCommand.ExecuteNonQuery();

所以当testheader为null时，你处理一个对象 ，然后立即尝试使用它。

那不行，不会。当你尝试它时，就像将汽车送入破碎机，然后尝试将它开到商店。不行。



严肃地说，从不连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。请改用参数化查询。

如果您使用参数化查询并转储testheader null检查，您可能会发现整个问题都消失了。

So when testheader is null, you dispose of an object and then immediately try to use it.
That won't work, not ever. And When you try it's like feeding your car into a car-crushing machine and then trying to drive it to the shops afterwards. Doesn't work.

And seriously, never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.
You may find your whole problem disappears if you use parameterised queries and dump the testheader null check.

这篇关于如果一个字段不为null，我想插入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！