SQL Statement in ASP.NET 2.0 page gives strange error!

[washoetech]

Hello,

I have a search box on my web app that I want to search several tables in my
SQL 2000 DB. I have set up a full text catalog for indexing.

The following Select statement works perfectly but only searches from 1
table:

SELECT * FROM edl WHERE CONTAINS (*, @query)

This is great but it only searched one table not many tables.

So I changed the Select statement to this:

SELECT * FROM edl WHERE CONTAINS (*, @query) UNION SELECT * FROM autometer
WHERE CONTAINS (*, @query)

Now I get this error:

Exception Details: System.Data.SqlClient.SqlException: All queries in an SQL
statement containing a UNION operator must have an equal number of
expressions in their target lists.

Does anyone know what I am doing wrong? I did some research on the web and
found that the UNION operator would add to the result set but it does not
seem to be working.

Thanks,

washoetech

 

[Hans Kesting]

Hello,

I have a search box on my web app that I want to search several
tables in my SQL 2000 DB. I have set up a full text catalog for
indexing.
The following Select statement works perfectly but only searches from
1 table:

SELECT * FROM edl WHERE CONTAINS (*, @query)

This is great but it only searched one table not many tables.

So I changed the Select statement to this:

SELECT * FROM edl WHERE CONTAINS (*, @query) UNION SELECT * FROM
autometer WHERE CONTAINS (*, @query)

Now I get this error:

Exception Details: System.Data.SqlClient.SqlException: All queries in
an SQL statement containing a UNION operator must have an equal
number of expressions in their target lists.

Does anyone know what I am doing wrong? I did some research on the
web and found that the UNION operator would add to the result set but
it does not seem to be working.

Thanks,

washoetech

a UNION returns the result as a single table, so the columns in the queries
*have* to contain the same columns. At least order & type *need* to be the same,
I'm not entirly sure about the actual *name* of the column, it's possible
that the name used in the first query is used.

Hans Kesting