New topic: Assigning * to listbox selection
<http://forums.realsoftware.com/viewtopic.php?t=46945> Page 1 of 1 [ 3 posts ] Previous topic | Next topic Author Message terreywe Post subject: Assigning * to listbox selectionPosted: Thu Feb 14, 2013 1:44 pm Joined: Wed Oct 31, 2012 11:21 pm Posts: 5 Hi all, Looking for some help here, its database related-ish, so I looking for some help. just creating a sample application for products,and using listbox for some navigation/filters. so i have 3 fields/listbox for filters, which are type, Color and size. Each has its own fields, but all have an option for "ALL" so roughly to show the search results, running sql = select * from products where type = '"+LBtype.text+"' and color = '"+LBcolor.text+"' and size = '"+LBsize.text+"' . This coding works fine. The issue, with the "ALL" option in each listbox, SQL doesnt read that selection. Tried " If LBtype.text = "ALL" then LBtype.text = "*" ", but that simply replaces the symbol into the list box Currently using if-Else statements for all possible combinations(there r 8 combinations), which although works, but rather troublesome. But now we might need to add 2 more filters(roughly 32 combinations), which will really make this near impossible. Wondering if we can either replace the value somehow, or if there is a much simpler method....again im still new to RS, so learning as much as possible. Appreciate any help in regards to this, and my thanks in advance. Top markwalsh Post subject: Re: Assigning * to listbox selectionPosted: Thu Feb 14, 2013 2:02 pm Joined: Wed May 20, 2009 4:43 pm Posts: 937 I usually add all of the 'option' fields to an array, and then join the array using 'AND' as a delimiter. Something along the lines of this (untested, off the top of my head): Dim optionsArray(-1) as string Dim options as string sql = "select * from products " if (LBtype.text <> "") Then ' OR use whatever string you use for 'ALL' optionsArray.append("type = '" + LBtype.text+ "'") end if if (LBcolor.text <> "") Then optionsArray.append("color = '" + LBcolor.text+ "'") end if if (LBsize.text <> "") Then optionsArray.append("size = '" + LBsize.text + "'") end if options = optionsArray.join(" AND ") if options <> "" Then ' Options were selected. sql = sql + " WHERE " options end if _________________ RB 2009r4 Windows XP Top HMARROQUINC Post subject: Re: Assigning * to listbox selectionPosted: Thu Feb 14, 2013 2:06 pm Joined: Sun Jan 25, 2009 5:11 pm Posts: 427 Location: Guatemala, Central America My suggestion would be to create a function that "builds" the sql statement based on the selections made on the different listboxes. Also, why are you using listboxes for the filter selection? Is the user allowed to enter choices that are not listed in the listbox? Why not use a popupmenu control or a combobox? Anyway... This is something from a project of mine, it's in spanglish so you might need to decipher a bit of it but perhaps it will help. I created a function to build an sql statement based on the selection the user has made on popupmenu controls. See if it helps. Function FilterSearchString() As String // ********************************************************************** // Create the filter string based on the selection the user has made in // the filter popupboxes // ********************************************************************** dim s, st as string dim d as new date // Filter by Date Range select case popDateFilter.text case "Hoy" s = " AND call_date = '" + d.SQLDate + "' " case "Este Mes" dim dfirstday as new date dim dlast as date dfirstday.Day = 1 dlast = mDateTime.GetLastDateOfMonth(d.Year, d.Month) s = " AND call_date BETWEEN '" + dfirstday.SQLDate + "' AND '" + dlast.SQLDate + "' " case "Este Año" dim dfirstday as new date dim dlastday as new date dfirstday.Month = 1 dfirstday.day = 3 dlastday.Month = 12 dlastday.day = 31 s = " AND call_date BETWEEN '" + dfirstday.SQLDate + "' AND '" + dlastday.SQLDate + "' " end select // Filter by Event type if popTypeFilter.Text <> "Todo" then st = popTypeFilter.Text s = s + " AND call_type = '" + st + "' " end if // Filter by Status (Resolved or Not Resolved) select case popStatusFilter.Text case "Resuelto" s = s + " AND call_resolved = 1 " case "No Resuelto" s = s + " AND call_resolved = 0 " end select // Filter by User if popUserFilter.Text <> "Todos" then dim iuid as integer iuid = popUserFilter.RowTag(popUserFilter.ListIndex) s = s + " AND call_to = '" + cstr(iuid) + "' " end if s = s + " ORDER BY call_date DESC " return s End Function I call the function when I want to "filter" the results like this: s = "SELECT * FROM calls WHERE customer_id = " + cstr(self.SelectedCustomerID) + " " // Get the filters applied by the user st = self.FilterSearchString // Convert the SQL string to the appropriate encoding s = s + st s = ConvertEncoding(s, encodings.ISOLatin1) rs = app.DB.SQLSelect(s) if app.db.Error then msgbox "Error al cargar la Lista de Eventos. " + EndOfLine + _ "wMain - Method : LoadEvents " + EndOfLine + _ CStr(app.db.ErrorCode) + " - " + app.db.ErrorMessage quit end if Yes, the error handling has funky messages but this is an in-house project so I can get away with simple messageboxes to display where an error occurred, what method it occured in and the error number and description. Hope it helps _________________ Future RS guru. Ride the world! Top Display posts from previous: All posts1 day7 days2 weeks1 month3 months6 months1 year Sort by AuthorPost timeSubject AscendingDescending Page 1 of 1 [ 3 posts ]
-- Over 1500 classes with 29000 functions in one REALbasic plug-in collection. The Monkeybread Software Realbasic Plugin v9.3. http://www.monkeybreadsoftware.de/realbasic/plugins.shtml [email protected]
