February 17, 2011 at 5:17 pm
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.
February 18, 2011 at 12:57 am
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.
February 18, 2011 at 1:14 am
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.
February 18, 2011 at 1:42 am
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.
February 18, 2011 at 1:47 am
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.
February 18, 2011 at 2:09 am
Guys, behave yourselves, please.
Don't let this thread go sideways.
-- Gianluca Sartori
February 18, 2011 at 2:14 am
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:
February 18, 2011 at 2:30 am
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
February 18, 2011 at 2:39 am
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.
February 18, 2011 at 2:43 am
I suggest everybody to stop posting to this thread.
Lennie seems to know everything he needs, and much more.
-- Gianluca Sartori
February 18, 2011 at 2:53 am
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.
February 18, 2011 at 4:48 am
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.
February 18, 2011 at 7:19 am
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
February 18, 2011 at 7:39 am
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.
February 18, 2011 at 3:59 pm
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