SQLSERVER 2000 SQL UNION

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » General » SQLSERVER 2000 SQL UNION

15 posts, Page 1 of 2

1 2 »»

SQLSERVER 2000 SQL UNION

Rate Topic

Display Mode

Topic Options

Author

Message

Lennie

Posted Thursday, February 17, 2011 5:17 PM

SSC Rookie

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

Hullo Friend, Tongue

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. Smile

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.

Post #1066085

Ian Scarlett

Posted Friday, February 18, 2011 12:57 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 2:12 AM
Points: 1,322, Visits: 4,400

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

Lennie

Posted Friday, February 18, 2011 1:14 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, April 05, 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

Dave Ballantyne

Posted Friday, February 18, 2011 1:42 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 3:42 AM
Points: 1,943, Visits: 8,228

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

Lennie

Posted Friday, February 18, 2011 1:47 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, April 05, 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

spaghettidba

Posted Friday, February 18, 2011 2:09 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:17 AM
Points: 4,804, Visits: 8,074

Guys, behave yourselves, please.
Don't let this thread go sideways.

Get your two-cent-answer quickly
The Spaghetti DBA

Post #1066196

Lennie

Posted Friday, February 18, 2011 2:14 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, April 05, 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. w00t

Post #1066199

ALZDBA

Posted Friday, February 18, 2011 2:30 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 2:17 AM
Points: 6,862, Visits: 8,049

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

     Jul 13

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

- 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" BigGrin

Need a bit of Powershell? How about this

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

Post #1066207

Lennie

Posted Friday, February 18, 2011 2:39 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, April 05, 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

spaghettidba

Posted Friday, February 18, 2011 2:43 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:17 AM
Points: 4,804, Visits: 8,074

I suggest everybody to stop posting to this thread.
Lennie seems to know everything he needs, and much more.

Get your two-cent-answer quickly
The Spaghetti DBA

Post #1066218

« Prev Topic | Next Topic »

15 posts, Page 1 of 2

1 2 »»

Permissions