Follow @dporton

JustCode : Delete a vdata record

This sub shows us how to delete a vdata record from a survey. It has 3 parms that you need to pass in. You will also need to enter a valid SQL user login & password.

 
' sSurvey: The name of the survey that is to be used.
' sID: The Respondent.serial that is to be deleted.
' sServer: The name or the IP of the SQL server
' ****************************************
' Designed by : Smarter Dimensions
' Last Updated : 8th July 2009
' Sub to delete a vdata record
' ****************************************

Sub DeleteVDataRecord(sSurvey,sID,sServer)

    Set oConnection = CreateObject("ADODB.Connection")

    oConnection.ConnectionString = "Provider=mrOleDB.Provider.2;Data Source=mrRdbDsc2;Location='Provider=SQLOLEDB.1;Password=XXXXXX;Persist Security Info=True;User ID=XX;Initial Catalog=" + sSurvey + ";Data Source=" + sServer + "';Initial Catalog=\" + sServer + "SPSSMR_FMROOTMaster" + sSurvey + "" + sSurvey + ".mdd;MR Init Project=" + sSurvey

    oConnection.Open()

    If ( oConnection.State = 1 ) Then

        Set oRecordset = CreateObject("ADODB.Recordset")
        oRecordset.Open("DELETE FROM VDATA WHERE Respondent.serial = '" + sID + "'" ,oConnection,3,1)
        Set oRecordset = Null

    End If

    oConnection.Close()
    Set oConnection = Null

End Sub

JustCode : Responses under radio buttons

This code shows us how to have our responses under the radio buttons.

Metadata(en-AU, Question, Label)
    Q1 "This is a Rating Scale {ReplaceMe}"
        categorical [1..1]
        {
        _1 "1 Poor",
        _2 "2",
        _3 "3",
        _4 "4",
        _5 "5 Good"
    };

End Metadata

And now for the routing.


Routing(Web)

    Q1.Style.Columns = 5

    Q1.Label.Inserts["ReplaceMe"] = "(Responses underneath)"
    Q1.Categories[..].Label.Style.ElementAlign=ElementAlignments.eaNewLine
    Q1.Categories[..].Style.Indent=0
    Q1.Categories[..].Style.Align=Alignments.alCenter
    Q1.Style.Orientation=orientations.orRow
    Q1.Ask()

End Routing

JustCode : Remove a language from MDD via script

This example show us how to open up an MDD file in read-write mode and remove a langauge from it.

' ****************************************
' Designed by : Smarter Dimensions
' Last Updated : 26th September 2009
' Open MDD file and remove language
' ****************************************

    Dim oMDM

    ' Create the MDM object and open the Short Drinks .mdd file in read-write mode
    Set oMDM = CreateObject("MDM.Document")
        oMDM.Open("c:\tempNew.mdd", ,2)

        oMDM.Languages.Remove("JPN")

        oMDM.Save()

        oMDM.Close()

JustCode : Randomize Cats V2

This function will randomize categories and make sure that some do not sit next to each other.

Sub q13aran(q,o)
    Dim cat, cat1, cat2, cat3, cats
    Dim idx, idx1, idx2, idx3
    Dim strFilt

    ' Randomize
    q.Categories.Order = o
    idx = 0
    idx1 = 0
    idx2 = 0
    idx3 = 0

    ' Find 3 categories interested in
    For Each cat in q.Categories
        idx = idx + 1
        If ( cat = {_18} Or cat = {_19} Or cat = {_20} ) Then
            If ( idx1 = 0 ) then
                idx1 = idx
                cat1 = cat.name
            ElseIf ( idx2 = 0 ) Then
                idx2 = idx
                cat2 = cat.name
            ElseIf ( idx3 = 0 ) Then
                idx3 = idx
                cat3 = cat.name
            End If
        End If
    Next

    ' If not next to each other, then keep what we have
    If ( (idx1 + 1) <> idx2 And (idx2 + 1) <> idx3 ) Then Exit Sub


    If ( (idx1 + 1) = idx2 And (idx2 + 1) = idx3 ) Then  ' If all 3 in a row
        If ( idx1 = 1 ) Then           ' First 3
            idx2 = idx2 + 1
            idx3 = idx3 + 2
        ElseIf ( idx3 = idx ) Then     ' Last 3
            idx1 = idx1 - 2
            idx2 = idx2 - 1
        Else                           ' in middle
            idx1 = idx1 - 1
            idx3 = idx3 + 1
        End If
    ElseIf ( idx1 = 1 And idx2 = 2 And idx3 = 4 ) Then  ' 1,2,4
        idx2 = 3
        idx3 = 5
    ElseIf ( idx1 = 1 And idx2 = 3 And idx3 = 4 ) Then  ' 1,3,4
        idx2 = 3
        idx3 = 5
    ElseIf ( (idx1 = (idx - 3)) And idx2 = (idx - 1) And idx3 = idx ) Then  ' last-3,last-1,last
        idx1 = idx1 - 1
        idx2 = idx2 - 1
    ElseIf ( (idx1 = (idx - 3)) And idx2 = (idx - 2) And idx3 = idx ) Then  ' last-3,last-2,last
        idx1 = idx3 - 4
        idx2 = idx3 - 2
    Else

        If ( (idx1 + 1) = idx2 ) Then
            If ( idx1 = 1 ) Then          ' idx1 is first category
                idx2 = 3
            Else
                idx1 = idx1 - 1
            End If
        End If

        If ( (idx2 + 1) = idx3 ) Then
            If ( idx3 = idx ) Then         ' idx3 is last category
                idx2 = idx2 - 1
            Else
                idx3 = idx3 + 1
            End If
        End If

    End If

    ' Convert list into a string of categories
    strFilt = ""
    idx = 1
    For Each cat in q.Categories
        If ( idx = idx1 ) Then
            strFilt = strFilt + cat1 + ","
            idx = idx + 1
        ElseIf ( idx = idx2 ) Then
            strFilt = strFilt + cat2 + ","
            idx = idx + 1
        ElseIf ( idx = idx3 ) Then
            strFilt = strFilt + cat3 + ","
            idx = idx + 1
        End If
        If ( cat <> {_18} And cat <> {_19} And cat <> {_20} ) Then
            strFilt = strFilt + cat.name + ","
            idx = idx + 1
        End If
    Next

    ' Put into custom order in .Categories
    strFilt = Left(strFilt,Len(strFilt) - 1)
    q.Categories.Filter = {}
    q.Categories.Order = OrderConstants.oCustom
    cats = split(strFilt,",")
    For idx = lbound(cats) to ubound(cats)
        q.Categories.Filter = q.Categories.Filter + CCAtegorical("{" + cats[idx] + "}")
    Next

    ' Catch all to ensure these are all captured - should never happen
    If ( FindItem(q.CAtegories,{_18}) is null ) Then q.Categories = q.Categories + {_18}
    If ( FindItem(q.CAtegories,{_19}) is null ) Then q.Categories = q.Categories + {_19}
    If ( FindItem(q.CAtegories,{_20}) is null ) Then q.Categories = q.Categories + {_20}

End Sub

JustCode : Adjust progress bar when asking Loops

This code shows us how to increment the EstimatedProgress counter to show a progress bar correctly when asking loop questions.

Metadata(en-AU, Question, label)

    Q1 "Loop_Text_Goes_Here" loop
    {
        A,B,C,D,E,F
    } fields
    (
        Q1Rate "Please Rate {@}" categorical [1]
        {
        VeryBad,Bad,OK,Good,VeryGood
        };
    ) expand;
End Metadata

and now the routing

' ****************************************
' Designed by : Smarter Dimensions
' Last Updated : 4th August 2009
' Adjust progress bar to display properly when asking loops
' ****************************************

Routing(Web)
    Dim oCat

    For Each oCat in Q1.Categories
        Q1[oCat].Ask()
        IOM.Info.EstimatedProgress = IOM.Info.EstimatedProgress+1
    Next

End Routing

JustCode : Connect to a Survey and find a record

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

JustCode : Connect and insert a record into a SQL table

This code will show you how to insert a record into a standard SQL table passing in values collected from a Data Collection Survey.

This is the metadata

Metadata(en-AU, Question, Label)
    ConnectionInfo "{Info}" info;
    Name "Name" text;
End Metadata

This is the routing

Routing(Web3)

    Dim oInfo
    Set oInfo = CreateObject("Scripting.Dictionary")
        oInfo.Add("Message","")
        oInfo.Add("ID",-1)

    Name.Ask()

   ' Lets Insert the Record
   Set oInfo = InsertDataRecord("MyServer","Participants", _
        "Names","ID,UserName", _
        CTEXT(iom.Info.Serial) + ",'" + Name.Response.Value + "'", _
        "MyAdmin","ABC",oInfo)

   ConnectionInfo.Label.Inserts["Info"] = Ctext(oInfo.item["Message"])
   ConnectionInfo.Show()

Function InsertDataRecord(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=SQLOLEDB.1;Persist Security Info=-1;"
        sConnection = sConnection + "Password=" + sPassword + ";"
        sConnection = sConnection + "User ID=" + sUser + ";"
        sConnection = sConnection + "Initial Catalog=" + sDatabase + ";"
        sConnection = sConnection + "Data Source=" + sServer

    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 InsertDataRecord = oReturnObject

End Function

End Routing

JustCode : Ask a read only question

This code will show you how to display a question on the page in Readonly mode.

Metadata(en-AU, Question, label)
    Q1 "This is my text question" text;
End Metadata

and the routing

****************************************
' Designed by : Smarter Dimensions
' Last Updated : 4th August 2009
' ask a read only question
' ****************************************

Routing(Web)

' First way to make a question readonly
Q1.show()

' Second way
QDate.Style.Control.ReadOnly = True
    Q1.ask()

End Routing

JustCode : Adding AutoAnswer Hints to your MDD

This code shows you how to add AutoAnswer Hints to your MDD files.

 
Metadata(en-AU, Question, Label)
    AUTOANSWER lcl(en-AU, AutoAnswer, Label);
    '### END LCL MAP ###

    HDATA -
    [
    Template = "DefaultLayout.htm",
    TemplateLocation = "Hints_files"
    ]
    AUTOANSWER:-;

    Email "Please enter email address"
      AUTOANSWER:-
      AUTOANSWER: [ Value = "smarterDimensions@live.com.au" ]
      text;

    SingleResponseQuestion ""
      AUTOANSWER:-
      AUTOANSWER: [ Value = "D" ]
    categorical [1..1]
    {
      A "A" AUTOANSWER:-,
      B "B" AUTOANSWER:-,
      C "C" AUTOANSWER:-,
      D "D" AUTOANSWER:-,
      E "E" AUTOANSWER:-,
      F "F" AUTOANSWER:-
    };

    MultiResponseQuestion ""
      AUTOANSWER:-
      AUTOANSWER: [ AllowedCategories = "A,B,C" ]
    categorical [1..]
    {
      A "A" AUTOANSWER:-,
      B "B" AUTOANSWER:-,
      C "C" AUTOANSWER:-,
      D "D" AUTOANSWER:-,
      E "E" AUTOANSWER:-,
      F "F" AUTOANSWER:-
    };

    NumericQuestion ""
      AUTOANSWER:-
      AUTOANSWER: [ Max = 10, Min = 20 ]
    long;
End Metadata
%d bloggers like this: