Follow @dporton

Learn ODBC : Updating records

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_FMROOTMaster" + 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 + "
Name Not Found"     oReturnObject.Remove("ID")        oReturnObject.Add("ID", -1)    Else     sInfo = sInfo + "
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 + "
Error :
"  sInfo = sInfo + Err.Description + "
"  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_FMROOTMaster" + 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 + "
Error :
"  sInfo = sInfo + Err.Description + "
"  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.

Update Complete

The full code for this article can be found in :http://CodeCorner.SmaterDimensions.Com @ Update a record in a SQL Table

Leave a Comment

%d bloggers like this: