CODEWITHSUNDEEP
c#sqlsql-serverwinforms
Marek
Marek

Reputation: 3575

SqlCommand If exist - update , if not exist - insert into

Hello I have got this following statement that I would like it to INSERT INTO if there wasn't found any value with id = 1

If it exist I would like to update

I inspired by this: 

    UPDATE Table1 SET (...) WHERE Column1='SomeValue'
IF @@ROWCOUNT=0
    INSERT INTO Table1 VALUES (...)

But getting this Exception: Incorrect syntax near (.

But I cant find where do I make a mistake , would someone help me solve this out please?

 string sqlcom = "UPDATE firma SET (firma=@firma,ulice=@ulice,mesto=@mesto,psc=@psc,ico=@ico,dico=@dico,dph=@dph,sdph=@sdph,upo1=@upo1,raz1=@raz1) WHERE id='1' IF @@ROWCOUNT=0 INSERT INTO firma (firma,ulice,mesto,psc,ico,dico,dph,sdph,upo1,raz1) VALUES (@firma,@ulice,@mesto,@psc,@ico,@dico,@dph,@sdph,@upo1,@raz1)";

                SqlCommand prikaz =
                    new SqlCommand(sqlcom, spojeni);
                prikaz.Parameters.AddWithValue("@firma", ffirma.Text);
                prikaz.Parameters.AddWithValue("@ulice", fulice.Text);
                prikaz.Parameters.AddWithValue("@mesto", fmesto.Text);
                prikaz.Parameters.AddWithValue("@psc", fpsc.Text);
                prikaz.Parameters.AddWithValue("@ico", fico.Text);
                prikaz.Parameters.AddWithValue("@dico", fdico.Text);
                prikaz.Parameters.AddWithValue("@dph", fdph.Text);
                prikaz.Parameters.AddWithValue("@sdph", fsdph.Text);
                prikaz.Parameters.AddWithValue("@raz1", fraz1.Text);
                prikaz.Parameters.AddWithValue("@upo1", fupo1.Text);

                spojeni.Open();
                prikaz.ExecuteNonQuery();
                spojeni.Close();

Upvotes: 2

Views: 458

Answers (4)

Steve
Steve

Reputation: 216243

Remove the open parenthesis after SET

UPDATE Firma SET firma=@firma ....

And, of course, the closing one after the list of fields updated.

However, a semicolon before the IF should also be added to allow a distinction between the two statements 

UPDATE ...... WHERE id='1'; IF @@ROWCOUNT ......

Upvotes: 4

AlexB
AlexB

Reputation: 7416

IMHO, you first need to correctly indent your SQL statement. It will be easier to see where is the mistake.

It is probably a parenthesis probelm. Try :

IF (@@ROWCOUNT=0) BEGIN ... END

Upvotes: 0

podiluska
podiluska

Reputation: 51494

Have you considered using the MERGE Sql Command?

That will allow you to do an update, if a record matches certain conditions, otherwise do an insert.

Something like 

MERGE firma AS target
USING (SELECT @SomeValue, @firma, @ulice...) AS source (ID, Firma, Ulice)
ON (target.ID= source.ID)
WHEN MATCHED THEN 
    UPDATE SET 
      Firma = source.Firma,
      Ulice  = source.Ulice, 
      ...
WHEN NOT MATCHED THEN   
     INSERT (ID, Firma, Ulice, ...)
     VALUES (source.ID, source.Firma, source.Ulice, ....)

http://technet.microsoft.com/en-us/library/bb510625.aspx

Upvotes: 2

David M
David M

Reputation: 72840

You need to separate your SQL statements. Try a semi-colon before your IF statement.

Upvotes: 1

Related Questions