Learn ODBC : Updating records

Thursday, February 25th, 2010 | Code Corner, IBM SPSS Professional, Learn ODBC, Scripting, mrStudio

Now that we know how to connect , find , insert and delete records there is one more action that we will write about in this series of articles on ODBC. As well as all the above we will need at some point in time to know how to update a record. This article will show you how to do just that.

As before we will take the basic routing created in the previous examples,

Routing(Web2)
Dim oInfo
Set oInfo = CreateObject("Scripting.Dictionary")
oInfo.Add("Message","")
oInfo.Add("ID",-1)
Set oInfo = FindVDataRecord("MyServer", _
     "ADO","VDATA","Respondent.Serial", _
     "Name = 'Test'","myAdmin","ABCD",oInfo)
ConnectionInfo.Label.Inserts["Info"] = Ctext(oInfo.item["Message"])
ConnectionInfo.Show()
If ( oInfo.item["ID"] <> -1  )  Then
 ' TODO�
End If
Name.Ask()
Function FindVDataRecord(sServer,sDatabase,sTable,sKey,sWhere, _
                           sUser,sPassword,oReturnObject)
Dim oConnection, oRecordset
Dim sInfo , sConnection, sSQL
On Error Goto ErrorMessage
   Set oConnection = CreateObject("ADODB.Connection")
   sConnection = "Provider=mrOleDB.Provider.2;Data Source=mrRdbDsc2;"
   sConnection = sConnection + "Location='Provider=SQLOLEDB.1;Password=" + sPassword
   sConnection = sConnection + ";Persist Security Info=True;User ID="
   sConnection = sConnection + sUser + ";Initial Catalog=" + sDatabase
   sConnection = sConnection + ";Data Source=" + sServer
   sConnection = sConnection + "';Initial Catalog=\\" + sServer
   sConnection = sConnection + "\SPSSMR_FMROOT\Master\" + sDatabase + "\"
   sConnection = sConnection + sDatabase + ".mdd;MR Init Project=" + sDatabase

   oConnection.Open(sConnection)
    If ( oConnection.State = 1 ) Then
      sInfo = "Connection to the Database worked"
      Set oRecordset = CreateObject("ADODB.Recordset")
      sSQL = "SELECT " + sKey + " FROM " + sTable + " WHERE " + sWhere
   oRecordSet.Open(sSQL,oConnection,3,1)
   If ( oRecordSet.EOF = true and oRecordset.BOF = true ) Then
    sInfo = sInfo + "<br />Name Not Found"
    oReturnObject.Remove("ID")
       oReturnObject.Add("ID", -1)
   Else
    sInfo = sInfo + "<br />Name Found !!!"
    oReturnObject.Remove("ID")
       oReturnObject.Add("ID", oRecordset[sKey].Value)
   End if
   oRecordset.Close()
   oReturnObject.Remove("Message")
      oReturnObject.Add("Message", sInfo)
    Else
      oReturnObject.Remove("Message")
      oReturnObject.Add("Message", "Connection to the Database failed")
   End If

   oConnection.Close()
   Goto EndOfFunction

ErrorMessage:
    sInfo = sInfo + "<br />Error : <br />"
 sInfo = sInfo + Err.Description + "<br />"
 sInfo = sInfo + CTEXT(Err.LineNumber)
    oReturnObject.Remove("Message")
 oReturnObject.Add("Message", sInfo)
EndOfFunction:
   Set oConnection = Null
   Set FindVDataRecord = oReturnObject
End Function

ok , so once this is typed in and working the next thing we should look at it adding in the code to call the update function. Locate the ‘ TODO in the above code and replace it with the following but of code.

' Lets Update the Record
 Set oInfo = UpdateVDataRecord("MyServer","ADO","VDATA", _
              "Name = 'DELETE'","name is null","MyAdmin","ABCD",oInfo)
 ConnectionInfo.Label.Inserts["Info"] = Ctext(oInfo.item["Message"])
 ConnectionInfo.Show()

Hopefully from this you will see that we have a function called UpdateVDataRecord and the first parameter that we pass into it is the name of our server , followed by the database name and then the table name. This like all our other function does not really change however the next parameter is the actual update string. For more information about the SQL update statement the click here. All we are going to do in this example is to change the Name value for the record that we found previously with the find function. To do this we will pass in the string

"Name = 'DELETE'"

which means for all the records that meet the specified criteria , which we will do in the next variable, change the value of the name column to the words ‘DELETE’. So how do we specify the criteria of the records to update , well we do that with the next parameter that says

"Respondent.Serial = " + CTEXT(oInfo.item["ID"])

Which means for every record in the survey that does not have a value in the Name field. The final remaining parameters , as before , are the username, password and info object that will be used to login, perform the action and return the results.

Now that we have the signature setup we can create the actual function. Once again type in this new function.

Function UpdateVDataRecord(sServer,sDatabase,sTable,sUpdate,sWhere,sUser,sPassword,oReturnObject)
Dim oConnection, oRecordset
Dim sInfo , sConnection, sSQL

On Error Goto ErrorMessage
   Set oConnection = CreateObject("ADODB.Connection")

   sConnection = "Provider=mrOleDB.Provider.2;Data Source=mrRdbDsc2;"
   sConnection = sConnection + "Location='Provider=SQLOLEDB.1;Password=" + sPassword
   sConnection = sConnection + ";Persist Security Info=True;User ID="
   sConnection = sConnection + sUser + ";Initial Catalog=" + sDatabase
   sConnection = sConnection + ";Data Source=" + sServer
   sConnection = sConnection + "';Initial Catalog=\\" + sServer
   sConnection = sConnection + "\SPSSMR_FMROOT\Master\" + sDatabase + "\"
   sConnection = sConnection + sDatabase + ".mdd;MR Init Project=" + sDatabase

   oConnection.Open(sConnection)

    If ( oConnection.State = 1 ) Then

      ' TODO

   oReturnObject.Remove("Message")
      oReturnObject.Add("Message", "Connection to the Database worked, Starting to Update.")
   Else
      oReturnObject.Remove("Message")
      oReturnObject.Add("Message", "Connection to the Database failed")
    End If
   oConnection.Close()
Goto EndOfFunction
ErrorMessage:
    sInfo = sInfo + "<br />Error : <br />"
 sInfo = sInfo + Err.Description + "<br />"
 sInfo = sInfo + CTEXT(Err.LineNumber)
    oReturnObject.Remove("Message")
 oReturnObject.Add("Message", sInfo)
EndOfFunction:
   Set oConnection = Null
   Set UpdateVDataRecord = oReturnObject
End Function

The above bit of code does the same as all the other functions, it first creates the connection string to the desired server and table , which in this case is a data collection server table. Once a valid connection is established the desired action is taken otherwise we report the fact that the connection failed and exit the function. So how do we do the update , well locate the ‘TODO in your script and replace it with the following.

sSQL = "UPDATE " + sTable + " SET " + sUpdate + " WHERE " + sWhere
oConnection.Execute(sSQL)

so this time we are going to create a string that holds at first the words UPDATE and then we add the table name to it followed by the SQL Command SET and then our update statement which in this case is “Name = ‘DELETE’” and then we add the SQL Command WHERE followed by the where clause that will pull out all the required records. This is what our final string would look like,

UPDATE VDATA SET Name = 'DELETE' WHERE Respondent.serial = 1

Once we have this string we need to execute or run it so this can be done with the Execute statement off the connection object. So how do we know if this has worked, well if your code is correct then you should get the following message displayed on the screen.