Need help with querying from 2 Tables

Question

User-1865478366 posted

I need help with getting data to display from 2 tables that contains all the same parameters just that the name of the tables are different.

Code below table is AMAT the second table is EMF:

<style type="text/css">
        body, input{font-family: Arial, Helvetica, sans-serif;}
        body {font-size:76%;}
        input{font-size:100%;}
        .label{float:left; width:80px;text-align:right;padding-right:5px;}
        .row{clear:both}
    </style>

<div class="row">
      <span class="label">Date: </span>
      <asp:TextBox ID="txtDate" runat="server" />
    </div>
    <div class="row">
      <span class="label">EquipmentID: </span>
      <asp:TextBox ID="txtEquipmentID" runat="server" />
    </div>
    <div class="row">
      <span class="label">Chamber: </span>
      <asp:TextBox ID="txtChamber" runat="server" />
    </div>   
    <div class="row">
      <span class="label">Status: </span>
      <asp:TextBox ID="txtStatus" runat="server" />
    </div>
    <div class="row">
      <asp:Button ID="Button1" runat="server" Text="Search"/>
    </div>
    <asp:GridView ID="GridView1" runat="server"
        AutoGenerateColumns="False"
        DataSourceID="AccessDataSource1"
        AllowPaging="True" AllowSorting="True">
        <Columns>
            <asp:BoundField DataField="Date" HeaderText="Date" SortExpression="Date" />
            <asp:BoundField DataField="EquipmentID" HeaderText="EquipmentID"
                SortExpression="EquipmentID" />
            <asp:BoundField DataField="Chamber" HeaderText="Chamber"
                SortExpression="Chamber" />
            <asp:BoundField DataField="Status" HeaderText="Status"
                SortExpression="Status" />
        </Columns>
    </asp:GridView>
    <asp:AccessDataSource ID="AccessDataSource1" runat="server"
        DataFile="~/Database/EqpPd.mdb"
        SelectCommand="SELECT [Date], [EquipmentID], [Chamber], [Status] FROM [AMAT] WHERE 
        (@Date IS NULL OR Date LIKE '%' +  @Date + '%') AND
        (@EquipmentID IS NULL OR EquipmentID LIKE '%' + @EquipmentID + '%') AND 
        (@Chamber IS NULL OR Chamber LIKE '%' + @Chamber + '%') AND 
        (@Status IS NULL OR Status LIKE '%' + @Status + '%');"
        CancelSelectOnNullParameter="False"
        onselecting="AccessDataSource1_Selecting">
      <SelectParameters>
        <asp:ControlParameter
          ControlID="txtDate"
          Name="Date"
          Type="DateTime" />
        <asp:ControlParameter
          ControlID="txtEquipmentID"
          Name="EquipmentID"
          Type="String" />
        <asp:ControlParameter
          ControlID="txtChamber"
          Name="Chamber"
          Type="String" />
        <asp:ControlParameter
          ControlID="txtStatus"
          Name="Status"
          Type="String" />
      </SelectParameters>
    </asp:AccessDataSource>

Answer 1

User1867929564 posted

use union all
I see only one table

Answer 2

User-1865478366 posted

i just need to know which line to insert the code for the 2nd table

Answer 3

User1867929564 posted

 SelectCommand="SELECT [Date], [EquipmentID], [Chamber], [Status] FROM [AMAT] WHERE   (@Date IS NULL OR Date LIKE '%' +  @Date + '%') AND  (@EquipmentID IS NULL OR EquipmentID LIKE '%' + @EquipmentID + '%') AND   (@Chamber IS NULL OR Chamber LIKE '%' + @Chamber + '%') AND  (@Status IS NULL OR Status LIKE '%' + @Status + '%');"

Here first make ur query in mdb's query designer
then

SelectCommand="SELECT [Date], [EquipmentID], [Chamber], [Status] FROM [AMAT] WHERE  
        (@Date IS NULL OR Date LIKE '%' +  @Date + '%') AND 
        (@EquipmentID IS NULL OR EquipmentID LIKE '%' + @EquipmentID + '%') AND  
        (@Chamber IS NULL OR Chamber LIKE '%' + @Chamber + '%') AND  
        (@Status IS NULL OR Status LIKE '%' + @Status + '%')
union all 
SELECT [Date], [EquipmentID], [Chamber], [Status] FROM [AMAT1] WHERE  
        (@Date IS NULL OR Date LIKE '%' +  @Date + '%') AND 
        (@EquipmentID IS NULL OR EquipmentID LIKE '%' + @EquipmentID + '%') AND  
        (@Chamber IS NULL OR Chamber LIKE '%' + @Chamber + '%') AND  
        (@Status IS NULL OR Status LIKE '%' + @Status + '%')
;"

Answer 4

User-1865478366 posted

i tried the code but doesn't display the data from 2nd table only able to display data from 1st table

Answer 5

User1867929564 posted

instead of copying try to understand the concept.
did you change the query which is after union all.
column number and datatype in both select should be same.
show your query.

Answer 6

User-1865478366 posted

<asp:AccessDataSource ID="AccessDataSource1" runat="server"
        DataFile="~/Database/EqpPd.mdb"
        SelectCommand="SELECT [Date], [EquipmentID], [Chamber], [Status] FROM [AMAT] WHERE  
        (@Date IS NULL OR Date LIKE '%' +  @Date + '%') AND
        (@EquipmentID IS NULL OR EquipmentID LIKE '%' + @EquipmentID + '%') AND  
        (@Chamber IS NULL OR Chamber LIKE '%' + @Chamber + '%') AND  
        (@Status IS NULL OR Status LIKE '%' + @Status + '%')
        UNION ALL
        SELECT [Date], [EquipmentID], [Chamber], [Status] FROM [Novellus] WHERE  
        (@Date IS NULL OR Date LIKE '%' +  @Date + '%') AND
        (@EquipmentID IS NULL OR EquipmentID LIKE '%' + @EquipmentID + '%') AND  
        (@Chamber IS NULL OR Chamber LIKE '%' + @Chamber + '%') AND  
        (@Status IS NULL OR Status LIKE '%' + @Status + '%');"
        CancelSelectOnNullParameter="False"
        onselecting="AccessDataSource1_Selecting">
      <SelectParameters>
        <asp:ControlParameter
          ControlID="txtDate"
          Name="Date"
          Type="DateTime" />
        <asp:ControlParameter
          ControlID="txtEquipmentID"
          Name="EquipmentID"
          Type="String" />
        <asp:ControlParameter
          ControlID="txtChamber"
          Name="Chamber"
          Type="String" />
        <asp:ControlParameter
          ControlID="txtStatus"
          Name="Status"
          Type="String" />
      </SelectParameters>
    </asp:AccessDataSource>

Answer 7

User1867929564 posted

do you really hv value in both table ?
Is it working ?Is there any error?then post error msg .
It look fine to me.

Answer 8

User-1865478366 posted

there's no error msg the search gridview just did not display anything from the 2nd table. There's value / data in both tables, and using visual studio 2008.

Answer 9

User-1199946673 posted

there's no error msg the search gridview just did not display anything from the 2nd table

 

Did you try to remove the first part to see if the seconde query returns any records?

By the way, I see you're using the LIKE operator with all fields. DO you understand that the LIKE operator should be used with TEXT fields only, it shouldn't be used with date- and numeric fields