In this example we will show you how to update a record in a VDATA table.
This is the routing we used:
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
' Lets Update the Record
Set oInfo = UpdateVDataRecord("MyServer","ADO","VDATA","Name = 'DELETE'","Respondent.Serial = " + CTEXT(oInfo.item["ID"]),"MyAdmin","ABCD",oInfo)
ConnectionInfo.Label.Inserts["Info"] = Ctext(oInfo.item["Message"])
ConnectionInfo.Show()
End If
Name.Ask()
Function InsertVDataRecord(sServer,sDatabase,sTable,sFields,sValues,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
sSQL = "INSERT INTO " + sTable + " (" + sFields + ") VALUES " + "(" + sValues + ")"
oConnection.Execute(sSQL)
oReturnObject.Remove("Message")
oReturnObject.Add("Message", "Connection to the Database worked, Insert Complete")
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 InsertVDataRecord = oReturnObject
End 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
sSQL = "UPDATE " + sTable + " SET " + sUpdate + " WHERE " + sWhere
oConnection.Execute(sSQL)
oReturnObject.Remove("Message")
oReturnObject.Add("Message", "Connection to the Database worked, Update Complete.")
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
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_FMROOT\Master" + sDatabase + ""
sConnection = sConnection + sDatabase + ".mdd;MR Init Project=" + sDatabase
oConnection.Open(sConnection)
If ( oConnection.State = 1 ) Then
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 + "Error : "
sInfo = sInfo + Err.Description + ""
sInfo = sInfo + CTEXT(Err.LineNumber)
oReturnObject.Remove("Message")
oReturnObject.Add("Message", sInfo)
EndOfFunction:
Set oConnection = Null
Set DeleteVDataRecord = oReturnObject
End Function
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
End Routing