SQLSERVER 2000 SQL UNION

  • Hullo Friend, 😛

    I am encounter problem trying to create a SQL string with UNION in order to display --- Select Customer --- as text on the C#NET combobox but it's not working.

    Please help me, I need your help. 🙂

    Error message: :w00t:

    All queries n an SQL statement containing a UNION operator must have

    equal number of expressions in their target lists

    Here are the coding:

    {

    string strsql = "";

    strsql += " Select ' ---Select Customer --- ' 'CompanyName' ";

    strsql += " Union Select CustomerId, CompanyName ";

    strsql += " From testCustomers ";

    strsql += " Order by CompanyName ";

    try

    {

    sqlconn = new SqlConnection(connstr);

    sqlconn.Open();

    DA = new SqlDataAdapter(strsql, sqlconn);

    DS = new System.Data.DataSet("DS");

    DA.Fill(DS, "cust");

    //---fill combobox

    this.comboBoxInvoice.DisplayMember = "CompanyName";

    this.comboBoxInvoice.ValueMember = "CustomerID";

    this.comboBoxInvoice.DataSource = DS.Tables["cust"];

    }

    Catch (exception ex)

    {

    messagebox.show(ex.message);

    }

    }

    Thank you for your help.

  • We need a lot more information to stand much chance of helping.

    When you say it doesn't work, exactly what do you mean? Are you getting an error message?

    Just looking at the SQL you are generating, I can see a problem... the first select should have a comma between the two columns you are selecting:-

    Select ' ---Select Customer --- ', 'CompanyName'

    There are other potential problems e.g. I suspect CustomerId will be an integer. If it is you will probably get another error when SQL Server tries to convert ' ---Select Customer --- ' to an integer.

  • Hullo Ten Centuries,

    This is one decade Lennie.

    Your suggestion and statement Quotaton

    Select ' ---Select Customer --- ', 'CompanyName'

    There are other potential problems e.g. I suspect CustomerId will be an integer. If it is you will probably get another error when SQL Server tries to convert ' ---Select Customer --- ' to an integer.

    Regarding your statement the CustomerID is integer I am sorry to say that it's wrong. I view the SQL SERVER table TestCustomer the datatype is Varchar.

    This sample coding from you:

    Select ' ---Select Customer --- ', 'CompanyName'

    it create 76 rows of System.Data.DataRowView

    in the COMBOXBOX.

  • Since your last thread went a little awry,

    http://www.sqlservercentral.com/Forums/Topic984249-9-1.aspx

    personally, im not inclined to help here.

    Karma my friend.



    Clear Sky SQL
    My Blog[/url]

  • Hullo Dave Ballantyne

    Thank you for not helping

    That proved that you don't have the knowledge or skill set.

    You should post your problem here to receive help.

  • Guys, behave yourselves, please.

    Don't let this thread go sideways.

    -- Gianluca Sartori

  • Gianluca Sartori

    The posting I did not go sideway as I am a good driver.

    I responded to the helper with the testing result based on their generous sharing of sample coding. It's an honest to tell them the truth of whether it's working or not so that other newbies who may have similar problem will be guide and not misguide and get frustrated.

    And you have to be gentleman to face the fact and stop blaming. :w00t:

  • Of course Ian is correct !

    The problems lies with the dev who tries to fit header rows into his row set and doesn't know how to make his combobox show headers.

    If the dev knew, (s)he would have just queried the table without the union and altered the column names s(he) received in the set or directly selected by providing column aliases that would fit her/his needs.

    Have a nice weekend.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • ALZDBA

    Ian is correct to himself not me because both of us are not from the same company and same department and differnt country.

    My company SQL SERVER table TestCustomers DataType for CustomerID is VarChar so it's not Integer. Unless you don't understand english as I did mentioned that CustomerID datatype is Varchar in respond to IAN suggestion.

    So, you better be careful with your driving and be sensitive to your environment and do not take event for granted.

    As per your statement I do not understand what you are suggesting as there are no sample coding to provide logical explanation like the other helpers did. They are good providing sample coding which they think is correct logical. They are good driver.

  • I suggest everybody to stop posting to this thread.

    Lennie seems to know everything he needs, and much more.

    -- Gianluca Sartori

  • Gianluca Sartori 😎

    You behave yourself please

    Don't let your accusation on this thread go sideway.

    Be careful in your driving

    Once the helper got my coding working,

    I will posting the working codes here to share with others encounter similar problem.

    And you behave yourself and control you abusing statement.

  • string strsql = "";

    strsql += " Select '' AS [CustomerId],' ---Select Customer --- ' AS [CompanyName] ";

    strsql += " Union Select CustomerId, CompanyName ";

    strsql += " From testCustomers ";

    strsql += " Order by CompanyName ";

    Far away is close at hand in the images of elsewhere.
    Anon.

  • David Burrows (2/18/2011)


    string strsql = "";

    strsql += " Select '' AS [CustomerId],' ---Select Customer --- ' AS [CompanyName] ";

    strsql += " Union Select CustomerId, CompanyName ";

    strsql += " From testCustomers ";

    strsql += " Order by CompanyName ";

    You can run into a problem with that, if there's a company name that comes earlier in the sort sequence than the "column header". I usually add a "Sequence" column, hard-coded to 0 for the header, and 1 for the query under the union, and then order by that first, to force the header to stay on top. (Had to do that in a few places in an Access front-end application.)

    Just something to think about on it.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • GSquared (2/18/2011)


    David Burrows (2/18/2011)


    string strsql = "";

    strsql += " Select '' AS [CustomerId],' ---Select Customer --- ' AS [CompanyName] ";

    strsql += " Union Select CustomerId, CompanyName ";

    strsql += " From testCustomers ";

    strsql += " Order by CompanyName ";

    You can run into a problem with that, if there's a company name that comes earlier in the sort sequence than the "column header". I usually add a "Sequence" column, hard-coded to 0 for the header, and 1 for the query under the union, and then order by that first, to force the header to stay on top. (Had to do that in a few places in an Access front-end application.)

    Just something to think about on it.

    Yep knew that, I've had to do the same as you several times 🙂

    But took it that the select text has a leading space would overcome that (unless there is a CompanyName with a leading space)

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Hullo David Burrows,

    Thank you very much for sharing information with me in coding that makes me understand it logically.

    Appreciate your generosity in helping with sample coding.

    You are wonderful. I will recommend you to my office other IT Progammers.

    I will try out your suggestion and once I got it working I will post it here to share with others who may have encounter similar problems.

    I am so glad and happy to meet you here.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply