SQL Union to create Header row for a combobox

[Rob W]

Greetings,

I posted in the vb.net forums as I'm developing a vb.net[2008] app with the
backend database being Access 07 (Connection string:
Microsoft.ACE.OLEDB.12.0).

I want a header row in a combobox with a user friendly message e.g. "---
Please choose a member ----"

I suggested I had looked at both vb.net control settings for a combo box and
SQL opportunities to find a solution.

I got a reply to investigate using the UNION ALL in SQL to create a header
row, having played around in Access query design, I cannot resolve my
problem.

The SQL currently used to populate the combobox is below :-

SELECT M.strMembershipId + ' ' + M.strTitle + ' ' + M.strForeNames + ' ' +
M.strSurname as memLookUp, M.*
FROM tblMembers M ORDER BY M.strMembershipId ASC

I select everything from the tblMembers table as it is used to populate a
form based on the value of the strMembershipId primary field.

The code below ties in the selection from the combobox to the primary field
cboSearchKey.ValueMember = "strMembershipId"

I display the alias field Lookup to the user with the following code:-
cboSearchKey.DisplayMember = "memLookUp"

Now to do this with a union I would have to ensure that the number of
columns remain the same for all of select statements, which I find could be
difficult
as I'm selecting everything from the table, many columns.

Also I don't know how to tie in the valuemember of strMembershipId to the
pseudo header row as it won't have a strMembershipId value.

Hope this problem is clear and someone can provide some assistance of how I
can create a header row, which will always appears at the top.

Thanks
Rob

 

[Douglas J. Steele]

Now to do this with a union I would have to ensure that the number of
columns remain the same for all of select statements, which I find could
be difficult
as I'm selecting everything from the table, many columns.

Unfortunately, there's no way around this. You could always write code that
generates the SQL for you, based on the number of fields in the table in
case it changes.

Also I don't know how to tie in the valuemember of strMembershipId to the
pseudo header row as it won't have a strMembershipId value.

Since all fields must match up, you have no choiice but to assign a value
for strMembershipId for the pseudo row. Choose a value you know will never
occur (like 0 or -1)

 

[Rob W]

Thanks for the confirmation.

I'm going to take a long break away from my pc and then start to write a
query.

 

[Rob W]

Well I created a small test database and table.

The Union statement below creates a header row but for every single row in
the person table.
From testing it appeared the first select MUST always have a FROM clause
though so Im clueless how to create a single row header.

SELECT 0 as PseudoRow, "" as id, "" as firstname, "" as surname
FROM Person p
UNION
SELECT 1, P.*
FROM Person p
ORDER by PseudoRow;

How can I create a SINGLE header row?

Thanks
Rob

 

[Paul Shapiro]

You can either use a table which you know will always contain exactly one
row, or add a criteria which limits output to a single row. I'm not sure
which criteria Access allows but something like this might work:

Select Top 1 0 as PseudoRow, ...
The Top 1 sql clause limits output to a single row.

Or From Person p Where p.personID = (Select Max(personID) From Person).

Access might not accept a scalar subquery for the where clause, in which
case you could change that to:
From Person p
Where Not Exists (
Select * From Person as P2 Where P2.personID > p.personID
)

Or you could add a table of sequential numbers which comes in handy for some
other sql chores as well:
Create Table SequentialNumber
(ID as long Not Null).

Fill that table with sequential numbers up to however many seem useful, and
then use that table to generate your pseudorow with a Where ID=1 clause.

 

[Rob W]

Thanks I used a criteria (aggregate function) to limit output to a single
row as you suggested.

Thanks.

 

[Douglas J. Steele]

Even if there are multiple rows in the Person table, the fact that you're
using UNION will eliminate any duplicates.

You'd only have a problem if you used UNION ALL instead.

 

[Rob W]

Yes, I was mistakenly selecting real data from the table instead of the made
up data for the head row.

Thanks

 

[cf]

Douglas- I thick I did i don't know at with thew art