InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 01:10:27 GMT20http://www.sqlservercentral.com/Forums/Topic1066085-9-1.aspx[b][size="4"]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.[/size][/b]Fri, 18 Feb 2011 15:59:20 GMTLenniehttp://www.sqlservercentral.com/Forums/Topic1066085-9-1.aspx[quote][b]GSquared (2/18/2011)[/b][hr][quote][b]David Burrows (2/18/2011)[/b][hr][code="other"]string strsql = "";strsql += " Select '' AS [CustomerId],' ---Select Customer --- ' AS [CompanyName] ";strsql += " Union Select CustomerId, CompanyName ";strsql += " From testCustomers ";strsql += " Order by CompanyName "; [/code][/quote]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.[/quote]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)Fri, 18 Feb 2011 07:39:31 GMTDavid Burrowshttp://www.sqlservercentral.com/Forums/Topic1066085-9-1.aspx[quote][b]David Burrows (2/18/2011)[/b][hr][code="other"]string strsql = "";strsql += " Select '' AS [CustomerId],' ---Select Customer --- ' AS [CompanyName] ";strsql += " Union Select CustomerId, CompanyName ";strsql += " From testCustomers ";strsql += " Order by CompanyName "; [/code][/quote]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.Fri, 18 Feb 2011 07:19:03 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1066085-9-1.aspx[code="other"]string strsql = "";strsql += " Select '' AS [CustomerId],' ---Select Customer --- ' AS [CompanyName] ";strsql += " Union Select CustomerId, CompanyName ";strsql += " From testCustomers ";strsql += " Order by CompanyName "; [/code]Fri, 18 Feb 2011 04:48:45 GMTDavid Burrowshttp://www.sqlservercentral.com/Forums/Topic1066085-9-1.aspx[size="5"]Gianluca Sartori :cool:You behave yourself pleaseDon't let your accusation on this thread go sideway.Be careful in your driving [/size]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.Fri, 18 Feb 2011 02:53:50 GMTLenniehttp://www.sqlservercentral.com/Forums/Topic1066085-9-1.aspxI suggest everybody to stop posting to this thread.Lennie seems to know everything he needs, and much more.Fri, 18 Feb 2011 02:43:33 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1066085-9-1.aspxALZDBAIan 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.Fri, 18 Feb 2011 02:39:20 GMTLenniehttp://www.sqlservercentral.com/Forums/Topic1066085-9-1.aspx[b]Of course Ian is correct ![/b]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.Fri, 18 Feb 2011 02:30:39 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic1066085-9-1.aspxGianluca 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:Fri, 18 Feb 2011 02:14:57 GMTLenniehttp://www.sqlservercentral.com/Forums/Topic1066085-9-1.aspxGuys, behave yourselves, please.Don't let this thread go sideways.Fri, 18 Feb 2011 02:09:50 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1066085-9-1.aspxHullo Dave BallantyneThank you for not helpingThat proved that you don't have the knowledge or skill set.You should post your problem here to receive help.Fri, 18 Feb 2011 01:47:56 GMTLenniehttp://www.sqlservercentral.com/Forums/Topic1066085-9-1.aspxSince your last thread went a little awry,[url]http://www.sqlservercentral.com/Forums/Topic984249-9-1.aspx[/url] personally, im not inclined to help here.Karma my friend.Fri, 18 Feb 2011 01:42:11 GMTDave Ballantynehttp://www.sqlservercentral.com/Forums/Topic1066085-9-1.aspxHullo Ten Centuries,This is one decade Lennie.[b]Your suggestion and statement Quotaton[/b]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 [b]System.Data.DataRowView[/b]in the COMBOXBOX.Fri, 18 Feb 2011 01:14:31 GMTLenniehttp://www.sqlservercentral.com/Forums/Topic1066085-9-1.aspxWe 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:-[code="plain"]Select ' ---Select Customer --- '[b],[/b] 'CompanyName' [/code]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.Fri, 18 Feb 2011 00:57:29 GMTIan Scarletthttp://www.sqlservercentral.com/Forums/Topic1066085-9-1.aspxHullo Friend, :-PI 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 haveequal 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.Thu, 17 Feb 2011 17:17:47 GMTLennie