Hi gkoutsiv,

Refer the code.

C#

private static void UpdatePreference(int locationId, int preference, int id)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Parameters.AddWithValue("@Id", locationId);
                cmd.Parameters.AddWithValue("@Preference", preference);
                string query = "";
                if (locationId == id)
                {
                    query = "UPDATE HolidayLocationsPref SET Preference = @Preference, UpdatedBy = @UserName WHERE Id = @Id; ";
                    query = query + "INSERT INTO HolidayLocationsPref2 (Location,Preference,UpdatedBy) VALUES(@Id,@Preference,@UserName)";
                    cmd.Parameters.AddWithValue("@UserName", HttpContext.Current.Session["User"].ToString());
                }
                else
                {
                    query = "UPDATE HolidayLocationsPref SET Preference = @Preference WHERE Id = @Id";
                }
                cmd.CommandText = query;
                cmd.CommandType = CommandType.Text;
                cmd.Connection = con;
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
    }
}

VB.Net

Private Shared Sub UpdatePreference(ByVal locationId As Integer, ByVal preference As Integer, ByVal id As Integer)
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand()
            Using sda As SqlDataAdapter = New SqlDataAdapter()
                cmd.Parameters.AddWithValue("@Id", locationId)
                cmd.Parameters.AddWithValue("@Preference", preference)
                Dim query As String = ""
                If locationId = id Then
                    query = "UPDATE HolidayLocationsPref SET Preference = @Preference, UpdatedBy = @UserName WHERE Id = @Id; "
                    query = query + "INSERT INTO HolidayLocationsPref2 (Location,Preference,UpdatedBy) VALUES(@Id,@Preference,@UserName)"
                    cmd.Parameters.AddWithValue("@UserName", HttpContext.Current.Session("User").ToString())
                Else
                    query = "UPDATE HolidayLocationsPref SET Preference = @Preference WHERE Id = @Id"
                End If

                cmd.CommandText = query
                cmd.CommandType = CommandType.Text
                cmd.Connection = con
                con.Open()
                cmd.ExecuteNonQuery()
                con.Close()
            End Using
        End Using
    End Using
End Sub

If you want to insert the Location Name instead of Id refer below code.

C#

[WebMethod]
public static void UpdatePreference(List<HlidayData> data)
{
    int preference = 1;
    foreach (var item in data)
    {
        UpdatePreference(item.LocationId, preference, item.Id, item.Location);
        preference++;
    }
}

private static void UpdatePreference(int locationId, int preference, int id, string location)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Parameters.AddWithValue("@Id", locationId);
                cmd.Parameters.AddWithValue("@Preference", preference);
                string query = "";
                if (locationId == id)
                {
                    query = "UPDATE HolidayLocationsPref SET Preference = @Preference, UpdatedBy = @UserName WHERE Id = @Id; ";
                    query = query + "INSERT INTO HolidayLocationsPref2 (Location,Preference,UpdatedBy) VALUES(@Location,@Preference,@UserName)";
                    cmd.Parameters.AddWithValue("@Location", location);
                    cmd.Parameters.AddWithValue("@UserName", HttpContext.Current.Session["User"].ToString());
                }
                else
                {
                    query = "UPDATE HolidayLocationsPref SET Preference = @Preference WHERE Id = @Id";
                }
                cmd.CommandText = query;
                cmd.CommandType = CommandType.Text;
                cmd.Connection = con;
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
    }
}

VB.Net

<WebMethod()>
Public Shared Sub UpdatePreference(ByVal data As List(Of HlidayData))
    Dim preference As Integer = 1
    For Each item In data
        UpdatePreference(item.LocationId, preference, item.Id, item.Location)
        preference += 1
    Next
End Sub

Private Shared Sub UpdatePreference(ByVal locationId As Integer, ByVal preference As Integer, ByVal id As Integer, ByVal location As String)
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand()
            Using sda As SqlDataAdapter = New SqlDataAdapter()
                cmd.Parameters.AddWithValue("@Id", locationId)
                cmd.Parameters.AddWithValue("@Preference", preference)
                Dim query As String = ""
                If locationId = id Then
                    query = "UPDATE HolidayLocationsPref SET Preference = @Preference, UpdatedBy = @UserName WHERE Id = @Id; "
                    query = query + "INSERT INTO HolidayLocationsPref2 (Location,Preference,UpdatedBy) VALUES(@Location,@Preference,@UserName)"
                    cmd.Parameters.AddWithValue("@Location", location)
                    cmd.Parameters.AddWithValue("@UserName", HttpContext.Current.Session("User").ToString())
                Else
                    query = "UPDATE HolidayLocationsPref SET Preference = @Preference WHERE Id = @Id"
                End If

                cmd.CommandText = query
                cmd.CommandType = CommandType.Text
                cmd.Connection = con
                con.Open()
                cmd.ExecuteNonQuery()
                con.Close()
            End Using
        End Using
    End Using
End Sub