Solved

UNION ALL Gridview, 2 unrelated tables

Posted on 2007-11-24
1
679 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

821 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