0

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Josh Fletcher
  • 178
  • 1
  • 1
  • 13
  • 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 Nov 27 '17 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 Nov 27 '17 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 Nov 27 '17 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 Nov 27 '17 at 23:53
  • [SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection - check out [Little Bobby Tables](https://xkcd.com/327/) – marc_s Nov 28 '17 at 05:32

1 Answers1

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.

Nishant Shrivastava
  • 389
  • 1
  • 3
  • 17
  • 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 + "')"); – Josh Fletcher Nov 27 '17 at 23:27