i in process of creating "related items" feature online store. have sql query pull list of items database , display them on page i'm trying rig system list change depending on few variables.
the code below , part i'm having trouble getting usable string out of query1 used 'results' variale in query 2.
public shared function getextraproducts(byval straddoncat string) dataset dim connect new sqlconnection dim data1 new dataset dim data2 new dataset connect.connectionstring = "server = server-sql01; trusted_connection=yes; database=globalpcsql" connect.open() dim query1 string = "" dim query2 string = "" query1 = "select stockid dbo.addonlists subcategory = 'acer-desktops'" dim command1 = new sqldataadapter(query1, connect) command1.fill(data1) if data1.tables(0).rows.count > 0 query1 = "select stockid dbo.addonlists subcategory = 'generic'" dim command3 = new sqldataadapter(query1, connect) command3.fill(data1, "stockid") end if dim results string = "" each row in data1.tables(0).rows results += row.tostring() + "','" next if results.length > 2 results = results.substring(0, results.length - 2) end if 'results = "'hd12047' , 'tv12008'" query2 = "select stock_items.stockcode, stock_groups.xw_url stockgroup, stock_group2s.xw_url stockgroup2, stock_maingroup.xw_url stockmaingroup, stock_items.stockcode pid, stock_items.description pname, stock_web.sales_html pdescription, stock_web.picture_url pimage, stock_web.picture_url plargeimage, stock_items.sellprice1 pprice, stock_items.sellprice1, stock_items.sellprice2, stock_items.sellprice3, stock_items.sellprice4, stock_items.sellprice5, stock_items.sellprice6, stock_items.sellprice7, stock_items.sellprice8, stock_items.sellprice9, stock_items.status itemtype, stock_items.sellprice10 plistprice, stock_items.x_totalstock pinstock, stock_items.x_webhits phits, stock_items.isactive, stock_items.web_show, stock_items.x_webblub x_webblurb, stock_items.x_webpromo x_promopage, stock_items.last_updated lastupdated, stock_items.x_stockleveloverride, isnull(stock_items.restricted_item,'n') restricted_item " query2 += "from stock_items left outer join stock_web on (stock_items.stockcode = stock_web.stockcode) left outer join stock_groups on (stock_groups.groupno = stock_items.stockgroup) left outer join stock_group2s on (stock_group2s.groupno = stock_items.stockgroup2) left outer join stock_maingroup on (stock_maingroup.groupno = stock_groups.xw_maingroup)" query2 += "where stock_items.isactive='y' , stock_web.picture_url not null " query2 += "and stock_items.stockcode in ('" + results + "')" query2 += results dim command2 = new sqldataadapter(query2, connect) command2.fill(data2) connect.close() return data2 end function everything works fine if spoon feed stock id numbers query 2 (in place of ' + results + ' section, when try use string query 1 is
incorrect syntax near 'system.data.datarow' which makes me think if got syntax sorted, it'l search system.data.datarow instead of actual value of field.
any appreciated.
a datarow can contain many datacolumns. though return datarow contains 1 datacolumn, must still specify datacolumn:
dim results string = "" each row in data1.tables(0).rows results += row.item(0).tostring() + "','" '<--- added item(0)' next if results.length > 2 results = results.substring(0, results.length - 2) end if also, double-check have apostrophe first result. might need:
dim results string = "'" finally, on unrelated note, while + operator can used string concatenation, recommend use & operator instead , use + numerical addition:
results &= row.item(0).tostring() & "','" '<--- added item(0)' 
Comments
Post a Comment