When using the advanced script I get the following error:
There was a problem querying the database:
42000:[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.
I'm using MM 2.5.2.951
Here's the code I used
Code: Select all
Sub SetPlayCounter
' Define variables
Dim list, itm, i, newPlayCounter, mb, progress
'Get a new value for the Play Counter from the user
newPlayCounter = InputBox("Enter the new value for the Played field", "Set Play Counter")
'If Canceled, exit
If newPlayCounter = "" Then
Exit Sub
End If
'Check that the text entered is a valid parameter. Inform user if it isn't.
If Not IsNumeric(newPlayCounter) Then
mb = MsgBox("You did not enter a number. Please try again.",0,"Error")
Exit Sub
ElseIf newPlayCounter < 0 Then
mb = MsgBox("Only positive numbers or 0 are allowed. Please try again.",0,"Error")
Exit Sub
End If
' Get list of selected tracks from MediaMonkey
Set list = SDB.SelectedSongList
If list.count=0 Then
Set list = SDB.AllVisibleSongList
End If
'No songs selected?
If list.count = 0 Then
mb = MsgBox("No songs were selected. Please select some songs and try again",0,"Error")
Exit Sub
End If
'Set up progress
Set Progress = SDB.Progress
Progress.Text = "Writing New Play Counters..."
Progress.MaxValue = list.count
'Process all selected tracks
For i=0 To list.count-1
Set itm = list.Item(i)
'Set the Play Counter
itm.PlayCounter = newPlayCounter
itm.UpdateDB
Progress.value = i+1
If Progress.terminate Then
Exit For
End if
Next
Set Progress = nothing
End Sub
Sub ResetPlayCounter
'This is drastic, so require confirmation from user
Dim yesimsure
yesimsure = InputBox("Are you sure you want to reset the play counter for all tracks in the Library? Write YES to proceed. Note: You will have to refresh the view (F5) in order to see the changes.", "Reset Play Counter")
If yesimsure <> "YES" Then
Exit Sub
End If
'Reset the counter...
SDB.Database.ExecSQL("UPDATE SONGS SET PLAYCOUNTER = 0 WHERE TRUE=TRUE")
End Sub
Sub RestorePlayCounter
'This is drastic, so require confirmation from user
Dim yesimsure
yesimsure = InputBox("Are you sure you want to restore the play counter to the number of times each track was played in MediaMonkey? Write YES to proceed. Note: You will have to refresh the view (F5) in order to see the changes.", "Restore Play Counter")
If yesimsure <> "YES" Then
Exit Sub
End If
Dim Str1, Str2, Str3, Str4, Str5
'Create a temporary table which stores song IDs and the number of times each song was played
Str1 = "SELECT Songs.ID, timesPlayed INTO updateCounter "
Str2 = "FROM Songs, [SELECT Played.idSong as SID, Count(Played.idSong) AS timesPlayed FROM Played GROUP BY Played.idSong]. AS subq "
Str3 = "WHERE Songs.Id =subq.SID"
SDB.Database.ExecSQL(Str1 & Str2 & Str3)
'Use the table just created to update the playCounter field in the songs table
Str4 = "UPDATE Songs, updateCounter SET songs.playcounter = updateCounter.timesPlayed "
Str5= "WHERE songs.id=updateCounter.id"
SDB.Database.ExecSQL(Str4 & Str5)
'Delete temporary table
SDB.Database.ExecSQL("DROP TABLE updateCounter")
End Sub
Function Min(x, y)
If x<y Then
Min = x
Else
Min = y
End If
End Function
Function Max(x, y)
If x>y Then
Max = x
Else
Max = y
End If
End Function
Sub SetPlayCounterAdvanced
' Define variables
Dim list, itm, i, userInput, newC , mb, progress, C
'Get a new value for the Play Counter from the user
userInput = InputBox("Enter a formula for the new play counter. Use C to refer to the current value. Examples: 0, C+1, Max(1,C).", "Set Play Counter (Advanced)")
'If Canceled, exit
If userInput = "" Then
Exit Sub
End If
' Get list of selected tracks from MediaMonkey
Set list = SDB.SelectedSongList
If list.count=0 Then
Set list = SDB.AllVisibleSongList
End If
'No songs selected?
If list.count = 0 Then
mb = MsgBox("No songs were selected. Please select some songs and try again",0,"Error")
Exit Sub
End If
'Set up progress
Set Progress = SDB.Progress
Progress.Text = "Writing New Play Counters..."
Progress.MaxValue = list.count
'Process all selected tracks
For i=0 To list.count-1
Set itm = list.Item(i)
C = itm.playCounter
'Evaluate user input
newC = Eval(userInput)
'Set the Play Counter
itm.PlayCounter = SDB.database.execSQL("UPDATE Songs SET PlayCounter=" & newValue & " WHERE Id=" & itm.songID)
itm.playCounter = itm.playCounter + 1
itm.updateDB
Progress.value = i+1
If Progress.terminate Then
Exit For
End if
Next
Set Progress = nothing
End Sub