/ Forums / Advansys Formativ / Creating Solutions with Formativ / Data Retrieval/Update Form based processing

  • Creator
    Topic
  • #4303
    sfritz
    Participant

    I apologize for sounding like a broken record by I am using Formativ 2.0, I create a menu form with 2 buttons, Add and Update. The Add functionality works great, the problem I continue to have is with the update.

    The user clicks on the update button, I prompt them with an input box asking which month they want to retrieve, and in a listview I display key fields, once they click on the record they want to edit I take that information and populate it into the form I use for adding except I change the captions. For whatever reason I continually need to keep opening connections to the db. I know I should be able to establish a single db connection and pass it from form to form but I can’t get it to work. Do you have an form based example that will show me the error in my ways? Your dialog box entry gives me a general idea but when I apply the logic it is a no go.

    Thanks

  • Author
    Replies
  • #7768
    Support 3
    Participant

    Sorry, we don’t have any examples relating to your questions. Based on your previous post, you may need to open the connection once and all other function/sub should use the opened database connection. Make sure to close the connection at the end of the process. Here is an example of the structure you can use:

      
    Sub Main(Client, GWEvent)
    
      dim oADOObj
    
      set oADOObj = nothing
    
      if not OpenDataBase() then
        exit sub
      end if
    
      select case iDlg.execute
        case Btn1 AddNewRecord(oADOObj)
        case Btn3 DisplayAllRecords(oADOObj)
        case Btn4 FindRecord(oADOObj)
        case Btn5 EditRecord(oADOObj)
      end select
    
      CloseDataBase(oADOObj)
    
      set oADOObj = nothing
    
    End Sub
    
    
    ' Open Database connection
    function OpenDataBase(byref aADOObj)
    
     OpenDataBase = false
     Set aADOObj = CreateObject("ADODB.Connection")
    
     ' << Setup your connection string and other process relate to opening database >>
    
    end function
    
    
    
    ' Close Database connection
    function CloseDataBase(byref aADOObj)
    
      if aADOObj is nothing then
        exit function
      end if
    
      aADOObj.close
    
    end function
    
    
    ' Add record as the connection is already opened so no need to open in here
    Sub AddNewRecord(byref aADOObj)
    
      if aADOObj is nothing then
        exit sub
      end if
    
    end sub
    

    Regards,
    Advansys Support

    [This message was edited by Support 3 on October 26, 2006 at 10:31 PM.]

    [This message was edited by Support 2 on October 27, 2006 at 12:54 AM.]

    #7769
    sfritz
    Participant

    You have been very helpful as usual and I am much further with my form processing and I thank you. I have one minor issue and have attached the code for you to look at. It is in the update logic, the record’s old value is not being kept, can you tell me why?

    ‘——————————————————————————-
    ‘ Insert your comments here
    ‘——————————————————————————-

    Sub Main(Client, GWEvent)
    dim oADOObj

    set oADOObj = nothing

    ‘ if not OpenDataBase(oADOObj) then
    ‘ exit sub
    ‘end if

    Menu.showmodal

    ‘CloseDataBase(oADOObj)
    set oADOObj = nothing

    End Sub

    ‘ Open Database connection
    function OpenDataBase(byref aADOObj)
    ‘ << Setup your connection string and other process relate to opening database >>
    OpenDataBase = false
    Set aADOObj = CreateObject(“ADODB.Connection”)
    if isobject(aADOObj) then
    iConnnectString = “Driver={MySQL ODBC 3.51 Driver}; Data Source=Formativ”
    aADOObj.Open iConnnectString
    if err.number = 0 then
    OpenDataBase = true
    msgbox “Database is open”
    else
    call msgbox(“Failed to create ADODB.Connection object.”, vbCritical, CAPTION)
    end if
    end if
    end function

    ‘ Close Database connection
    function CloseDataBase(byref aADOObj)
    if aADOObj is nothing then
    exit function
    end if
    aADOObj.close
    end function

    function clear_form
    FRM1.Name_data.text = “”
    FRM1.ModalResult = MROK
    end function

    ‘ Add record as the connection is already opened so no need to open in here
    Sub AddNewRecord(byref aADOObj)
    dim iSQLstring
    dim iName, iRST, iUserid

    if aADOObj is nothing then ‘** error Object required
    exit sub
    end if

    iName = FRM1.Name_data.text
    iUserid = groupwise.EnvUserID
    iSQLString = “Insert into children (name, userid) values (‘” & iName & “‘,'” & iUserid & “‘)”
    Set iRST = aADOObj.Execute(iSQLString)
    Set iRST = Nothing
    end sub

    Sub TestButton2Click(Sender)
    FRM1.Showmodal
    End Sub

    Sub NameButton1Click(Sender)
    if FRM1.NameButton1.Caption = “Update Record” then
    call UpdateExistingRecordDialog(iADOObj, old_name)
    Clear_form
    exit sub
    end if
    if not OpenDataBase(oADOObj) then
    exit sub
    end if
    AddNewRecord(oADOObj)
    Clear_form
    CloseDataBase(OADOObj)
    End Sub

    sub StoreExistingRecordsDialog(byref iADOObj, byref iUpdateDlg, byref iListBox)
    dim iRST

    set iUpdateDlg = Utilities.NewControlBoxDialog
    with iUpdateDlg
    .Caption = CAPTION
    .Title = “Existing records”
    .Description = HRT & “Select an existing record to update”
    end with

    set iListBox = iUpdateDlg.AddListBoxControl
    iListBox.Caption = “Records:”

    iSQLString = “SELECT * FROM Children”
    Set iRST = iADOObj.Execute(iSQLString)

    Do Until (iRST.EOF)
    iListBox.items.add(iRST(“Name”))
    iRST.MoveNext
    Loop

    iRST.Close
    Set iRST = Nothing

    end sub

    ‘***** Problem area ****
    ‘aExtName is empty’

    sub UpdateExistingRecordDialog(byref iADOObj, aExtName)
    dim iNameUpdate, INameOld
    iNameUpdate = FRM1.Name_data.text
    iNameOld = aExtName
    ‘ If we press the Ol button to update
    ‘ iSQLUpdate = “UPDATE SET Name ='” & Name.Text & “‘ WHERE Name ='” & aExtName & “‘”
    ‘ Set iRST = iADOObj.Execute(iSQLUpdate)
    msgbox iNameOld
    msgbox iNameUpdate
    call msgbox (“record updated.”, vbInformation, CAPTION)
    end sub

    Hope you have a great weekend.

    sub EditRecord(byref iADOObj)

    dim iUpdateDlg
    dim iListBox
    dim iNameSelected

    cExit = 999
    cEditRecord = 201
    cDisplayAllRecords = 101
    Cmd = cDisplayAllRecords

    call StoreExistingRecordsDialog(iADOObj, iUpdateDlg, iListBox)

    do while Cmd <> cExit

    if Cmd = cDisplayAllRecords then
    select case iUpdateDlg.execute
    case Btn1 Cmd = cEditRecord
    case else Cmd = cExit
    end select
    end if

    if Cmd = cEditRecord then
    if iListBox.Selected <> “” then
    iNameSelected = iListBox.Selected
    FRM1.Name_data.text = iNameSelected
    FRM1.NameButton1.Caption = “Update Record”
    FRM1.Showmodal
    Cmd = cExit
    else
    call msgbox (“Select a record to update.”, vbInformation, CAPTION)
    Cmd = cDisplayAllRecords
    end if
    end if
    loop

    end sub

    Sub MenuButton1Click(Sender)
    if not OpenDataBase(oADOObj) then
    exit sub
    end if
    EditRecord(oADOObj)
    CloseDataBase(OADOObj)
    End Sub

    Sub name_dataEnter(Sender)
    dim old_name
    old_name = FRM1.Name_data.text
    msgbox old_name ‘ data is in this field
    End Sub

    #7766
    sfritz
    Participant

    Hello Support,

    Please disregard my request below, I figured out the problem.

    #7767
    Support 3
    Participant

    Thanks for your feedback.

    Regards,
    Advansys Support

Viewing 4 replies - 1 through 4 (of 4 total)
  • You must be logged in to reply to this topic.