Data Retrieval/Update Form based processing

[sfritz]

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

Topic

[Support 3]

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.]

[sfritz]

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

[sfritz]

Hello Support,

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

[Support 3]

Thanks for your feedback.

Regards,
Advansys Support

[Author]

Replies