Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# SQL VisualStudio2010
Guys, please help.. I am a novice with very little knowledge about programming, here I have my source code that inserts data into SQL DB, want to add a Stored procedure to this code so that it validates if the entry exists before inserting data, "IF EXISTS UPDATE ELSE INSERT", I have no idea about stored procedures, Kindly help, need to finish this project overnight, Please assist.
 
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)
        {
            
        }
       
    }
}
Posted 20-Mar-13 8:13am
Edited 20-Mar-13 8:15am
v3
Comments
RedDK at 20-Mar-13 13:32pm
   
I think this might be a double post ...
JagzKrish at 20-Mar-13 13:36pm
   
RedDK - yes it might be, but with a different question as suggested by one of the members, in response to my previous question, member wanted me to post another question in specific to Stored procedure, which I have no idea about, added my source code so that one canunderstand what I am actually looking fwd to.
Thanks
RedDK at 20-Mar-13 13:44pm
   
I could be wrong about this but isn't it possible to just a add tag to the OP by clicking on "Improve question"? Not arguing, just a suggestion for next time? Anyway ...
JagzKrish at 20-Mar-13 13:46pm
   
I will sure keep that in mind, I am all new here on Code Project, so have not much knowledge on stuff, Sorry for my ignorance and thank you for the advice :)
RedDK at 20-Mar-13 14:46pm
   
Just a thought but have a look at:
http://code.msdn.microsoft.com/vstudio/site/search?query=SQL&f%5B4%5D.Value=SQL&f%5B4%5D.Type=SearchText&f%5B0%5D.Value=9.0&f%5B0%5D.Type=VisualStudioVersion&f%5B0%5D.Text=Visual%20Studio%202008&f%5B1%5D.Value=C%23&f%5B1%5D.Type=ProgrammingLanguage&f%5B1%5D.Text=C%23&f%5B2%5D.Value=10.0&f%5B2%5D.Type=VisualStudioVersion&f%5B2%5D.Text=Visual%20Studio%202010&f%5B3%5D.Value=Data&f%5B3%5D.Type=Platform&f%5B3%5D.Text=Data&ac=4
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

This is a rough sample of what you require.
 
IF EXISTS (SELECT * FROM [dbo].[Table] WHERE [uid]=@uid)
BEGIN
    --update existing record
    UPDATE [dbo].[Table] SET [Field1]=@Parameter1, [Field2]=@Parameter2
    WHERE [uid]=@uid
END
ELSE
BEGIN
    --insert new record
    INSERT INTO [dbo].[Table] ([Field1], [Field2])
    VALUES (@Parameter1, @Parameter2)
    SET @uid=SCOPE_IDENTITY()
END
  Permalink  
Comments
Maciej Los at 20-Mar-13 15:45pm
   
It should works, my 5!
JagzKrish at 8-Apr-13 20:25pm
   
Pheonyx: This does the Job for me perfectly fine. However, challenging part is, I want the Application to throw a pop up or message box, "Data already exists do you want to update?" on clicking Ok it should be updated, if data doesn't exist, it will just insert without any pop up. Any solution for this Lame man?
Pheonyx at 9-Apr-13 4:04am
   
Hi JagzKrish,
 
Basically, if you are passing in the uid, and the user is not able to set that value (which they shouldnt be as it is unique) in the main application code just check to see if uid > 0, if it is >0 then it should exist so ask them if they want to update it. If it is = 0 then just save it.
JagzKrish at 9-Apr-13 13:06pm
   
Pheonyx: I am sorry, still don't understand that, in which part of the code should I use the IF ELSE statement? so that I can throw a message box before actually updating the Row in the table. Kindly help, I am a NooB...
Pheonyx at 9-Apr-13 14:57pm
   
You can't throw a message box from a stored procedure. You need to do some logic in your main program that checks to see if the entry exists. I am assuming your table has a unique primary key column. But without knowing your actual table structure and the stored procedure you have produced so far it is quote hard to advise.
JagzKrish at 9-Apr-13 15:17pm
   
Pheonyx: Here is the stored procedure that I have used.
 
CREATE PROCEDURE [dbo].[dpssp_InsertOrUpdate]
(
@Dispatcher char(30),
@DPSNum char(10),
@DPSType char(10),
@DT datetime,
@Queue char(10)
)
AS BEGIN
 
IF EXISTS (SELECT * FROM dbo.dps_data WHERE [DispatchNumber]=@DPSNum)
 
BEGIN
 
UPDATE dbo.dps_data SET [DispatcherName] = @Dispatcher, [DispatchNumber] = @DPSNum, [DPSType]=@DPSType,[DateTime]=@DT,[Queue]=@Queue
WHERE DispatchNumber=@DPSNum
 
END
 
ELSE
 
BEGIN
 
INSERT INTO dps_data(DispatcherName, DispatchNumber, DPSType, DateTime, Queue) VALUES( @Dispatcher, @DPSNum, @DPSType, @DT, @Queue)
SET @DPSNum=SCOPE_IDENTITY()
END
 
END
Pheonyx at 10-Apr-13 4:17am
   
In your table, how is the column DPSNum configured? It appears to be a char(10) from what you have shown. Is it the Primary Key column of the table? Is it guaranteed to be unique?
JagzKrish at 10-Apr-13 8:08am
   
Pheonyx: DPSNum is the Primary Key, so this is what is happening when I execute the Application with the stored procedure. Here is an example, lets say there is a Number 123 in the Column DPSNum and rest four columns have 'A' 'B' 'C' 'D' respectively. When I again insert the values 'E' 'F' 'G' 'H' and Value '123' in the DPSNum Box in the application, The values ABCD in the table gets replaced by EFGH, is that right?
Pheonyx at 10-Apr-13 8:19am
   
Yes that is correct.
If that is the case, then you can take two approaches to checking if data exists.
a) you can perform a select query on the database passing in the DPSNum, if this returns a count value > 0 then prompt to ask if they wish to update.
b) Check the DPSNum field to see if it has been left blank, if it is blank then you can be sure it is a new DPSNum, if it is not blank then again, ask if they wish to update it.
 
How you go about this depends alot on your current program design and what is easier.
If DPSNum can be typed in by a user then take the first approach, if DPSNum is pre-populated based on a selection or creation of a new object then take the second approach.
 
At least that is my advice.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Hi,
 
I write you a sample stored procedure for what you need.
 
Please note that dpssp_InsertOrUpdate is the stored procedure name, and that i make assumptions about input parameters based on your code.
 
Is necessary to write the insert and update statement in the respective comment.
 
CREATE PROCEDURE dbo.dpssp_InsertOrUpdate
(
        --i make an assumption about sql types
	@Dispatcher nvarchar(max), 
	@DPSNum nvarchar(max), 
	@DPSType nvarchar(max), 
	@DT datetime, 
	@Queue nvarchar(max)
)
AS
BEGIN
	IF EXISTS(
			SELECT Dispatcher 
			FROM dps_data 
			WHERE
			   DispatcherName = @Dispatcher
		           AND DispatchNumber = @DPSNum
		           AND DPSType = @DPSType
			   AND [DateTime] = @DT
		           AND Queue = @Queue
	         )
	BEGIN
		-- UPDATE SQL
	END
	 ELSE
	BEGIN
		-- INSERT SQL
	END
END
 
You will need to use a sql client like management studio for create the stored procedure in the database engine.
In the code you need to change your sql command to something like this:
 
SqlConnection conn = new SqlConnection(connection);
SqlCommand cmd = new SqlCommand();
                
cmd.CommandType = CommandType.StoredProcedure;
//Assuming solution sp name
cmd.CommandText = "dbo.dpssp_InsertOrUpdate"; 
cmd.Connection = 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();
 
//Maybe you want to handle sqlexception or exception types.

conn.Close();
----------------------------------------------------------------------
 
Good luck.
  Permalink  
v3
Comments
JagzKrish at 20-Mar-13 13:50pm
   
José Amílcar Ferreira Casimiro - Thank you, I sure get the logic here as what your trying to make a point, my question is how and where in my source code do I add it? or to be really Honest, I want it to be spoon fed, Sorry about that :( I am completely dumb.
Maciej Los at 20-Mar-13 15:37pm
   
See my answer.
JagzKrish at 20-Mar-13 15:39pm
   
Maciej Los: Did you post one? I dont see it... :(
Maciej Los at 20-Mar-13 15:43pm
   
Yes, i did it. See it one more time.
Maciej Los at 20-Mar-13 16:52pm
   
Jose, please, use "Improve answer" rather than putting code into comment, OK?
José Amílcar Ferreira Casimiro at 20-Mar-13 17:05pm
   
Ok. I realized the message. I was just trying to help.
Maciej Los at 20-Mar-13 17:30pm
   
Thank you ;)
Maciej Los at 20-Mar-13 15:45pm
   
Good answer, my 5!
José Amílcar Ferreira Casimiro at 20-Mar-13 16:39pm
   
thx
JagzKrish at 1-Apr-13 18:38pm
   
José Amílcar Ferreira Casimiro : If I got it right, i am creating a stored procedure on SQL and calling the same from the Code in Application. However, I also want the application to initimate the user if exists "Data already exists do you want to update?" on clicking Ok it should be updated, if data doesn't exist, it will just insert without any pop up.
   
Then you will need to make a previous select statement to verify if the data already exists in the database. If exists, then prompt the user for update. You can use the select statement used in the solution between the if statement to verify if the record already exists in database.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

  Permalink  
Comments
Prasad_Kulkarni at 21-Mar-13 1:50am
   
5'ed :-D
Maciej Los at 21-Mar-13 2:49am
   
Thank you, Prasad ;)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

Refer to this sp i think u got the solution if u have any query let me know............
create proc sp_dpsdata( @Dispatcher datatype, @DPSNum datatype, @DPSType datatype, @DT datatype, @Queue datatype)
as begin
declare count int
count=(select count(*) from dps_data where DispatchNumber=@DPSNum)
note:DispatchNumber should be primary key column or instead of DispatchNumber mention primary key column
 
if(count>0)
{
update dpsdata table query
}
else
{
insert dpsdata table query
}
end
  Permalink  
v2

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

  Print Answers RSS
0 Maciej Los 310
1 OriginalGriff 285
2 Afzaal Ahmad Zeeshan 200
3 Sergey Alexandrovich Kryukov 195
4 BillWoodruff 180
0 OriginalGriff 6,499
1 Sergey Alexandrovich Kryukov 6,048
2 DamithSL 5,193
3 Manas Bhardwaj 4,657
4 Maciej Los 4,120


Advertise | Privacy | Mobile
Web04 | 2.8.1411022.1 | Last Updated 21 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