?
Solved

UNION ALL Gridview, 2 unrelated tables

Posted on 2007-11-24
1
Medium Priority
?
689 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 37

Accepted Solution

by:
samtran0331 earned 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

764 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