locked
Use same datasource for 2 Gridviews? RRS feed

  • Question

  • User-1063839557 posted

    I have a gridview that is working as intended, and the SQL query was written by someone one else.  I have been asked to take one of the items returned in that query - Check List - and separate it from the other items.

    I thought I would be able to copy and paste the gridview and query, rename them, and filter for Check List only.  I used the Query builder to do this, but the new gridview still returns all the items instead of just the gridview.

    Is this possible?  I am sure there is an easier way to do this but like my name indicates, I am a Noob, and the person who wrote the query is not available to help.

    Thanks!

     

    The attached code is copied from the first instance of this query.  The only thing different between the two (other than being named different) is I used the query builder to filter on Check List.

     

    <asp:SqlDataSource ID="sdschecklistonly" runat="server" 
                        ConnectionString="<%$ ConnectionStrings:MYConnectionString %>" 
                                            
                        SelectCommand="SELECT tblContractDocuments.FileName, tblContractDocuments.ContractDocument, tblContractDocuments.ContractNumber, tblGradeCrossingContract.DOTContractSID, tblContractDocuments.ContractDocumentType, tblContractDocuments.ContractDocumentID, tblContractDocumentTypes.ContractTypeID, tblContractDocumentTypes.ContractTypeName FROM tblContractDocuments INNER JOIN tblGradeCrossingContract ON tblContractDocuments.ContractNumber = tblGradeCrossingContract.ContractNumber CROSS JOIN tblContractDocumentTypes WHERE (tblGradeCrossingContract.DOTContractSID = @DOTContractSID) AND (tblContractDocumentTypes.ContractTypeName = 'Check List')">
                        <SelectParameters>
                            <asp:Parameter Name="DOTContractSID" />
                        </SelectParameters>
                    </asp:SqlDataSource>


     

    Monday, January 10, 2011 10:03 AM

Answers

  • User-1083922933 posted

    hi

    you're almost there

    in the tblContractDocumentTypes table the primary key column is ContractTypeID

    now please find the corresponding foriegn key column in the tblContractDocuments table and correct the join a gave you above accordingly.

    I mean let's say that the foriegn key column name is : x then the join be :

    inner join tblContractDocumentTypes on
    tblContractDocumentTypes.ContractTypeID = tblContractDocuments.x


    If this Helps Please mark as an Answer
    Good luck

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 10, 2011 10:14 PM

All replies

  • User-1083922933 posted

    Hi


    I'm afraid there is a missing Join between the tblContractDocuments table and the tblContractDocumentTypes table and perhaps it's the ContractTypeID column

    so Please in your query text replace :

    CROSS JOIN tblContractDocumentTypes

    with :

    inner join tblContractDocumentTypes on
    tblContractDocumentTypes.ContractTypeID = tblContractDocuments.ContractTypeID


    If This Helps Please Mark as an Answer

    Good luck

    Monday, January 10, 2011 10:59 AM
  • User-1063839557 posted

    Thank you for the response.  I made the changes and it is still pull all the documents back instead of just the check list.  Here is the query after your changes.  Thanks!

    <asp:SqlDataSource ID="sdschecklistonly" runat="server"
                        ConnectionString="<%$ ConnectionStrings:MYConnectionString %>"
    SelectCommand="SELECT tblContractDocuments.FileName, tblContractDocuments.ContractDocument, tblContractDocuments.ContractNumber, tblGradeCrossingContract.DOTContractSID, tblContractDocuments.ContractDocumentType, tblContractDocuments.ContractDocumentID, tblContractDocumentTypes.ContractTypeID, tblContractDocumentTypes.ContractTypeName FROM tblContractDocuments INNER JOIN tblGradeCrossingContract ON tblContractDocuments.ContractNumber = tblGradeCrossingContract.ContractNumber INNER JOIN tblContractDocumentTypes ON tblContractDocumentTypes.ContractTypeID = tblContractDocuments.ContractTypeID WHERE (tblGradeCrossingContract.DOTContractSID = @DOTContractSID) AND (tblContractDocumentTypes.ContractTypeID = 8)">
                        <SelectParameters>
                            <asp:ControlParameter ControlID="gvDOTContractSID" Name="DOTContractSID"
                                PropertyName="SelectedValue" />
                        </SelectParameters>
                    </asp:SqlDataSource>


     

    Monday, January 10, 2011 11:28 AM
  • User-1083922933 posted

    Hi


    the query has three tables and two joins and it's ok

    Please make sure the DataSourceID property of the new GridView is set to the name of the New dataSource which is sdschecklistonly in your case


    If This Helps Please Mark as an Answer

    Good luck


    Monday, January 10, 2011 11:46 AM
  • User-1063839557 posted

     Thank you for your patience.  You were right that my new gridview was still set to the previous datasource.  I got the checklist only gridview set correctly and am now getting "Invalid column name "ContractTypeID". 

    I went back to the original query and it is not having this problem.  I copied the protected sub from the code behind and inserted it into the protected sub for sdschecklistonly and that did not help.

    I appreciate your help!

     

     

    Monday, January 10, 2011 3:14 PM
  • User-1083922933 posted

    hi

    you're almost there

    in the tblContractDocumentTypes table the primary key column is ContractTypeID

    now please find the corresponding foriegn key column in the tblContractDocuments table and correct the join a gave you above accordingly.

    I mean let's say that the foriegn key column name is : x then the join be :

    inner join tblContractDocumentTypes on
    tblContractDocumentTypes.ContractTypeID = tblContractDocuments.x


    If this Helps Please mark as an Answer
    Good luck

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 10, 2011 10:14 PM