Pre check for data insertion

Question

I want to make a program for value prechecking .a user will give the single input (wbslement no) to UI .I want to insert that record into System . Before inserting into database I want to check It is present in Table or not . if it is present in table then It should not insert record into table if it is not present into database then it should insert .

currently at load time I am fetching all the records from table after that I am trying to insert into System.

in my code it is inserted value any case

            CrCon = new SqlConnection(spcallloggin);
            CrCon.Open();
            CrCmd = new SqlCommand();
            CrCmd.Connection = CrCon;

            CrCmd.CommandText = "GetOraderNumberDetail";
            CrCmd.CommandType = CommandType.StoredProcedure;

            sqladpter = new SqlDataAdapter(CrCmd);
            ds = new DataSet();
            sqladpter.Fill(ds);
            for (int count = 0; count < ds.Tables[0].Rows.Count; count++)
            {

                if (txtwbs.Text == ds.Tables[0].Rows[count][0].ToString())
                {
                            Lbmsg.Visible = true;
                            Lbmsg.Text = "Data Already Exists !";
                            count = count + 1;
                 }
                 else
                 {
                       insetreco(val);
                 }
             }

Please check these links you will get good result http://stackoverflow.com/questions/2273815/if-exists-before-insert-update-delete-for-optimization http://stackoverflow.com/questions/5599874/how-can-i-check-for-duplicates-before-inserting-into-a-table-when-inserting-by-s — Sunny, Jun 11 '12 at 07:00
I am Having single Table . So how can I check for Duplicates before inserting — Kuldeep Verma, Jun 11 '12 at 07:29

k0stya · Answer 1 · 2012-06-11T07:35:39.727

0

It is better to check it directly in the stored procedure.

IF NOT EXISTS(SELECT * FROM [TABLE] WHERE unique_field = "value of the txtwbs")
BEGIN
    INSERT INTO [TABLE]
    VALUES (value1, value2, value3,...)
END

Also you can change your code as following:

bool doesExist;
for (int count = 0; count < ds.Tables[0].Rows.Count; count++)
{

    if (txtwbs.Text == ds.Tables[0].Rows[count][0].ToString())
    {
          Lbmsg.Visible = true;
          Lbmsg.Text = "Data Already Exists !";
          doesExist = true;
          break;
    }
}
if(!doesExist)
    insetreco(val);

edited Jun 11 '12 at 07:35

answered Jun 11 '12 at 06:38

k0stya

4,091
29
39

how we can write where cluase in insert statement ? – Kuldeep Verma Jun 11 '12 at 07:25

score 0 · Answer 2 · answered Jun 11 '12 at 06:44

You could use DataView's RowFilter for querying in-memory tables :

Dataview dv = ds.Tables[0].DefaultView ;
dv.RowFilter="wbslement_no="+number;
if(dv.ToTable().Rows.Count==0)
{
      //insert into database 
}
else
{
       Lbmsg.Visible = true;
       Lbmsg.Text = "Data Already Exists !";
}

Or

Instead of two separate calls to the database , you could check for duplicity inside your Stored Procedure.

score 0 · Answer 3 · answered Jun 11 '12 at 06:47

0

Just make a "Unique" key constraint on the table itself. MySQL already has an existing construct to handle this, so there's no point doing in your code.

answered Jun 11 '12 at 06:47

Jason Larke

4,648
22
28

Sunny · Answer 4 · 2012-06-11T07:41:26.690

0

Check this :

Make changes according to your need

IF EXISTS (SELECT 1 FROM targetTable AS t
 WHERE t.empNo = @yourEmpNo
     AND t.project = @yourProject)
BEGIN
   --what ever you want to do here
END
ELSE
BEGIN
  INSERT INTO yourTable (empno, name, project)
  SELECT @empno, @name, @project
END

edited Jun 11 '12 at 07:41

answered Jun 11 '12 at 07:34

Sunny

2,897
5
27
58

Pre check for data insertion

4 Answers4