Search code examples
sqlvbscriptado

explain differences between two different updates

I have 2 different update queries, one I created:

UPDATE_QUERY = ""
UPDATE_QUERY = UPDATE_QUERY & "UPDATE ORGBHVTasklist "
UPDATE_QUERY = UPDATE_QUERY & "SET TaskID = NULL "
UPDATE_QUERY = UPDATE_QUERY & "WHERE TaskID = ? "

set cmd = server.CreateObject("ADODB.Command")
cmd.ActiveConnection = objCon
cmd.CommandText = UPDATE_QUERY

set paramID = cmd.CreateParameter("@id", 3, 1, , taskID)

cmd.Parameters.Append paramID

cmd.execute

my colleague preferred this one:

UPDATE_QUERY = ""
UPDATE_QUERY = UPDATE_QUERY & "SELECT taskid "
UPDATE_QUERY = UPDATE_QUERY & "FROM ORGBHVTasklist "
UPDATE_QUERY = UPDATE_QUERY & "Where taskID = "&taskID&" "
    
set objUpdate = Server.CreateObject("ADODB.Recordset")
objUpdate.CursorType = 2
objUpdate.LockType = 3
objUpdate.Open UPDATE_QUERY, objCon

objUpdate.Fields("taskid") = NULL
objUpdate.Update

objUpdate.close
Set objUpdate = nothing

He can't explain which one should be better and neither can I. The only difference I have, is that the update on the second query crashes when there are no results to update.

Can anyone explain what's the difference?

Solution

  • To sum it up:

    The first approach (parameterized query/prepared statement) is best, because it

    1. is easy: automagical quoting and formatting of the parameters
    2. can be more efficient: the DBMS may optimize
    3. avoids fetching and storing the resultset (@Ansgar)
    4. scales better: would work for many records without change
    5. guards against SQL-Injection

    The second approach is worse because not having features 1 - 5 and worst/naive because not checking for an empty recordset (@peter, @Cageman).