locked
Need help with querying from 2 Tables RRS feed

  • 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>


    Wednesday, January 19, 2011 8:47 PM

Answers

  • 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 + '%')
    ;"

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 20, 2011 12:50 AM

All replies

  • User1867929564 posted

    use union all
    I see only one table

    Wednesday, January 19, 2011 11:51 PM
  • User-1865478366 posted

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


    Thursday, January 20, 2011 12:42 AM
  • 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 + '%')
    ;"

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 20, 2011 12:50 AM
  • User-1865478366 posted

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


    Thursday, January 20, 2011 12:58 AM
  • 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.

    Thursday, January 20, 2011 1:03 AM
  • 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>


    Thursday, January 20, 2011 1:13 AM
  • 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.

    Thursday, January 20, 2011 1:20 AM
  • 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.


    Thursday, January 20, 2011 1:31 AM
  • 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

    Friday, January 21, 2011 3:24 AM