This code will show you how to connect to a data collection survey and find a specific record. Once found the function will return the ID of the found record.
This is the metadata
Metadata(en-AU, Question, Label)
ConnectionInfo "{Info}" info;
Name "Name" text;
End Metadata
This is the Routing
Routing(Web1)
Dim oInfo
Set oInfo = CreateObject("Scripting.Dictionary")
oInfo.Add("Message","")
oInfo.Add("ID",-1)
Set oInfo = FindVDataRecord("SERVERNAME","ADO","VDATA","Respondent.Serial","Name = 'Test'","LOGIN","PASSWORD",oInfo)
ConnectionInfo.Label.Inserts["Info"] = Ctext(oInfo.item["Message"])
ConnectionInfo.Show()
If ( oInfo.item["ID"] <> -1 ) Then
' Do something
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 + "Name Not Found"
oReturnObject.Remove("ID")
ReturnObject.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
Like this:
Like Loading...
