且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

如何防止将重复记录插入到db中

更新时间:1970-01-01 07:58:48

1。切勿使用字符串连接来创建SQL查询。您打开系统进行称为注入的攻击 - xkcd:对妈妈的漏洞利用 [ ^ ]

2.防止重复数据的唯一正确方法是通过声明唯一索引在您的数据库中... SQL Server提示:使用唯一约束来防止重复记录LGIT智能解决方案 [ ^ ]


删除该行并解决问题



 cmd.ExecuteNonQuery(); 







EASY SOLUTION


the problem with my code is when i click save it insert duplicate records

idont know how i fix that

 using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    using System.Data.SqlClient;
    using System.IO;
    namespace Carprogram
    {
        public partial class Form1 : Form
        {
            SqlConnection mm = new SqlConnection("Data Source=NAWAF;Initial Catalog=CAR;Integrated Security=True");
            DataTable dt = new DataTable();
            public Form1()
            {
                InitializeComponent();
            }

        
        string imgloc="";
        SqlCommand cmd;
         private void linkLabel4_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
          OpenFileDialog dialog = new OpenFileDialog ();
             dialog.Filter = "png files(*.png)|*.png|jpg files(*.jpg)|*.jpg|All files(*.*)|*.*";
             if(dialog.ShowDialog()==DialogResult.OK)
             {
                 imgloc =dialog.FileName.ToString();
                 pictureBox1.ImageLocation=imgloc;

             }
        }


        private void groupBox1_Enter(object sender, EventArgs e)
        {

        }

        private void save_Click(object sender, EventArgs e)
        {
            string stat = "active";

            byte[] images = null;
            FileStream Streem = new FileStream(imgloc, FileMode.Open, FileAccess.Read);
            BinaryReader brs = new BinaryReader(Streem);
            images = brs.ReadBytes((int)Streem.Length);

            
            mm.Open();

            string vmd = "Insert into vehicleinfo(modely,make,model,color,type,odometer,odoty,vin,vehicle,driverop,department,engine,transmission,tiresize,platlic,renewal,company,account,premium,due,note,img,status)VALUES('" + modely.Text + "' ,'" + make.Text + "' , '" + model.Text + "' , '" + color.Text + "' , '" + type.Text + "','" + odometer.Text + "' ,'" + odoty.Text + "','" + vin.Text + "' , '" + vehicle.Text + "' , '" + driverop.Text + "' , '" + department.Text + "','" + engine.Text + "','" + transmission.Text + "','" + tiresize.Text + "','" + platlic.Text + "','" + renewal.Text + "','" + company.Text + "','" + account.Text + "','" + premium.Text + "','" + due.Text + "','" + note.Text + "',@images,@stat)";
           
           cmd = new SqlCommand (vmd,mm);
            cmd.Parameters.Add(new SqlParameter("@images",images));
            cmd.Parameters.Add(new SqlParameter("@stat", stat));

             cmd.ExecuteNonQuery();

             int N = cmd.ExecuteNonQuery();
            

            mm.Close();

            MessageBox.Show(N.ToString() + "Data Saved");



            modely.Text ="";
             make.Text ="";
             model.Text ="";
             color.Text ="";
             type.Text ="";
              odometer.Text=""; 
              vin.Text ="";
             vehicle.Text ="";
             driverop.Text ="";
             department.Text ="";
              engine.Text ="";
             transmission.Text=""; 
             tiresize.Text ="";
              platlic.Text ="";
              renewal.Text ="";
              company.Text ="";
              
             premium.Text ="";
             due.Text = "";
             note.Text = "";


        }

        private void Form1_Load(object sender, EventArgs e)
        {
          
            fillData();
        }

       private void fillData()
        {
            SqlDataAdapter adapter = new SqlDataAdapter();
            SqlCommand rrm;

            string sql = "select * from vehicleinfo";
            rrm = new SqlCommand(sql, mm);
            adapter.SelectCommand = rrm;
            adapter.Fill(dt);
            dataGridView1.DataSource = dt;
        }

       private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
       {
           DataView dv = new DataView(dt);
           if(comboBox1.SelectedItem.ToString()=="Show ALL")
           {
               dataGridView1.DataSource = dt;

           }
           else
           {
               dv.RowFilter = string.Format("status LIKE '%0%'", comboBox1.SelectedItem.ToString());

           }
       }
    }
}



What I have tried:

i want single record not duplicate

1. NEVER use string concatenation to create SQL query. You open your system to attacks called 'injection' - xkcd: Exploits of a Mom[^]
2. The only true - and right - way to prevent duplicate data is by declaring unique indexes in your database... SQL Server Tip : Preventing Duplicate Records Using the "Unique" Constraint | LGIT Smart Solutions[^]


Delete the line and resolve the problem

cmd.ExecuteNonQuery();




EASY SOLUTION