How do I rewite this code to connect to a Domino DECS connection vs an ADO connection?

Posted on 2004-10-21
Last Modified: 2013-12-18
Need help rewriting this.
I need to connect to a DECS connection already established. (same name).
Sub Initialize
      Dim session             As New NotesSession
      Dim db                         As NotesDatabase
      Dim EODMark As String, tempPONumb As String, currPO As String, povend As String, postat As String, sql As String
      Dim GetNextPO       As Integer
      Dim poline                   As Integer, cnt As Integer
      Dim tdoc                   As NotesDocument
      Dim ItemView            As NotesView
      Dim tempdate            As Variant
      Dim datediff             As Double
      Dim ret                         As Long
      Dim cmd                   As Variant
      Dim cols                   As Variant
      Dim tval                         As Variant
      Dim tDesc                  As Variant
      Set db = session.CurrentDatabase
      Set ItemView = db.GetView("(Items)")
'      ret = Messagebox("Use ADO (Yes) or RDO (No)", 4, "Question")
'      If ret = 6 Then
      Set con = CreateObject("ADODB.Connection.2.5")
      Set res = CreateObject("ADODB.Recordset.2.5")
      If con.State = adStateOpen Then
      End If
      con.ConnectionString = "uid=sa;pwd=sas;DSN=Warehouse;RO=True;FBS=50;"    
      SQL = "Select pt_part, pt_desc1 From PT_MSTR where pt_part like '1%'"
      Set res = con.Execute(sql, ret, adCmdTable)
      cnt = 0
      While res.EOF = False
            tval = res(0).Value
            If Instr(1, tval, "40477", 0) > 0 Then
                  tval = res(0).Value
            End If
            tDesc = res(1).Value
            Set tdoc = ItemView.getDocumentbyKey(tval)
            If tdoc Is Nothing Then
                  Set tdoc = db.CreateDocument
                  tdoc.Form = "(Items)"
                  tdoc.ItemNumber = tval
                  tdoc.ItemDesc = tDesc
                  Call tdoc.Save(True, True)
            End If
            cnt = cnt + 1
'      Else
'            Set con = CreateObject("MicrosoftRDO.RdoConnection2.0")
'            con.close
'            con.Connect = "uid=sa;pwd=sas;DSN=Warehouse;RO=True;FBS=50;"
'            con.cursordriver = 1
'            con.EstablishConnection 1
'            SQL = "Select pt_part, pt_desc1 From PT_MSTR where pt_part like '4%'"
'            Set rs = con.OpenResultSet(SQL,rdOpenDynamic,rdConcurReadOnly)
'            cnt = 0
'            rs.MoveNext
'            While rs.EOF = False
'                  tval = rs(0).Value
'                  tDesc = rs(1).Value
'                  Set tdoc = ItemView.getDocumentbyKey(tval)
'                  If tdoc Is Nothing Then
'                        Set tdoc = db.CreateDocument
'                        tdoc.Form = "(Items)"
'                        tdoc.ItemNumber = tval
'                        tdoc.ItemDesc = tDesc
'                        Call tdoc.Save(True, True)
'                  End If
'                  cnt = cnt + 1
'                  rs.MoveNext
'             Wend
'            rs.close
'            con.close
'      End If  
End Sub

Question by:rbend
    LVL 31

    Expert Comment

    You would nly use a DECS connection if you wanted Domino to automatically pull in the SQL results as the form opens, and place them intop the designated fields.

    Also, DECS can't be scripted -- you basically tel it what you want tomatch up, and it either maps or it does not map.  You can't do much logic in it.  If you want logic, you have to go with LEI.  Or you can use LSXLC, which is the DECS connection but using LotusScript (similar to ADO).  However, DECS and LEI both run at the server, while LSXLC runs at the client.  (LSXLC can run on the server, but there is simple way to trigger it without LEI).

    Why don't you explain WHY you need to switch ADO to DECS, and what the context ofthe bussiness case is?

    Author Comment

    OK...we havn't had this script running in a while and we have since changed boxes.
    There wasn't a DSN set up on the box the parameters of which are not known.
    All we know is it ran fine.
    Now, there is a SQL DSN set up on the box and it checks out.
    Not sure what we're missing to make th above script work, but when I invoke DEBUG, it bombs at the line " Set con = CreateObject("ADODB.Connection.2.5")"
    I don't know enough at this point to be able to know why.
    It was suggested that we use DECS, but again, I'm swirling and don't know how to employ it correctly.
    I'd like to leave the script as is and get whatever I'm missing going on the other end.

    LVL 31

    Accepted Solution

    You ask for a lot for 50 points!  Anyway...

    In what way does it bomb?

    Perhaps you do not have ADO installed, or the wrong version of ADO.

    Don't forget, this code has to execute on a particular box. If this is a web application, then the code must be in an agent that runs on the server.  If it is a Notes client application, then it will execute on the Notes client, unless it is an agent that is set to run on the server.  Whichever box it executes on has to be a Widnows box (CreateObject uses COM/OLE/ActiveX, which only exists under Windows), and the component being loaded (ADODB.Connection.2.5) has to available on the same box.

    It looks like LS:DO would be an easier conversion for you in this case, since it is less complex.  The following MIGHT do it:

    In declarations, you need: Use "*lsxodbc"

    In you code you need:

    Dim con as New ODBConnection
    Dim qry as ODBCQuery
    Dim res as ODBCResultSet

    Set qry.Connection = con
    con.ConnectTo "Warehouse"
    qry.SQL =  "Select pt_part, pt_desc1 From PT_MSTR where pt_part like '1%'"

    Set res.Query = qry
    While Not res.IsEndOfData
        tval = res.GetValue(1) 'not GetValue(0) -- equivalent to res(0).value in your code
        res.nextRow 'equivalent to res.MoveNext in your code

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    This is an old article, please see an updated version of this article, located here:
    This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
    With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…
    This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

    934 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now