Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
I have a WFA C# code which helps to insert data into the SQL table and reset the fields once saved, now I want to avoid duplicate values in the Table, want a message box if data already exists and let update it, if data doesn't exist then want to insert it, kindly help(Add code required). below is my code.
 
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Collections; 
 
namespace Dispatch_Tracker
{
    public partial class DispatchTracker : Form
    {
        public DispatchTracker()
        {
            InitializeComponent();
        }
 
    string connection = "Data Source=WN7-4WVX1BS;" + "Initial Catalog=DispatchDB;Persist Security Info=True;" + "User ID=sa;" + "Password= ***";
 
        private void Save_Click(object sender, EventArgs e)
        {
            if (DispatcherName.Text == "")
            {
                MessageBox.Show("Would you like to Lose out on Productivity?\nPlease select the Dispatcher name.");
                DispatcherName.Focus();
            }
 
            else if (DispatchNumber.Text == "")
            {
                MessageBox.Show("Did you really process a Dispatch?\nI don't see a Dispatch Number.");
                DispatchNumber.Focus();
            }
 
            else if (DPSType.Text == "")
            {
                MessageBox.Show("Did You Just Forget Something?\nPlease Enter the DPS Type");
                DPSType.Focus();
            }
 
            else if (QG.Text == "")
            {
                MessageBox.Show("Please select the Queue which the DPS belongs to.");
                QG.Focus();
            }
           
            else
            {
                dateTimePickerDT.Value = System.DateTime.Now;
                string sDispatcher = DispatcherName.Text;
                string sDPSNum = DispatchNumber.Text;
                string sDPSType = DPSType.Text;
                DateTime dtDT = dateTimePickerDT.Value;
                string sQueue = QG.Text;
 
                string query = "INSERT INTO dps_data(DispatcherName, DispatchNumber, DPSType, DateTime, Queue)" +"VALUES( @Dispatcher, @DPSNum, @DPSType, @DT, @Queue)";
 
                SqlConnection conn = new SqlConnection(connection);
                SqlCommand cmd = new SqlCommand(query, conn);
                conn.Open();
 
                cmd.Parameters.Add(new SqlParameter("@Dispatcher", sDispatcher));
                cmd.Parameters.Add(new SqlParameter("@DPSNum", sDPSNum));
                cmd.Parameters.Add(new SqlParameter("@DPSType", sDPSType));
                cmd.Parameters.Add(new SqlParameter("@DT", dtDT));
                cmd.Parameters.Add(new SqlParameter("@Queue", sQueue));
 
                int result = cmd.ExecuteNonQuery();
    
                if (result > 0)
                
                {
                    MessageBox.Show("Data Added Successfully");
                }
                
                else
                    
                {
                    MessageBox.Show("Error in Adding Data");
                }
 
                conn.Close();
 
                DispatchNumber.Text = String.Empty;
                DPSType.Text = String.Empty;
                QG.Text = String.Empty;
            }
        }
 
        private void Form1_Load(object sender, EventArgs e)
        {
 
        }
 
        private void DispatcherName_SelectedIndexChanged(object sender, EventArgs e)
        {
 
        }
 
        private void QG_SelectedIndexChanged(object sender, EventArgs e)
        {
            
        }
       
    }
}
 
 

Edited: Code Blocks added
Posted 19-Mar-13 15:20pm
Edited 19-Mar-13 15:34pm
v2
Comments
ThePhantomUpvoter at 19-Mar-13 20:31pm
   
Please format your code so that it is readable.
PIEBALDconsult at 19-Mar-13 21:35pm
   
"Add code required"
 
We don't do that.
 

If this is SQL Server, you may want a MERGE command. But probably you should redesign your app to avoid all that.
ryanb31 at 19-Mar-13 21:37pm
   
Just do a check first. Put this into a stored procedure and in your stored procedure use IF EXISTS()
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

You can use a Stored Procedure that will get the parameters and check if the record exists, then either INSERT or UPDATE according to the result.
 
quite easy Smile | :)
 
If you need help with stored procedures, just post another question and we will gladly help.
 
Cheers,
Edo
  Permalink  
Comments
JagzKrish at 20-Mar-13 13:06pm
   
Edo Tzumer - Thank you, your answer sounds simple and confident, but to be honest, I am a Novice, have not much info about stored procedure and how to script it in this code. Posting another question, kindly assist.
Edo Tzumer at 21-Mar-13 1:26am
   
In case you are behind, I saw you posted a second question and you've got a nice answer there too.
 
Stored Procedures are code blocks inside the SQL Server that you can call from your C# application quite easily and they run the code of course on the DB hosting server.
This is a handy way to accomplish tasks on the server instead of having to query the DB and then having to query again, or in cases where you need a short answer but a long data set in order to get to it, in which case it is far better to perform that on the server side.
 
Do ask if you missed me or if I went too fast ...
 
Cheers,
Edo
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

You can simply code for reading the data or row that you need to update/insert.
If record exists it will tell you else you can insert data.You can use Data Reader for that.
 
If(dr.Read())//Here dr is the object of Data Reader
{
Update();
}
else
{
insert();
}
 
Thanks
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 459
1 Maciej Los 325
2 Sergey Alexandrovich Kryukov 234
3 DamithSL 233
4 BillWoodruff 200
0 OriginalGriff 7,168
1 Sergey Alexandrovich Kryukov 6,377
2 DamithSL 5,461
3 Manas Bhardwaj 4,876
4 Maciej Los 4,450


Advertise | Privacy | Mobile
Web03 | 2.8.1411023.1 | Last Updated 20 Mar 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100