Solved

UNION ALL Gridview, 2 unrelated tables

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

911 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now