Forum OpenACS Q&A: Cannot work out what I've done wrong.

Approaching frustration meltdown, so posting before metamorphosing into that duck with the sledgehammer!

I am trying to add the sku number to the search results listing in the ecommerce admin pages.

I have attempted this by adding sku to the query (in both the .xql and .tcl files, however the active query appears to be the one in the db_foreach call in the .tcl file) and adding to the doc_body_append call. I have re-named the local variable sku that used to hold the user selected sku to sku_sel to try to avoid problems with variable collisions (although I would have thought that db_foreach would just overwrite any existing vars). I tested the searching after this change and all is ok with the name change.

For some reason that I do not understand, db_foreach will not set sku (or price which I tried as a test) and reports :

'errmsg is can't read "sku": no such variable.'

It does however populate product_id and product_name.

I must be leaving out a step....

Please can someone explain why this code :

# www/[ec_url_concat [ec_url] /admin]/products/search.tcl
ad_page_contract {
Search for a product based on name or sku.

@author Eve Andersson (mailto:eveander@arsdigita.com)
@author Bart Teeuwisse (mailto:bart.teeuwisse@7-sisters.com)
@creation-date Summer 1999
@cvs-id $Id: search.tcl,v 1.3 2002/09/10 22:22:45 jeffd Exp $
@author ported by Jerry Asher (mailto:jerry@theashergroup.com)
} {
sku_sel:notnull,optional
product_name:optional
}

ad_require_permission [ad_conn package_id] admin

if { [info exists sku_sel] } {
set additional_query_part "sku=:sku_sel"
set description "Products with SKU #$sku_sel:"
} else {
set product_name_search $product_name
set additional_query_part "upper(product_name) like '%' || upper(:product_name_search) || '%'"
set description "Products whose name includes \"$product_name\":"
}

doc_body_append "[ad_admin_header "Product Search"]

<h2>Product Search</h2>

[ad_admin_context_bar [list "../" "Ecommerce([ec_system_name])"] [list "index.tcl" "Products"] "Product Search"]

<hr>

$description

<ul>
"

# Query modified by RH on 09/06/03 to include sku in listing

set product_counter 0
db_foreach product_search_select "select product_id, sku, product_name from ec_products where $additional_query_part" {
incr product_counter
doc_body_append "<li><a href=\"one?[export_url_vars product_id ]\">$sku - $product_name</a>\n"
} if_no_rows {
doc_body_append "No matching products were found.\n"
}

doc_body_append "</ul>

[ad_admin_footer]
"

Produces this error :

[10/Jun/2003:09:54:25][31774.114696][-conn4-] Notice: Querying '
select product_id, product_name from ec_products where sku='100';'
[10/Jun/2003:09:54:25][31774.114696][-conn4-] Notice: dbinit: sql(localhost::server): '
select product_id, product_name from ec_products where sku='100'
'
[10/Jun/2003:09:54:25][31774.114696][-conn4-] Notice: RP (41.792 ms): error in rp_handler: serving POST /index/admin/products/search
    ad_url "/index/admin/products/search" maps to file "/web/server/packages/ecommerce/www/admin/products/search.tcl"
errmsg is can't read "sku": no such variable
[10/Jun/2003:09:54:25][31774.114696][-conn4-] Notice: Querying '
select 1
where 't' = acs_permission__permission_p('5892', '2612', 'admin');'
[10/Jun/2003:09:54:25][31774.114696][-conn4-] Notice: dbinit: sql(localhost::server): '
select 1
where 't' = acs_permission__permission_p('5892', '2612', 'admin')
'
[10/Jun/2003:09:54:25][31774.114696][-conn4-] Error: POST http://server1/index/admin/products/search?
referred by "https://server1/index/admin/products/"
can't read "sku": no such variable
while executing
"doc_body_append "<li><a href=\"one?[export_url_vars product_id ]\">$sku - $product_name</a>\n"
"
("uplevel" body line 3)
invoked from within
"uplevel 1 $code_block "
("1" arm line 1)
invoked from within
"switch $errno {
        0 {
          # TCL_OK
        }
        1 {
          # TCL_ERROR
          global errorInfo errorCode
          error $error $errorInfo $errorCode
        }
        2 {
    ..."
("while" body line 20)
invoked from within
"while { [db_getrow $db $selection] } {
      incr counter
      if { [info exists array_val] } {
        unset array_val
      }
      if { ![info exists column..."
("uplevel" body line 5)
invoked from within
"uplevel 1 $code_block "
invoked from within
"db_with_handle db {
    set selection [db_exec select $db $full_statement_name $sql]

    set counter 0
    while { [db_getrow $db $selection] } {
      incr co..."
(procedure "db_foreach" line 35)
invoked from within
"db_foreach product_search_select "select product_id, sku, product_name from ec_products where $additional_query_part" {
incr product_counter
d..."
("uplevel" body line 44)
invoked from within
"uplevel {
      # www/[ec_url_concat [ec_url] /admin]/products/search.tcl
ad_page_contract {
Search for a product based on name or sku.

@author..."
(procedure "code::tcl::/web/server/packages/ecommerce/www/ad..." line 2)
invoked from within
"code::tcl::$__adp_stub"
invoked from within
"if { [file exists $__adp_stub.tcl] } {

# ensure that data source preparation procedure exists and is up-to-date
adp_init tcl $__adp_stub
..."
("uplevel" body line 3)
invoked from within
"uplevel {

if { [file exists $__adp_stub.tcl] } {

# ensure that data source preparation procedure exists and is up-to-date
adp_init t..."
(procedure "adp_prepare" line 2)
invoked from within
"adp_prepare "
(procedure "template::adp_parse" line 30)
invoked from within
"template::adp_parse [file root [ad_conn file]] {}"
(procedure "adp_parse_ad_conn_file" line 7)
invoked from within
"$handler"
("uplevel" body line 2)
invoked from within
"uplevel $code"
invoked from within
"ad_try {
    $handler
} ad_script_abort val {
    # do nothing
}"
invoked from within
"rp_serve_concrete_file [ad_conn file]"
(procedure "rp_serve_abstract_file" line 60)
invoked from within
"rp_serve_abstract_file "$root/$path""
("uplevel" body line 2)
invoked from within
"uplevel $code"
invoked from within
"ad_try {
    rp_serve_abstract_file "$root/$path"
    set tcl_url2file([ad_conn url]) [ad_conn file]
    set tcl_url2path_info([ad_conn url]) [ad_conn path_inf..."

The server log does not add any new information.

Regards
Richard