数据库字段类型存储两次 [英] Data base field type stored in two times

问题描述

 使用（SqlConnection connect =  new  SqlConnection（db.Connectionstring （）））
 {
 使用（SqlCommand command =  new  SqlCommand（） ）
 {
  string  pro =  ; 
  int  qty; 
  int  totqty; 
 
  int  i =  0 ; 
 
  foreach （DataGridViewRow rows  in  dataGridView1.Rows）
 { 
 
 SqlConnection con1 =  new  SqlConnection（db.Connectionstring（））; 
 con1.Open（）; 
 SqlCommand cmd1 =  new  SqlCommand（  select *来自销售，其中empnames =' + comboBox1.Text +  '，con1 ）; 
 SqlDataReader dr = cmd1.ExecuteReader（）; 
 
  if （dr.HasRows）
 {
  //   while（dr.Read（）） 
  //   { 
 SqlConnection con2 =  new  SqlConnection（db.Connectionstring（））; 
 con2.Open（）; 
 
 SqlCommand cmd2 =  new  SqlCommand（  update sales set empnames = @ empnames +' + comboBox1.Text +  '，categories = @categories，weight = @ weight，per = @ per，wastage = @wastage，customer = @ customer，party = @ party where date = @ date，con2）; 
 
 cmd2.Parameters.AddWithValue（  @ empnames，Convert.ToString （（comboBox1.Text）））; 
 cmd2.Parameters.AddWithValue（  @ date，Convert.ToDouble（rows.Cells [ 0 ]。值））; 
 cmd2.Parameters.AddWithValue（  @ categories，Convert.ToString（rows.Cells [ 1 ]。Value））; 
 cmd2.Parameters.AddWithValue（  @ weight，Convert.ToString（（行。单元格[ 2 ]。值）））; 
 cmd2.Parameters.AddWithValue（  @ per，Convert.ToDouble（rows.Cells [ 3 ]。Value））; 
 cmd2.Parameters.AddWithValue（  @ wastage，Convert.ToString（（行。单元格[ 4 ]。值）））; 
 cmd2.Parameters.AddWithValue（  @ customer，Convert.ToDouble（rows.Cells [ 5 ]。Value））; 
 cmd2.Parameters.AddWithValue（  @ party，Convert.ToString（（行。单元格[ 6 ]。Value）））; 
 
 
 
 
 cmd2.ExecuteNonQuery（）; 
 con2.Close（）; 
 
 
 SqlConnection con4 =  new  SqlConnection（db.Connectionstring（））; 
 con4.Open（）; 
 SqlCommand cmd4 =  new  SqlCommand（  update sales set amtcst =' + txt_netamount.Text +  '，amtparty =' + txtparty.Text +  '，amtfinal =' + txtfinal.Text +  'where empnames =' + comboBox1.SelectedText +  '，con4）; 
 cmd4.ExecuteNonQuery（）; 
 con4.Close（）; 
 
  //  }  
} 
 
 
  else  
 {
 SqlConnection con3 =  new  SqlConnection（ db.Connectionstring（））; 
 con3.Open（）; 
 SqlCommand cmd3 =  new  SqlCommand（  insert进入销售（empnames，date，categories，weight，per，wastage，customer，party，amtcst，amtparty，amtfinal）值（' + comboBox1.Text +   '，' + rows.Cells [ 0 ]。值+  '，' + rows.Cells [ 1 ]。值+  '，' + rows.Cells [ 2 ]。值+  '，' + rows.Cells [ 3 ]。值+  '，' + rows.Cells [ 4 ]。值+  '，' + rows.Cells [ 5 ]。值+  '，' + rows.Cells [ 6 ]。值+  '，' + txt_netamount.Text +  '，' + txtparty.Text +  '，' + txtfinal.Text +  '），con3）; 
 cmd3.ExecuteNonQuery（）; 
 con3.Close（）; 
 
 
} 
} 
 
} 
} 

这是我的c＃2010 win表单的插入和更新脚本，我有两次插入数据库的empnames问题。 />


任何人给我的想法



我尝试过的事情：

数据 base 字段类型存储在两次

解决方案

>> empnames = @ empnames +' + comboBox1.Text

>> cmd2.Parameters.AddWithValue（@ empnames，Convert.ToString（（comboBox1.Text）） ）;



你已经将@empnames作为参数传递（这很好）所以你不需要用查询重新添加它。因为empname将是保存为：sometextsometext



但是从第1行开始考虑sql注入查询...因为你要传递任何文本来查询而不仅仅是参数。



另外一点是1方法功能太多了。

打开很多连接，可以重新使用每次相同但清晰的参数

选择

更新

更新

insert



没有验证服务器端的数据......所以有些查询可能会因插入字符串而不是int等而失败

你正在混合参数和文字，这会给你造成混淆。使用参数rs和您的语句中只有参数。每次你开始写一些像 ......+ ... 这样的SQL语句时，你很可能走错了轨道。



因此更新应该类似于

 SqlCommand cmd2 =  new  SqlCommand （
  @ 更新销售额
 set empnames = @ empnames，
 categories = @类别，
重量= @重量，
每次= @每次，
浪费= @ wastage，
客户= @客户，
 party = @ party 
其中date = @ date，con2）; 
 cmd2.Parameters.AddWithValue（  @ empnames，comboBox1.Text）; 
 cmd2.Parameters.AddWithValue（  @ categories ，Convert.ToString（rows.Cells [ 1 ]。Value））; 
 cmd2.Parameters.AddWithValue（  @ weight，Convert.ToString（（rows.Cells [ 2 ]。Value）））; 
 cmd2.Parameters.AddWithValue（  @ per，Convert.ToDouble（rows.Cells [ 3 ]。Value））; 
 cmd2.Parameters.AddWithValue（  @ wastage，Convert.ToString（（行。单元格[ 4 ]。值）））; 
 cmd2.Parameters.AddWithValue（  @ customer，Convert.ToDouble（rows.Cells [ 5 ]。Value））; 
 cmd2.Parameters.AddWithValue（  @ party，Convert.ToString（（行。单元格[ 6 ]。Value）））; 
 cmd2.Parameters.AddWithValue（  @ date，Convert.ToDouble（rows.Cells [ 0 ]。值））; 
 
 cmd2.ExecuteNonQuery（）; 

但它并不止于此，你应该修复所有语句以使用参数。



另外你应该

- 在一个方法内只打开一次连接。为什么反复打开它？

- 使用块来确保正确放置对象。

- 使用try..catch块来正确处理异常。



我建议通过正确执行数据库操作 [ ^ ]

永远不要使用这样的连接构建SQL查询：

 SqlCommand cmd1 =  new  SqlCommand（  select * from sales where empnames =' + comboBox1.Text +  '，con1）; 

如果用户输入是异国情调，查询将失败，如果它是恶意的，它打开了SQL注入的大门。

<小时ef =http://www.w3schools.com/Sql/sql_injection.asp> SQL注入 [ ^ ]

SQL注入 - 维基百科 [ ^ ]

using (SqlConnection connect = new SqlConnection(db.Connectionstring()))
           {
               using (SqlCommand command = new SqlCommand())
               {
                   string pro = "";
                   int qty;
                   int totqty;

                   int i=0;

                        foreach (DataGridViewRow rows in dataGridView1.Rows)
                        {

                            SqlConnection con1 = new SqlConnection(db.Connectionstring());
                            con1.Open();
                            SqlCommand cmd1 = new SqlCommand("select * from sales where empnames='" + comboBox1.Text + "' ", con1);
                            SqlDataReader dr = cmd1.ExecuteReader();

                            if (dr.HasRows)
                            {
                                //while (dr.Read())
                                //{
                                    SqlConnection con2 = new SqlConnection(db.Connectionstring());
                                    con2.Open();

                                    SqlCommand cmd2 = new SqlCommand("update sales set empnames=@empnames+'" + comboBox1.Text + "',  categories=@categories, weight=@weight,per=@per,wastage=@wastage,customer=@customer,party=@party  where date=@date ", con2);

                                   cmd2.Parameters.AddWithValue("@empnames", Convert.ToString((comboBox1.Text)));
                                    cmd2.Parameters.AddWithValue("@date", Convert.ToDouble(rows.Cells[0].Value));
                                    cmd2.Parameters.AddWithValue("@categories", Convert.ToString(rows.Cells[1].Value));
                                    cmd2.Parameters.AddWithValue("@weight", Convert.ToString((rows.Cells[2].Value)));
                                    cmd2.Parameters.AddWithValue("@per", Convert.ToDouble(rows.Cells[3].Value));
                                    cmd2.Parameters.AddWithValue("@wastage", Convert.ToString((rows.Cells[4].Value)));
                                    cmd2.Parameters.AddWithValue("@customer", Convert.ToDouble(rows.Cells[5].Value));
                                    cmd2.Parameters.AddWithValue("@party", Convert.ToString((rows.Cells[6].Value)));




                                    cmd2.ExecuteNonQuery();
                                    con2.Close();


                                    SqlConnection con4 = new SqlConnection(db.Connectionstring());
                                    con4.Open();
                                    SqlCommand cmd4 = new SqlCommand("update sales set amtcst='" + txt_netamount.Text + "', amtparty='" + txtparty.Text + "', amtfinal='" + txtfinal.Text + "' where empnames='" + comboBox1.SelectedText + "'", con4);
                                    cmd4.ExecuteNonQuery();
                                    con4.Close();

                          // }
                            }


                            else
                            {
                                SqlConnection con3 = new SqlConnection(db.Connectionstring());
                                con3.Open();
                                SqlCommand cmd3 = new SqlCommand("insert into sales(empnames,date,categories,weight,per,wastage,customer,party,amtcst,amtparty,amtfinal)values('" + comboBox1.Text + "', '" + rows.Cells[0].Value + "','" + rows.Cells[1].Value + "','" + rows.Cells[2].Value + "','" + rows.Cells[3].Value + "','" + rows.Cells[4].Value + "','" + rows.Cells[5].Value + "','" + rows.Cells[6].Value + "','" + txt_netamount.Text + "','" + txtparty.Text + "','" + txtfinal.Text + "')", con3);
                                cmd3.ExecuteNonQuery();
                                con3.Close();


                            }
                        }

                   }
               }

this is my insert and updation script for my c# 2010 win forms, i have problem in empnames insert in two times to data base.

Any one give me ideas

What I have tried:

data base field type stored in two times

解决方案

>> empnames=@empnames+'" + comboBox1.Text
>> cmd2.Parameters.AddWithValue("@empnames", Convert.ToString((comboBox1.Text)));

You're already passing @empnames as parameter (which is good) so you don't need to re add it with query. As is empname will be saved as: "sometext""sometext"

But from the 1st line think of sql injection queries...as you're passing any text to query instead of just parameter.

Also on another point is that there is too much functionality being done by 1 method.
Opening many connections, can re use the same but clear parameters each time
select
update
update
insert

No validation of data on server side...so some queries may fail ex insert a string instead of int, etc

You're mixing parameters and literals and this is causing confusion for you. Use parameters and only parameters in your statements. Every time you start writing something like ..." + ... into an SQL statement you're most likely on the wrong track.

So the update should look something like

SqlCommand cmd2 = new SqlCommand(
@"update sales 
  set empnames=@empnames,
      categories=@categories,      
      weight=@weight,
      per=@per,
      wastage=@wastage,
      customer=@customer,
      party=@party  
where date=@date ", con2);
cmd2.Parameters.AddWithValue("@empnames", comboBox1.Text);
cmd2.Parameters.AddWithValue("@categories", Convert.ToString(rows.Cells[1].Value));
cmd2.Parameters.AddWithValue("@weight", Convert.ToString((rows.Cells[2].Value)));
cmd2.Parameters.AddWithValue("@per", Convert.ToDouble(rows.Cells[3].Value));
cmd2.Parameters.AddWithValue("@wastage", Convert.ToString((rows.Cells[4].Value)));
cmd2.Parameters.AddWithValue("@customer", Convert.ToDouble(rows.Cells[5].Value));
cmd2.Parameters.AddWithValue("@party", Convert.ToString((rows.Cells[6].Value)));
cmd2.Parameters.AddWithValue("@date", Convert.ToDouble(rows.Cells[0].Value));

cmd2.ExecuteNonQuery();

But it doesn't stop there, you should fix all the statements to use parameters.

Also you should
- Open a connection only once inside a single method. Why repeatedly open it?
- Use using blocks to ensure that objects are disposed correctly.
- Use try..catch blocks to properly handle exceptions.

I suggest reading through Properly executing database operations[^]

Never build an SQL query with concatenation like this:

SqlCommand cmd1 = new SqlCommand("select * from sales where empnames='" + comboBox1.Text + "' ", con1);

If user input is exotic, the query will fail, if it is malicious,it opens door to SQL injection.
SQL Injection[^]
SQL injection - Wikipedia[^]

这篇关于数据库字段类型存储两次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！