Solved

UNION ALL Gridview, 2 unrelated tables

Posted on 2007-11-24
1
683 Views
Last Modified: 2012-06-21
I'm trying to join 2 unrelated tables to display some of their data in a gridview. I get an error, saying "A field or property with the name 'PaidServer' was not found on the selected datasource". I think it may be a problem with my gridview coding, but possibly with the SQL statement. Here is my table format and code:

Table 'Expenses'
ExpenseDate, SmallDateTime
ExpenseType, varchar(100)
ExpenseDescription, varchar(300)
ExpenseAmount, money

Table 'Billing'
PaidServer, money


Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs)
     SqlDataSource1.SelectCommand = "SELECT ExpenseDate, ExpenseType, ExpenseDescription, ExpenseAmount FROM Expenses UNION ALL SELECT NULL, NULL, NULL, PaidServer FROM Billing"
     SqlDataSource1.DataBind()
End Sub
 
<asp:GridView ID="GridView1" runat="server" AllowSorting="True" BackColor="White" BorderColor="White" BorderStyle="Ridge" BorderWidth="2px" CellPadding="3" CellSpacing="1"                       DataSourceID="SqlDataSource1" GridLines="None" AutoGenerateColumns="False">
<FooterStyle BackColor="#C6C3C6" ForeColor="Black" />
<RowStyle BackColor="#DEDFDE" ForeColor="Black" />
<SelectedRowStyle BackColor="#9471DE" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#C6C3C6" ForeColor="Black" HorizontalAlign="Right" />
<HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#E7E7FF" />
   <Columns>
   <asp:BoundField DataField="ExpenseDate" Daformatstring="{0:MM/dd/yy}" htmlencode="false"  HeaderText="Date" />
   <asp:BoundField DataField="ExpenseType" HeaderText="Expense Type" />
   <asp:BoundField DataField="ExpenseDescription" HeaderText="Description" />
   <asp:BoundField DataField="ExpenseAmount" HeaderText="Amount" />
   <asp:BoundField DataField="PaidServer" HeaderText="Paid to Server" />
 </Columns>
</asp:GridView>
 
<asp:SqlDataSource id="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ClientConnectionString % ">                   
                </asp:SqlDataSource>

Open in new window

0
Comment
Question by:mlg101
1 Comment
 
LVL 37

Accepted Solution

by:
samtran0331 earned 500 total points
ID: 20343166
The number of columns and column names must match if you want to UNION, try something like this for your SQL:

SELECT ExpenseDate, ExpenseType, ExpenseDescription, ExpenseAmount, '' AS PaidServer FROM Expenses
UNION
SELECT '' AS ExpenseDate, '' AS ExpenseType, '' AS ExpenseDescription, '' AS ExpenseAmount, PaidServer FROM Billing
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question