JustCode : Connect and insert a record into a SQL table

by

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

Leave a Comment