Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 5 of 5 total |
Query Posting |
Mon, Apr 17 2006 9:24 PM | Permanent Link |
Lance R | ok. I know this is probably a newbie question....
3 Tables. Person, Address and PersonToAddress (to accomodate 1 to many relationship). GUID's are used for keys. I.E. TPERSON ====== personkey GUID name string[40] active boolean TADDRESS ======= AddressKey GUID Street string[40] active boolean .. .. TPERSONTOADDRESS ============== personkey GUID addresskey GUID addresstype string[40] active boolean ... ... I have a form that essentially allows editing a person and address that uses a datagrid that only has the name column to select the person. Then there is a panel that has a dropdown listbox to choose which of the addresses to edit for that person. Then there are DBAware editboxes to edit the street, etc for the address for the type of address selected. Query1's SQL will select all people with active being true. The results are put into the datagrid to choose the person. Query2 will select all address types for the person selected. The results is stuffed into a drop-down listbox. Query 3 will select the address based on the address type of the person selected from the drop-down listbox. The data aware edit boxes are set to Query 3. So far, no problem. I can select and view the data without issue. I placed a button on the form to allow changing the datasource to go into edit mode. No problem. I placed a label and I can see that I'm in edit mode when I select that. I placed a button on the form to save the changes. Now comes the question(s). What is the appropriate/best/easiest way to make those changes. I did the following, which did save the changes, but kept the 3rd query in edit mode and did not switch it to browse. function UpdateAddress(mykey:GUID); var tb: tdbisamtable; begin tb.session:=sessionname; tb.database:=databasename; tb.tablename:='Address'; tb.open; tb.Locate('AddressKey', mykey,[]); tb.edit; tb.fieldbyname('Street').AsString:= dbeditStreet.Text; tb.post; tb.close; end; |
Tue, Apr 18 2006 1:37 AM | Permanent Link |
Tony Pomfrett | Lance R wrote:
> ok. I know this is probably a newbie question.... > > 3 Tables. Person, Address and PersonToAddress (to accomodate 1 to many relationship). GUID's are used for keys. > I.E. > > TPERSON > ====== > personkey GUID > name string[40] > active boolean > > TADDRESS > ======= > AddressKey GUID > Street string[40] > active boolean > . > . > > > TPERSONTOADDRESS > ============== > personkey GUID > addresskey GUID > addresstype string[40] > active boolean > .. > .. > > > I have a form that essentially allows editing a person and address that uses a datagrid that only has the name column to select the person. Then there is a panel that has a dropdown listbox to > choose which of the addresses to edit for that person. Then there are DBAware editboxes to edit the street, etc for the address for the type of address selected. > > Query1's SQL will select all people with active being true. The results are put into the datagrid to choose the person. > > Query2 will select all address types for the person selected. The results is stuffed into a drop-down listbox. > > Query 3 will select the address based on the address type of the person selected from the drop-down listbox. > > The data aware edit boxes are set to Query 3. > > So far, no problem. I can select and view the data without issue. > > I placed a button on the form to allow changing the datasource to go into edit mode. No problem. I placed a label and I can see that I'm in edit mode when I select that. > > I placed a button on the form to save the changes. > > Now comes the question(s). > > What is the appropriate/best/easiest way to make those changes. > > I did the following, which did save the changes, but kept the 3rd query in edit mode and did not switch it to browse. > > function UpdateAddress(mykey:GUID); > var > tb: tdbisamtable; > begin > tb.session:=sessionname; > tb.database:=databasename; > tb.tablename:='Address'; > tb.open; > tb.Locate('AddressKey', mykey,[]); > tb.edit; > tb.fieldbyname('Street').AsString:= dbeditStreet.Text; > tb.post; > tb.close; > end; > > > Hi Lance, You haven't posted the query so why would it change mode? Looks like you've created a temporary DBISAMTable to do the update and have left the query in edit mode? Tony. |
Tue, Apr 18 2006 5:59 PM | Permanent Link |
"Lance R." | Here's essentially the code side of the sample project I'm doing.
Any suggestions to more efficiently do this is very welcome. =========================== procedure TForm1.btnSaveChangesClick(Sender: TObject); begin //Do I do this? DBISAMQuery3.FieldByName('NAME').AsString:= dbeName.Text; DBISAMQuery3.FieldByName('STREET').AsString:= dbeStreet.Text; DBISAMQuery3.FieldByName('CITY').AsString:= dbeCity.Text; DBISAMQuery3.FieldByName('STATE').AsString:= dbeState.Text; DBISAMQuery3.FieldByName('ZIP').AsString:= dbeZip.Text; DBISAMQuery3.UpdateRecord; DBISAMQuery3.Post; // Or this UpdateDB; DataSource1.DataSet.Close; DataSource1.DataSet.Open; // Or...??? end; procedure TForm1.UpdateDB; var tb: tdbisamtable; PersonGuid: TGUID; AddressGuid: TGUID; newperson: boolean; newaddress: boolean; begin tb:=tdbisamtable.Create(nil); try tb.SessionName:='mysession'; tb.DatabaseName:='ADDR'; // Update the name record tb.Tablename:='PERSON'; tb.Open; if tb.Locate('PersonID', dbeSelectedPersonID.Text,[]) then begin tb.edit; newperson:=FALSE; end else begin tb.Insert; CreateGuid(PersonGuid); tb.FieldByName('PersonID').AsString:= GuidToString(PersonGuid); newperson:=TRUE; end; tb.FieldByName('NAME').AsString:= dbeName.Text; tb.Post; tb.Close; // Update the address record tb.Tablename:='ADDRESS'; tb.Open; if tb.Locate('AddressID', dbeSelectedAddressID.Text,[]) then begin tb.edit; newaddress:=FALSE; end else begin tb.Insert; CreateGuid(AddressGuid); tb.FieldByName('AddressID').AsString:= GuidToString(AddressGuid); newaddress:=TRUE; end; tb.FieldByName('STREET').AsString:= dbeStreet.Text; tb.FieldByName('CITY').AsString:= dbeCity.Text; tb.FieldByName('STATE').AsString:= dbeState.Text; tb.FieldByName('ZIP').AsString:= dbeZip.Text; tb.Post; tb.Close; finally tb.free; end; end; procedure TForm1.DataSource1DataChange(Sender: TObject; Field: TField); begin DBISAMQuery2.Close; DBISAMQuery2.SQL.Clear; DBISAMQuery2.SQL.Add('SELECT * FROM PERSON_ADDRESS WHERE PERSONID='''+dbeSelectedPersonID.TEXT+''''); DBISAMQuery2.Open; end; procedure TForm1.DataSource2DataChange(Sender: TObject; Field: TField); var sqlstr: string; begin DBISAMQuery3.Close; DBISAMQuery3.SQL.Clear; sqlstr:='SELECT PERSON.Name,PERSON_ADDRESS.AddressType, ADDRESS.STREET, ADDRESS.CITY, ADDRESS.STATE, ADDRESS.ZIP '+ ' FROM PERSON INNER JOIN PERSON_ADDRESS ON (PERSON.PersonID = PERSON_ADDRESS.PersonID) ' + ' INNER JOIN ADDRESS ON (PERSON_ADDRESS.AddressID = ADDRESS.AddressID) ' + ' WHERE ' + ' '#9'(PERSON.PersonID = ''' + dbeSelectedPersonID.Text + ''') AND ' + ' '#9'(PERSON_ADDRESS.ADDRESSID = ''' + dbeSelectedAddressID.Text + ''')'; DBISAMQuery3.SQL.Add(sqlstr); DBISAMQuery3.Open; end; procedure TForm1.DataSource3StateChange(Sender: TObject); begin case DataSource3.State of dsInactive: btnSaveChanges.Enabled:=FALSE; dsBrowse: btnSaveChanges.Enabled:=FALSE ; dsEdit: btnSaveChanges.Enabled:=TRUE ; else btnSaveChanges.Enabled:=FALSE ; end; end; procedure TForm1.FormShow(Sender: TObject); begin self.DataSource1.DataSet.First; end; =========================== |
Fri, Apr 21 2006 10:26 AM | Permanent Link |
"B Miller" | I guess this may be a bit unrelated to the question, but why would you have
a separate table to hold the links in a 1-to-many relationship. Why not include the personkey in the address table. It just looks like you have a lot of redundant information and we're only seeing partial tables. If each address can belong to one and only one person, then there's no need for another table. I think the only time you would need the relation table is when you have a many-to-many relationship. Just my 2c, Bill "Lance R" <lance@lance.ws> wrote in message news:63F17CAB-7E7B-46F6-97EB-390A370EC3C5@news.elevatesoft.com... > ok. I know this is probably a newbie question.... > > 3 Tables. Person, Address and PersonToAddress (to accomodate 1 to many > relationship). GUID's are used for keys. > I.E. > > TPERSON > ====== > personkey GUID > name string[40] > active boolean > > TADDRESS > ======= > AddressKey GUID > Street string[40] > active boolean > . > . > > > TPERSONTOADDRESS > ============== > personkey GUID > addresskey GUID > addresstype string[40] > active boolean > .. > .. > > > I have a form that essentially allows editing a person and address that > uses a datagrid that only has the name column to select the person. Then > there is a panel that has a dropdown listbox to > choose which of the addresses to edit for that person. Then there are > DBAware editboxes to edit the street, etc for the address for the type of > address selected. > > Query1's SQL will select all people with active being true. The results > are put into the datagrid to choose the person. > > Query2 will select all address types for the person selected. The results > is stuffed into a drop-down listbox. > > Query 3 will select the address based on the address type of the person > selected from the drop-down listbox. > > The data aware edit boxes are set to Query 3. > > So far, no problem. I can select and view the data without issue. > > I placed a button on the form to allow changing the datasource to go into > edit mode. No problem. I placed a label and I can see that I'm in edit > mode when I select that. > > I placed a button on the form to save the changes. > > Now comes the question(s). > > What is the appropriate/best/easiest way to make those changes. > > I did the following, which did save the changes, but kept the 3rd query in > edit mode and did not switch it to browse. > > function UpdateAddress(mykey:GUID); > var > tb: tdbisamtable; > begin > tb.session:=sessionname; > tb.database:=databasename; > tb.tablename:='Address'; > tb.open; > tb.Locate('AddressKey', mykey,[]); > tb.edit; > tb.fieldbyname('Street').AsString:= dbeditStreet.Text; > tb.post; > tb.close; > end; > > > |
Fri, Apr 21 2006 6:24 PM | Permanent Link |
"Lance R." | Bill,
Your point is well taken. I support having it set as a many-to-many gives me the flexibility, but doesn't really give any referential integrity. In this schema, it does allow me to reduce the # of rows when, for example, Billing and Shipping addresses are the same. By using the Intersection table to point to the same address record, it reduces rows. Lance B Miller wrote: > I guess this may be a bit unrelated to the question, but why would you have > a separate table to hold the links in a 1-to-many relationship. Why not > include the personkey in the address table. It just looks like you have a > lot of redundant information and we're only seeing partial tables. If each > address can belong to one and only one person, then there's no need for > another table. I think the only time you would need the relation table is > when you have a many-to-many relationship. > > Just my 2c, > Bill > > "Lance R" <lance@lance.ws> wrote in message > news:63F17CAB-7E7B-46F6-97EB-390A370EC3C5@news.elevatesoft.com... >> ok. I know this is probably a newbie question.... >> >> 3 Tables. Person, Address and PersonToAddress (to accomodate 1 to many >> relationship). GUID's are used for keys. >> I.E. >> >> TPERSON >> ====== >> personkey GUID >> name string[40] >> active boolean >> >> TADDRESS >> ======= >> AddressKey GUID >> Street string[40] >> active boolean >> . >> . >> >> >> TPERSONTOADDRESS >> ============== >> personkey GUID >> addresskey GUID >> addresstype string[40] >> active boolean >> .. >> .. >> |
This web page was last updated on Wednesday, April 24, 2024 at 11:07 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |