Adding to the database but not having duplicate keys

Question

I've created an ASP.NET MVC web application and am adding to the database but am attempting to remove duplicates when adding userSettings to my site.

My SQL code in a C# controller:

db.Database.ExecuteSqlCommand(@"INSERT INTO [dbo].[Settings] ([SiteID],
[SiteName]) VALUES (" + settings.SiteID + ",'" + settings.SiteName + "' WHERE 
NOT EXISTS (SELECT SiteID FROM [dbo].[Settings] WHERE ([SiteID]) = " + 
settings.SiteID + ")");

I am getting this error:

System.Data.SqlClient.SqlException occurred
HResult=0x80131904
Message=Incorrect syntax near the keyword 'WHERE'.
Source=.Net SqlClient Data Provider
StackTrace:
Cannot evaluate the exception stack trace

Thanks

I think the error is happening in the nested selected statement....well... depends on type of SiteId. If it is numeric, then the second where clause is adding single quotes around it, which is the problem. If the type is string, then the first where statement is missing the single quotes. — Sparrow
– Sparrow, Commented Nov 27, 2017 at 22:27
You were correct about it being an int so I removed the single quote marks from second where statement but still same error. I have updated the question above to reflect the change. Thanks for that improvement though — Josh Fletcher
– Josh Fletcher, Commented Nov 27, 2017 at 22:58
Oh you are missing a closing ')' before the first where clause too. Also, in the second where clause, you don't need to put the SiteId inside ( and ) — Sparrow
– Sparrow, Commented Nov 27, 2017 at 23:00
I would suggest creating the SQL command as a string variable, then when debugging copy and paste it into SSMS to test. — Aron
– Aron, Commented Nov 27, 2017 at 23:53
SQL Injection alert - you should not concatenate together your SQL statements - use parametrized queries instead to avoid SQL injection - check out Little Bobby Tables — marc_s
– marc_s, Commented Nov 28, 2017 at 5:32

Nishant Shrivastava · Accepted Answer · 2017-11-27 22:31:08Z

0

Have a look at this SO link insert into values with where clause

There is a solution to avoid duplicate records while inserting them in the database.

answered Nov 27, 2017 at 22:31

Nishant Shrivastava

3991 gold badge4 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Josh Fletcher Over a year ago

Worked Perfectly. For reference the result was : db.Database.ExecuteSqlCommand(@"IF NOT EXISTS(SELECT 1 FROM [dbo].[Settings] WHERE SiteID = " + settings.SiteID + ") INSERT INTO [dbo].[Settings] ([SiteID], [SiteName]) VALUES (" + settings.SiteID + ",'" + settings.SiteName + "')");

Collectives™ on Stack Overflow

Adding to the database but not having duplicate keys

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related