Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQLSERVER 2000 SQL UNION Expand / Collapse
Author
Message
Posted Thursday, February 17, 2011 5:17 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 5, 2012 12:14 AM
Points: 37, Visits: 50
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:
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.
Post #1066085
Posted Friday, February 18, 2011 12:57 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:22 AM
Points: 1,327, Visits: 4,506
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.



Post #1066174
Posted Friday, February 18, 2011 1:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 5, 2012 12:14 AM
Points: 37, Visits: 50
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.
Post #1066179
Posted Friday, February 18, 2011 1:42 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:57 AM
Points: 1,949, Visits: 8,304
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
Kent user group
Post #1066187
Posted Friday, February 18, 2011 1:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 5, 2012 12:14 AM
Points: 37, Visits: 50

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.
Post #1066189
Posted Friday, February 18, 2011 2:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 5:18 AM
Points: 5,014, Visits: 10,515
Guys, behave yourselves, please.
Don't let this thread go sideways.


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1066196
Posted Friday, February 18, 2011 2:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 5, 2012 12:14 AM
Points: 37, Visits: 50
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.
Post #1066199
Posted Friday, February 18, 2011 2:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:40 AM
Points: 7,001, Visits: 8,439
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

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


- 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
Post #1066207
Posted Friday, February 18, 2011 2:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 5, 2012 12:14 AM
Points: 37, Visits: 50
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.
Post #1066215
Posted Friday, February 18, 2011 2:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 5:18 AM
Points: 5,014, Visits: 10,515
I suggest everybody to stop posting to this thread.
Lennie seems to know everything he needs, and much more.


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1066218
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse