Adding a dummy record in the first row of a recordset
May 6

There are numerous reasons why you should add a dummy record as the first row of a record. You may want to add a choice for the user to select none for example, or change the first row of a dropdown that is populated by a SQL query that has a unique label, such as select.... However, the best way to add a record coming from a database is in the SQL query itself.
Here, I want to add a new row at the top of the recordset that allows the user to choose none. I don't want to have to create a new SubCategory record into the database, and even if I could, the new None value would not be sorted correctly in the first row. Instead, we will create a dummy record as the first record using SQL.
In the inner query, the first select statement will create a new dummy record using SQL, and the second query is appending the data from the database to this record using a UNION ALL. We are also going to alias the column that we are displaying, in this case, the SubCategory, as a new column that we will sort by (SubCategorySort).
The dummy record at the top will have a 0 string as the SubCategorySort value. A zero value will make the dummy record with None appear at the top of the dropdown list. We will then use the SubCategory database column value as the SubCategorySort psuedo column in the subsequent SELECT statement in order to sort the rest of the data by the SubCategory, and will use an order by statement using our dummy SubCategorySort column outside of the inner query to sort the results like so:
ColdFISH is developed by Jason Delmore. Source code and license information available at coldfish.riaforge.org SELECT * FROM ( SELECT NULL as ResourceSubCategoryId, 'None' as SubCategory, '0' as SubCategorySort UNION ALL SELECT ResourceSubCategoryId, SubCategory, SubCategory as SubCategorySort FROM ResourceSubCategory ) InnerQuery ORDER BY SubCategorySort 1SELECT * 2FROM ( 3 SELECT 4 NULL as ResourceSubCategoryId, 5 'None' as SubCategory, 6 '0' as SubCategorySort 7 UNION ALL 8 SELECT 9 ResourceSubCategoryId, 10 SubCategory, 11 SubCategory as SubCategorySort 12 FROM 13 ResourceSubCategory 14 15) InnerQuery 16ORDER BY 17 SubCategorySort |
This entry was posted on May 6, 2020 at 2:15 PM and has received 541 views.
Download Galaxie Blog
Subscribe
Tags
Recent Posts
Recent Comments
Archives
Monthly Archives
CFBloggers Feed
_UNKNOWNTRANSLATION_ |