If not exists then insert else show message "Already exists"

Question

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;

namespace Barcode
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string strconn = @"Data Source=ASHWINI-LAPY\SQLEXPRESS;Initial Catalog=complete;Integrated Security=True;Pooling=False";
            SqlDataReader reader = null;

            SqlConnection conn = null;

            conn = new SqlConnection(strconn);
            conn.Open();

            DateTime Dt_Time = DateTime.Now;
            string Barcode = textBox1.Text;
            SqlCommand cmd = new SqlCommand("select Barcode from table3 where @Barcode='" + textBox1.Text + "'", conn);
            cmd.Parameters.AddWithValue("@Barcode", textBox1.Text);
            reader = cmd.ExecuteReader();
            if (reader != null && reader.HasRows)
            {
                //email exists in db do something

                MessageBox.Show("Barcode Already Exists!!");

            }
            else
            {
                string strquery = string.Format("insert into table3 values('{0}','{1}')", Barcode, Dt_Time);


                cmd = new SqlCommand(strquery, conn);


                int count = (int)cmd.ExecuteNonQuery();
                MessageBox.Show("Barcode:" + Barcode +
                                "\nTime" + Dt_Time);



            }

I am new to C# coding so I tried to do it like what I mentioned below in code, so please somebody help me.

I want to insert a barcode manually and when I press button the SQL Server database has to be checked whether that barcode exists. If not, it has to insert that barcode into the database, but if it already exists, it has to give a message that barcode already exists!

Along with inserting barcode I am also inserting system date and time also in database.

First and foremost this would be `... where Barcode = @Barcode" (glad you are using parameters, same applicable for the inserts too) — V4Vendetta, Aug 03 '12 at 06:58

score 2 · Answer 1 · edited May 23 '17 at 10:34

EDIT

C# code that you can write in your button click event

using (System.Data.SqlClient.SqlConnection cn = 
                    new System.Data.SqlClient.SqlConnection(@"Data Source=ASHWINI-LAPY\SQLEXPRESS;Initial Catalog=complete;Integrated Security=True;Pooling=False"+
                        "Integrated Security=True"))
{
       using (System.Data.SqlClient.SqlCommand cmd= new System.Data.SqlClient.SqlCommand("IsBarcodeCheckAndInsert", cn))
       {
            cmd.CommandType=CommandType.StoredProcedure ; 
            SqlParameter parm= new SqlParameter("@BarCode", cn",SqlDbType.VarChar) ;
            parm.Value="ALFKI";
            parm.Size=25;  
            parm.Direction =ParameterDirection.Input ;
            cmd.Parameters.Add(parm);
            SqlParameter parm2=new SqlParameter("@IsExists",SqlDbType.Int);
            parm2.Direction=ParameterDirection.Output;
            cmd.Parameters.Add(parm2); 
            cn.Open();
            cmd.ExecuteNonQuery();
            cn.Close();
            int IsExists = Convert.ToInt32(cmd.Parameters["@IsExists"].Value.ToString());
            if(IsExists ==0)
                 MessageBox.Show("Barcode Already Exists !!"); 
            else if(IsExists ==1)
                 MessageBox.Show("Barcode not Exists And Inserted In DataBase!!"); 

      }
}

SQL Procdure

CREATE PROCEDURE [dbo].[IsBarcodeCheckAndInsert]
     (
       @BarCode AS VARCHAR(25),
       @IsExists AS INT out     )
 AS 
BEGIN
 IF EXISTS (SELECT * FROM table3 WHERE BarCode = @BarCode )
 BEGIN
     set @IsExists =1
 END
 ELSE
 BEGIN 
   Insert into table3 values(@BarCode ,getDate())
     set @IsExists =0
 END 
END

Whats wrong with the code I check your code code is fine ..if it's not working at you end what error you are getting.

Just on recommandation make use of SQLParameter in your second queryi.e in insert query also to avoid SQLInjection attack for more detail check here : How does SQLParameter prevent SQL Injection?

You might want to change the first line of your SQL statement (ALTER, stored procedure name) :) — rikitikitik, Aug 03 '12 at 07:10
I still do not see how a barcode comes into play when you do an UpdateCustomerLogin ;) — rikitikitik, Aug 03 '12 at 07:18

Alex · Answer 2 · 2012-08-06T06:55:03.283

You mixed up your sql parameters syntax, this:

SqlCommand cmd = new SqlCommand("select Barcode from table3 where @Barcode='" + textBox1.Text + "'", conn);
cmd.Parameters.AddWithValue("@Barcode", textBox1.Text);

Should be changed to be like this:

SqlCommand cmd = new SqlCommand("select Barcode from table3 where Barcode = @Barcode", conn);
cmd.Parameters.AddWithValue("@Barcode", textBox1.Text);

Basically you switched the column name with the parameter name in the query.

UPDATE

As for the "There is already an open DataReader..." exception, adjust the code with using blocks (in a "best practice" approach), like this:

private void button1_Click(object sender, EventArgs e)
{
    string strconn = "<connection string";

    using (SqlConnection conn = new SqlConnection(strconn))
    {
        bool readerHasRows = false; // <-- Initialize bool here for later use
        DateTime Dt_Time = DateTime.Now;
        string Barcode = textBox1.Text;
        string commandQuery = "SELECT Barcode FROM table3 WHERE Barcode = @Barcode";
        using(SqlCommand cmd = new SqlCommand(commandQuery, conn))
        {
            cmd.Parameters.AddWithValue("@Barcode", textBox1.Text);
            using(SqlDataReader reader = cmd.ExecuteReader())
            {
                // bool initialized above is set here
                readerHasRows = (reader != null && reader.HasRows);
            }
        }

        if (readerHasRows)
        {
            //email exists in db do something
            MessageBox.Show("Barcode Already Exists!!");
        }
        else
        {
            //Same as above
            string strquery = "INSERT INTO table3 VALUES (@Barcode, @DtTime)"; // '{0}','{1}')", Barcode, Dt_Time);
            using (SqlCommand cmd = new SqlCommand(strquery, conn))
            {
                cmd.Parameters.AddWithValue("Barcode", Barcode);
                cmd.Parameters.AddWithValue("DtTime", Dt_Time);
                int count = cmd.ExecuteNonQuery(); // this already the number of affected rows by itself
                // NOTE: '\n' doesn't really work to output a line break. 
                // Environment.NewLine should be used.
                MessageBox.Show("Barcode:" + Barcode + Environment.NewLine + "Time" + Dt_Time);
            }

        // code probably goes on ...

    } // end of using(SqlConnection...
} // end of method

Should at least lead you on the right track.

thanks Alex!!,Its working now but when you enter the same Barcode which is present in the sql database, its showing"There is already an open Datareader associated with this command which must be closed first" the error message comes when executing this Query int count = (int)cmd.ExecuteNonQuery(); — danyss, Aug 04 '12 at 05:23
@danyss I updated the answer with a rewrite of your code according to the most common best practice (SqlConnection/Command/DataReader are IDisposable, so using blocks are highly suggested). — Alex, Aug 06 '12 at 06:55

score 1 · Answer 3 · answered Aug 03 '12 at 07:01

Check out these lines of code:

string Barcode = textBox1.Text;
SqlCommand cmd = new SqlCommand("select Barcode from table3 where @Barcode='" + textBox1.Text + "'", conn);
cmd.Parameters.AddWithValue("@Barcode", textBox1.Text);

If textBox1.Text is equal to "example", the resulting SQL query would be

Select Barcode from table3 where 'example'='example'

You might want to change the SqlCommand statement to:

SqlCommand cmd = new SqlCommand("select Barcode from table3 where Barcode=@Barcode", conn);

score 1 · Answer 4 · answered Aug 03 '12 at 07:08

1

You can do something like this:

SqlCommand cmd = new SqlCommand("select Barcode from table3 where Barcode=@Barcode", conn);
cmd.Parameters.AddWithValue("@Barcode", textBox1.Text);

Regards

answered Aug 03 '12 at 07:08

BizApps

6,048
9
40
62

score 0 · Answer 5 · answered Aug 14 '13 at 14:25

You can do this in one sql query with the Merge-command.

In plain SQL it will look like:

merge table3 WITH(HOLDLOCK) as target
    using (SELECT @Barcode, @DtTime)
        as source (Barcode, DtTime)
        on target.Barcode = @Barcode
    when not matched then
        insert ( Barcode, DtTime)
        values ( @Barcode, @DtTime);

If not exists then insert else show message "Already exists"

5 Answers5