Hello again, day 3 with Xojo and I am having fun. I have been working on an address book as my first project and so far I have it adding records and displaying records including using autocomplete to display records (all from a remote MySQLCommunity Server database).
Problem:
Now I am working on a method called ‘EditRecord’ to edit the displayed record however I am doing something wrong that results in my recordset being nil even if the query showing the debug window works in Workbench to fetch the single record row I am looking for.
I have added some remarks to the code for this post. After a few hours messing trying different things, I am stumped. I have modeled my code after the Changing Existing data section of the Database operations guide and am not seeing what I may be doing wrong.
At the time of this method call, you have a series of populated text fields fetched from the database in a windows form called ContactsWindow. An edit button on ContactsWindow that is made visible after field population calls this ‘EditRecord’ method as its click event .
With this method I am trying to update a bunch recordset fields with what is in the populated textfields whether they are changed or not (later I will make it only update changed fields, for now I am just trying to get it updating at all) but the recordset is nil so the method elses its way to db.Close without error.
' EditRecord method is public
' Connectdb is global method in a module "DatabaseModule" with global properties
' db As MySQLCommunityServer
' and
' IsConnected As Boolean
' Connectdb method includes line db = New MySQLCommunityServer
//
// Begin method
//
' Connect to database, report error(s) if fail
Connectdb
If Not db.Connect Then
MsgBox("Database Connect failed: " + db.ErrorMessage)
Else
IsConnected = True
IsConnectedLabel.Text = "Connected to: " + db.databasename + "@" + db.Host
End If
' Database shows connected in workbench and IsConnected = True
' RecordID.Text is record PK set by user selection, assigning to a variable to show in debug window
Dim RecordIDString As String = RecordID.Text
Dim sql As String
sql = ("SELECT * FROM contact_table WHERE idContact = '" + str(RecordIDString) + "';")
Dim rs As RecordSet
rs = db.SQLSelect(sql)
' Break at If rs <> Nil... to check rs for Nil and value of sql, sql query statement looks good
' e.g. SELECT * FROM contact_table WHERE idContact = '2';
If rs <> Nil Then
rs.Edit
If Not db.Error Then
rs.Field("ShipName").StringValue = cNameShipField.Text
rs.Field("ShipAddress").StringValue = shipAddressField.Text
rs.Field("ShipAttn").StringValue = attnShipField.Text
rs.Field("ShipCity").StringValue = shipCityField.Text
rs.Field("ShipState").StringValue = shipStatePopup.Text
rs.Field("ShipZip").StringValue = shipZipField.Text
rs.Field("ShipPrefecture").StringValue = shipPrefectureField.Text
rs.Field("ShipCountry").StringValue = shipCountryField.Text
rs.Field("ShipEmail").StringValue = shipEmailField.Text
rs.Field("ShipAltEmail").StringValue = shipAltEmailField.Text
rs.Field("BillName").StringValue = cNameBillField.Text
rs.Field("BillAddress").StringValue = billAddressField.Text
rs.Field("BillAttn").StringValue = attnBillField.Text
rs.Field("BillCity").StringValue = billCityField.Text
rs.Field("BillState").StringValue = billStatePopup.Text
rs.Field("BillZip").StringValue = billZipField.Text
rs.Field("BillPrefecture").StringValue = billPrefectureField.Text
rs.Field("BillCountry").StringValue = billCountryField.Text
rs.Field("BillEmail").StringValue = billEmailField.Text
rs.Field("BillAltEmail").StringValue = billAltEmailField.Text
rs.Field("TermsIndex").StringValue = TermsPopup.Text
rs.Field("CarrierIndex").StringValue = ShipPopup.Text
rs.Field("ShipAcctNo").StringValue = custShipNoField.Text
rs.Field("LegacyCustNo").StringValue = LegacyCustNoField.Text
rs.Field("ShipPhoneNo").StringValue = shipPhoneField.Text
rs.Field("ShipFaxNo").StringValue = shipFaxField.Text
rs.Field("BillPhoneNo").StringValue = billPhoneField.Text
rs.Field("BillFaxNo").StringValue = billFaxField.Text
rs.Update
MsgBox("Record Updated")
Else
MsgBox(db.ErrorMessage)
End If
rs.close
Else
If db.Error Then
MsgBox(db.ErrorMessage)
End If
End If
db.Close