[Zope] Access SQL record from list

[Tim Considine]

Hi Alan

Thanks for this - I have tried to integrate this into my code as
follows (some edited for brevity), but I get an error KeyError on
KeyValue ContactID.   But I can't see where it is wrong - the
SQLContactRecord method works fine manually.

SQLContactRecord (SQLMethod)
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
argument : CID
select FirstName from contacts where ContactID=3D<dtml-sqlvar CID
type=3Dint>

ViewContact (DTML)
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
<dtml-var standard_html_header>
<dtml-call "SQLContactRecord(CID=3DCID)">
You selected <dtml-var FirstName>
<dtml-var standard_html_footer>

ShowAllContacts
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
<dtml-var standard_html_header>
<dtml-in SQLContactsListLastName>

<table>
  <tr>
  <td><a href=3D"ViewContact?CID=3D<dtml-var ContactID
url_quote>"><dtml-var ContactID></a></td>
  <td><dtml-var FIRSTNAME></td>
  <td><dtml-var LASTNAME></td>
  </tr>
</table>

</dtml-in>
<dtml-var standard_html_footer>

Any thoughts ?

Tim Considine

On Sunday 08 July 2001  5:13 pm, you wrote:
> What you'll want to do is create a link that points to another page
> and passes a unique identifier to the page for use in an SQL method.
>
> Here's an example from a simple Bill of Materials system I wrote. The
> first method is called component_list and displays a component
> database. The second is the del_component method which call del_comp
> (an SQL method) and then redisplays component list. (There are more
> pieces to the example, but this is the gist.)
>
> My system runs on an Access database, and uses an autonumber field to
> provide a unique identifier to each record which is used in other
> tables to reference the record. You can use any kind of unique
> identifier. If the key is not unique, you will return multiple
> records (which may be desirable depending on  your needs).
>
> The user sees a list like this, where the first and last colums ar
> clickable and act on different records.
>  3COM Hub 16-port   16-port 10/100BASE-TX Fast Ethernet Hub   Remove
>  3COM Hub 8-port    8-port 10/100BASE-TX Fast Ethernet Hub    Remove
>  3COM 10/100 NIC    10/100 PCI Adapter                        Remove
>
>
> component_list
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> <dtml-var standard_html_header>
>
> <TABLE border=3D0 cellpadding=3D2>
>   <TR align=3Dcenter bgcolor=3D#dd88ff>
>   <TD>Name</TD><TD>Desc</TD><TD><BR></TD>
>   </TR>
> <dtml-in all_components>
>   <TR>
>   <TD><a href=3D"form_edit_component?itemnum=3D&idnum=3D&cid=3D<dtml-va=
r
> CIDNum url_quote>"><dtml-var CItem></A><BR></TD>
>   <TD><dtml-var CDesc></TD>
>   <TD><a href=3D"del_component?comp=3D<dtml-var CItem>&cid=3D<dtml-var
> CIDNum url_quote>">Remove</A></TD>
>   </TR>
> </dtml-in>
> </TABLE>
> <BR><BR>
> <a href=3D"/BOM/form_add_component">Add a new component</A><BR>
>
> <dtml-var standard_html_footer>
>
> del_component
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D
> <B></B>
> <dtml-if "xref_by_comp(comp=3Dcid)">
>   <BR>You cannot delete this component. It is in use in the following
> items:<BR><BR>
>   <BLOCKQUOTE>
>   <dtml-in "xref_by_comp(comp=3Dcid)" >
>     <dtml-if first-Parent>
>       <dtml-in "components_all(item=3DParent)"><BR>
>         <dtml-var CItem><BR>
>       </dtml-in>
>     </dtml-if>
>   </dtml-in>
>   </BLOCKQUOTE>
>   <BR>Remove the component from all of the items listed above, then
> return here and delete it.<BR>
> <dtml-else>
>   <dtml-call "del_comp(cid=3Dcid)">
>   <dtml-call "RESPONSE.redirect('component_list')" >
> </dtml-if>
>
> del_comp
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> argument: cid    (unique id of record)
> DELETE FROM Components
> WHERE CIDNum =3D <dtml-sqlvar cid type=3Dint>
>
>
>
> - Alan
> ---------------------------------------
> Zope tips and tricks site
> http://twsite.bizland.com/zopetips.htm
> http://www.twstore.com
>
> > -----Original Message-----
> > From: zope-admin@zope.org [mailto:zope-admin@zope.org]On Behalf Of
> > Tim Considine
> > Sent: Sunday, July 08, 2001 10:40 AM
> > To: zope@zope.org
> > Subject: [Zope] Access SQL record from list
> >
> >
> >
> > I am confused how to access a specific record in a MySQL database,
> > from an HTML table holding the results of an SQL query.
> >
> > For example I use a DTML method with a form to specify a name, and
> > then call an SQLMethod, which returns a list of names into an HTML
> > table.
> >
> > I can specify an HTML link to another DTML or SQL Method, but I am
> > not sure what that method should be.
> >
> > Any advice ?
> >
> >
> > Tim Considine