Learn ODBC : Deleting a records

In this article we are going to learn how to delete a record with ODBC. We will use the same base code as we have done in the previous examples and just add the additional delete function.

The first thing we need to do is make sure we have full working code from the previous articles.

Routing(Web2)
Dim oInfo
Set oInfo = CreateObject("Scripting.Dictionary")
oInfo.Add("Message","")
oInfo.Add("ID",-1)
Set oInfo = FindVDataRecord("SERVER","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
        '  DO SOMETHING HERE
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 :" + mr.NewLine
     sInfo = sInfo + Err.Description + "" + mr.NewLine
     sInfo = sInfo + CTEXT(Err.LineNumber)
     oReturnObject.Remove("Message")
     oReturnObject.Add("Message", sInfo)
EndOfFunction:
Set oConnection = Null
Set FindVDataRecord = oReturnObject
End Function
End Routing

Next we need to decide the signature of the delete function, place the following underneath the words ‘ DO SOMETHING HERE

' Lets Delete the Record
Set oInfo = DeleteVDataRecord("SERVERNAME","ADO","VDATA","Respondent.Serial =
                 " + CTEXT(oInfo.item["ID"]),"myAdmin","ABCD",oInfo)
ConnectionInfo.Label.Inserts["Info"] = Ctext(oInfo.item["Message"])
ConnectionInfo.Show()

By adding this code we are saying that when we have found the record we are looking for we will delete it, so if we find a record the first thing we will do is call the delete function that we pass in the standard connection information ,Server name ,Database name and Table name, once again we are working with Data Collection data so it is the VDATA table that we will be deleting from. Next we pass in an sql where clause. The clause we are going to use will allow us to pull out the record we found for example.

"Respondent.serial = 12"

But it could be anything you like. For example, if you wanted to delete all the rows that had their name value set to “Test” then your statement would look like this,

"Name = 'Test'"

You may notice that the first where clause does not use ” around the value , this is because the Name variable is a text variable and needs the ”. If you would like to find out more about sql where clauses then Click Here. After the where clause we again pass in the user information and the info item that will be used to store the return message.

As we saw in the last two articles the next two lines take the message that has been return via the info object and display or show it on the screen. If all goes well what we would see at this stage is a message telling us the record or records have been deleted.

Ok so we have created the code to call the delete function but now we need to create the function its self. The best way for us to do this is to take a copy of the Find function and modify it to look like this.

Function DeleteVDataRecord(sServer,sDatabase,sTable,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
     ' DO SOMETHING HERE
     sSQL = "DELETE FROM " + sTable + " WHERE " + sWhere
     oConnection.Execute(sSQL)
     oReturnObject.Remove("Message")
     oReturnObject.Add("Message", "Connection to the Database worked, Record deleted.")
Else
     oReturnObject.Remove("Message")
     oReturnObject.Add("Message", "Connection to the Database failed")
End If
oConnection.Close()
Goto EndOfFunction
ErrorMessage:
     sInfo = sInfo + mr.NewLine + "Error :"
     sInfo = sInfo + Err.Description + mr.NewLine
     sInfo = sInfo + CTEXT(Err.LineNumber)
     oReturnObject.Remove("Message")
     oReturnObject.Add("Message", sInfo)
EndOfFunction:
Set oConnection = Null
Set DeleteVDataRecord = oReturnObject
End Function

You should be able to see that the code is very similar, we make the connection string , see if we can connect, if not we will return a message saying we cannot if we can connect we will delete the record or records (depending on the where clause ) and then display a message telling us the delete has worked.

The lines of code that do the actual delete are the following ones,

sSQL = "DELETE FROM " + sTable + " WHERE " + sWhere
oConnection.Execute(sSQL)

and unlike before , we do not have an ado recordset, this time we have just run the SQL syntax from the connection string. We can do this because we don’t want anything back from the command , we just want to execute it.

And there you have it a generic function to delete records from a vdata table via a Data Collection Survey.

Leave a Comment

%d bloggers like this: