dcsimg


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Populating a combo box with a stored procedure that contains a UNION

  1. #1
    Bob Guest

    Populating a combo box with a stored procedure that contains a UNION

    I've got a combo box on a form I'm trying to populate with a stored procedure
    that uses a UNION.

    My code is:

    Private Sub PopulateParentCompany()
    Dim SqlCmd As New SqlCommand()
    Dim SqlConn As New SqlConnection(ConnectionString)
    Dim Sqlda As SqlDataAdapter
    Dim Sqlds As New DataSet()

    With SqlCmd
    .Connection = SqlConn
    .CommandText = "spGetParentCompanyNameList"
    .CommandType = CommandType.StoredProcedure
    End With
    Sqlda = New SqlDataAdapter(SqlCmd)
    Sqlda.Fill(Sqlds, "tblParentCompany")
    ParentCompany.DataSource = Sqlds.Tables("tblParentCompany").DefaultView
    ParentCompany.DisplayMember = "Name"
    End Sub

    My stored procedure is:
    SET NOCOUNT ON

    SELECT 0 AS ID
    , '<All>' AS NAME
    FROM tblParentCompany
    UNION
    SELECT Id
    , Name
    FROM tblParentCompany
    ORDER BY Name

    When I run the code, the combo box gets populated with entries that all
    contain the value, "SYSTEM.DATA.DATAROWVIEW".

    As you can guess, tblParentCompany is simply a lookup table to map Ids to
    names.

    Obviously, one way to solve the problem would be to insert a row with an Id
    of 0 and Name of "<All>", but this is an existing system that was originally
    designed with Access and is still used by Access, so the impact of making
    that change is unknown.

    So, has anyone successfully populated a combo box in this manner?

    Thanks,

    Bob

  2. #2
    Rune Bivrin Guest

    Re: Populating a combo box with a stored procedure that contains a UNION

    Suggestions inline:

    > Sqlda = New SqlDataAdapter(SqlCmd)
    > Sqlda.Fill(Sqlds, "tblParentCompany")
    > ParentCompany.DataSource = Sqlds.Tables("tblParentCompany").DefaultView


    Remove the ".DefaultView" part.

    > ParentCompany.DisplayMember = "Name" End Sub
    >
    > My stored procedure is:
    > SET NOCOUNT ON
    >
    > SELECT 0 AS ID
    > , '<All>' AS NAME
    > FROM tblParentCompany

    Remove the FROM clause, or you'll get as many "0, <All>" rows as there are
    rows in tblParentCompany.

    > UNION

    Use UNION ALL to let SQL Server not worry about duplicate rows.

    > SELECT Id
    > , Name
    > FROM tblParentCompany
    > ORDER BY Name
    >


    Rune Bivrin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center