Query is producing deadlocks

[bulgarian388]

Hey guys,

So I have what I thought was a stable query, but it will randomly produce deadlocks. The table it's updating is not absolutelly mission critical, but the query will end up being replicated to a couple of other websites, and I can only assume it will produce the same random deadlocks with them, thus I would like to get it resolved.

So here is the code, keep in mind this is being executed via a Linq to Sql class inside of an ASP.NET MVC website, all code is in C#:

public static void SetTraffic(int Sitecode) {
if (Sitecode != 1000) {
try {
using (DataContext db = new DataContext()) {
db.ExecuteCommand("DECLARE @Count INT; SET @Count = ISNULL((SELECT TOP 1 [TrafficCount] FROM [Traffic] WITH (NOLOCK) WHERE ([Sitecode] = {0}) AND ([DateStamp] BETWEEN {1} AND {2}) ORDER BY [TrafficCount] DESC), 0) IF @Count = 0 BEGIN: INSERT INTO [Traffic]([Sitecode], [TrafficCount], [DateStamp]) VALUES({0}, 1, GETDATE()) END ELSE BEGIN: UPDATE [Traffic] SET [TrafficCount] = (@Count + 1) WHERE ([Sitecode] = {0}) AND ([DateStamp] BETWEEN {1} AND {2}) END", Sitecode, DateTime.Parse(Date + " 00:00:00.000"), DateTime.Parse(Date + " 23:59:59.999"));
};
} catch (Exception ex) {
Event.Log(ex, string.Empty);
};
};
}

Thanks in advance for anyone who would be able to help me!

EDIT: The database is Sql Server 2000, and for those who don't know what ASP.NET MVC is, it's pretty much running .NET 3.5 SP1.