Reputation: 31
string searched = TextBox1.Text; // for example, text is 4477
...
sorgu.CommandText = "IF EXISTS(SELECT * FROM [Pins] WHERE Pin =' " + searched.ToString() + " ') BEGIN UPDATE [Pins] SET SAY= SAY+1, Pin = ' " + searched.ToString() + " ' END ELSE BEGIN INSERT INTO Pins(SAY,Pin) VALUES (+1,' " + searched.ToString() + " ') END";
...
I am using SAY for counting the number of searches.
This code is changing all records on column (Pins) to searched text.
Where/What is my fault?
Upvotes: 0
Views: 4365
Reputation: 6979
You missed the WHERE
clause.
I would also prefer using Parameters
instead of string concatenated query (which makes your code open to SQL injection attacks)
string searched = TextBox1.Text; // for example, text is 4477
sorgu.CommandText = "IF EXISTS(SELECT * FROM [Pins] WHERE Pin = @searched) BEGIN UPDATE [Pins] SET SAY=SAY+1 WHERE Pin = @searched END ELSE BEGIN INSERT INTO Pins(SAY,Pin) VALUES (1, @searched) END";
sorgu.Parameters.AddWithValue("@searched", TextBox1.Text);
Upvotes: 0
Reputation: 76
You need to have a where to not update all records. This would fix it.... but is a horrible query.
IF EXISTS(SELECT * FROM [Pins] WHERE Pin = 'searched.ToString()')
BEGIN
UPDATE [Pins] SET SAY = SAY + 1
WHERE [Pin] = 'searched.ToString() '
END
ELSE
BEGIN
INSERT INTO Pins(SAY, Pin) VALUES (1, 'searched.ToString()')
END
Upvotes: 1
Reputation: 31795
In pseudo code, you're saying
IF EXISTS(SELECT Statement) UPDATE ALL ROWS
The correct way to do this with EXISTS is
UPDATE TABLE
WHERE EXISTS(Correlated SELECT Statement)
Upvotes: 1