If exists... Query cannot work

Question

0.00/5 (No votes)

See more:

Hi,

I have table Item,I need to check if the ID exists in table then delete the record and insert it.My code look like this

C#

public bool Savedata(List<itemValues> lstitem)
       {

          string query = "IF EXISTS(SELECT ID from Item where ID=@ID)" +
                       "DELETE FROM Item WHERE ID=@ID;" +
                       "INSERT INTO Item(" +
                       "ItemName, " +
                       "Price, " +
                       "ID)  VALUES (" +
                       "@ItemName, " +
                       "@Price, " +
                       "@ID)";

           return Data.CreateItem(lstitem, query);
       }

C#

public bool CreateItem(List<itemValues> lstitem, string query)
      {
          try
          {
              Connection Conn = new Connection();
              sqlCon = Conn.SqlConnection();

                  if (lstitem.Any())
                  {
                      foreach (var item in lstitem)
                      {

                          using (SqlCommand cmd = new SqlCommand(query, sqlCon))
                          {

                              cmd.CommandType = CommandType.Text;
                              cmd.Parameters.Add("@ItemName ", SqlDbType.VarChar).Value = item.itemName;
                              cmd.Parameters.Add("@Price", SqlDbType.Int).Value = item.Price;
                              cmd.Parameters.Add("@ID", SqlDbType.Int).Value = item.ID;

                              sqlCon.Open();
                              int rowsAffected = cmd.ExecuteNonQuery();

                              sqlCon.Close();
                          }

                      }


                  }
          }
          catch (Exception ex)
          {

              return false;
          }

          return true;
      }

It cannot check exists data,insert all data in the list to Item table.How to solve this?

What I have tried:

I try with If exists(...) query.

Posted 25-Oct-16 1:18am

Dil0500

Updated 1-Nov-16 1:03am

Add a Solution

7 solutions

Solution 2

You don't need IF EXISTS - it's redundant. Simple remove the record and insert the new one.

However, I would actually do it this way:

SQL

-- try to update the record
UPDATE	[mydatabase]
SET	ItemName = @itemName, Price = @price,
WHERE	ID = @ID;
-- if no record was updated, insert it	
IF (@@ROWCOUNT = 0)
    INSERT INTO [mydatabase](ID, ItemName, Price) VALUES (@ID, @itemName, @price);

Finally, use SSME to test your queries before putting them into your code (or a stored procedure).

Posted 25-Oct-16 1:35am

#realJSOP

Updated 25-Oct-16 1:43am

v3

Comments

Suvendu Shekhar Giri 25-Oct-16 7:58am

True ! Should solve the purpose.
My 5!

Solution 3

I haven't seen many people doing this.
The problem here could be due to the scope is not defined correctly.

The best way would be to create a stored procedure which can be executed on single call to the DB. Your stored proc should look like-

SQL

CREATE PROCEDURE myProcName
(
  @ID INT,
  @ItemName VARCHAR(50), -- or what ever as per your DB design
  @Price MONEY
)
AS
BEGIN
   IF EXISTS(SELECT ID from Item where ID=@ID)
   BEGIN
      DELETE FROM Item WHERE ID=@ID;

      INSERT INTO Item(ItemName, Price, ID)  
          VALUES (@ItemName, @Price, @ID)
   END
   --else condition goes here
END

Here the statements are properly blocked with BEGIN..END keywords.

However, you can also stick to your parameterised query approach in which case you need to make multiple calls to the DB.
The approach could be something like-
--Check the existance of data in the table with just the SELECT query and get the true/false value.
--If you get true value then go ahead to execute the DELETE command and then the INSERT command in another command object.

Hope, it helps :)

Posted 25-Oct-16 1:56am

Suvendu Shekhar Giri

Solution 5

You have missed to write begin and end block for if exists.
refer the following query

"IF EXISTS(SELECT ID from Item where ID=@ID) begin" +
"DELETE FROM Item WHERE ID=@ID;" +
"INSERT INTO Item(" +
"ItemName, " +
"Price, " +
"ID) VALUES (" +
"@ItemName, " +
"@Price, " +
"@ID) end ";

Posted 26-Oct-16 3:48am

Gaurav Raosaheb Raut

Solution 4

we cant achieve this using inline query or normal SQL like what you tried better you go with PlSQL stored procedure

SQL

CREATE PROCEDURE PrCheckAndSave
(
  @ID INT,
  @ItemName VARCHAR(100),
  @Price FLOAT
)
AS
IF EXISTS(SELECT ID from Item where ID=@ID)
 BEGIN 
       DELETE FROM Item WHERE ID=@ID

      INSERT INTO Item(ItemName,Price,ID) VALUES (@ItemName,@Price,@ID)
END

Just Call the PrCheckAndSave SPC into your application that may help you

Posted 25-Oct-16 21:00pm

SanthoshBabu Mahimairaj

Solution 6

Why you want to delete you can update that particular record

SQL

CREATE PROCEDURE <ProcedureName>
(
  @Id INT,
  @ItemName VARCHAR(50), 
  @Price MONEY
)
AS
BEGIN
   IF EXISTS(SELECT ID from Item where ID=@ID)
   BEGIN

   Update Item SET ItemName=@ItemName, Price=@Price WHERE ID=@Id

   END
END

Posted 27-Oct-16 2:26am

Vaibhav-Systematix

Solution 8

HI,
Your procedure should be consists of two operations
1. Update
2. Insert

if(exists)
Begin
Update statement
end

else
Begin
Insert statement
end

Posted 1-Nov-16 1:03am

Harpreet05Kaur

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Accepted Answer · 2016-10-25T01:35:00

Solution 1

Why not just use UPDATE?

C#

string query = "UPDATE Item SET 
               "ItemName = @ItemName, " +
               "Price = @Price " +
               "where ID = @ID";

Posted 25-Oct-16 1:35am

OriginalGriff