I have a problem in executing a SQL query from a C# tool where it tries to do the insert.
我在从C#工具执行SQL查询时遇到问题,它尝试执行插入操作。
I need to insert NULL value if the string is empty (not entered by the user). I tried with the DB null value and normal string 'NULL' to do the NULL insert but all I get is an empty value (insetead of NULL keyword) which gives me the error.
如果字符串为空(我没有输入),我需要插入NULL值。我尝试使用DB null值和普通字符串'NULL'来执行NULL插入,但我得到的只是一个空值(不包括NULL关键字),这给了我错误。
Let me know if anyone has the solution for this....
让我知道是否有人有这个解决方案....
Below is my code
以下是我的代码
if (comboBox_ConfacValue.Text == "")
{
comboBox_ConfacValue.Text = DBNull.Value.ToString();
}
if (combobox_conversionDescription.Text == "")
{
combobox_conversionDescription.Text = "NULL";
}
try
{
con.Open();
if (MessageBox.Show("Do you really want to Insert these values?", "Confirm Insert", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
SqlDataAdapter SDA = new SqlDataAdapter(@" insert INTO Table1 (alpha1,alpha2,alpha3) VALUES ('" + comboBox_ConfacValue.Text + "','" + combobox_conversionDescription.Text + "','"+ combobox_Description.Text + "',')",con)
SDA.SelectCommand.ExecuteNonQuery();
MessageBox.Show("Inserted successfully.");
}
}
1 个解决方案
#1
2
You should avoid this kind of code. Concatenating strings to produce an sql command is a recipe to disasters. Parsing errors is the common mistake, but a worse foe is lurking behind this pattern and is called Sql Injection
你应该避免这种代码。连接字符串以生成sql命令是灾难的一种方法。解析错误是常见的错误,但更糟糕的敌人隐藏在这种模式背后,称为Sql Injection
try
{
con.Open();
if (MessageBox.Show("Do you really want to Insert these values?", "Confirm Insert", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
// Now the command text is no more built from pieces of
// of user input and it is a lot more clear
SqlCommand cmd = new SqlCommand(@"insert INTO Table1
(alpha1,alpha2,alpha3)
VALUES (@a1, @a2, @a3)", con);
// For every parameter placeholder add the respective parameter
// and set the DbNull.Value when you need it
cmd.Parameters.Add("@a1", SqlDbType.NVarChar).Value =
string.IsNullOrEmpty(comboBox_ConfacValue.Text) ?
DbNull.Value : comboBox_ConfacValue.Text);
cmd.Parameters.Add("@a2", SqlDbType.NVarChar).Value =
string.IsNullOrEmpty(combobox_conversionDescription.Text ) ?
DbNull.Value : combobox_conversionDescription.Text );
cmd.Parameters.Add("@a3", SqlDbType.NVarChar).Value =
string.IsNullOrEmpty(combobox_Description.Text ) ?
DbNull.Value : combobox_Description.Text );
// Run the command, no need to use all the infrastructure of
// an SqlDataAdapter here....
int rows = cmd.ExecuteNonQuery();
// Check the number of rows added before message...
if(rows > 0) MessageBox.Show("Inserted Successfully.");
#1
2
You should avoid this kind of code. Concatenating strings to produce an sql command is a recipe to disasters. Parsing errors is the common mistake, but a worse foe is lurking behind this pattern and is called Sql Injection
你应该避免这种代码。连接字符串以生成sql命令是灾难的一种方法。解析错误是常见的错误,但更糟糕的敌人隐藏在这种模式背后,称为Sql Injection
try
{
con.Open();
if (MessageBox.Show("Do you really want to Insert these values?", "Confirm Insert", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
// Now the command text is no more built from pieces of
// of user input and it is a lot more clear
SqlCommand cmd = new SqlCommand(@"insert INTO Table1
(alpha1,alpha2,alpha3)
VALUES (@a1, @a2, @a3)", con);
// For every parameter placeholder add the respective parameter
// and set the DbNull.Value when you need it
cmd.Parameters.Add("@a1", SqlDbType.NVarChar).Value =
string.IsNullOrEmpty(comboBox_ConfacValue.Text) ?
DbNull.Value : comboBox_ConfacValue.Text);
cmd.Parameters.Add("@a2", SqlDbType.NVarChar).Value =
string.IsNullOrEmpty(combobox_conversionDescription.Text ) ?
DbNull.Value : combobox_conversionDescription.Text );
cmd.Parameters.Add("@a3", SqlDbType.NVarChar).Value =
string.IsNullOrEmpty(combobox_Description.Text ) ?
DbNull.Value : combobox_Description.Text );
// Run the command, no need to use all the infrastructure of
// an SqlDataAdapter here....
int rows = cmd.ExecuteNonQuery();
// Check the number of rows added before message...
if(rows > 0) MessageBox.Show("Inserted Successfully.");