Multiple DataKeyNames in Master Detail Gridview.

Question

This code lets you search for a table name in an Oracle database. then when you select a row it puts all the columns for the selected table in the second gridview.

The Code below works if I only use one DatakeyName - "Table_Name" but it will display identical table names with different owners if they exist which is not what I want. I want to pull the details based on two fields - Table_Name and Owner.

I can't figure out how to get the details part to work with two datakeyNames.

 <asp:Label ID="lblTitleSrchOracleTab" runat="server" Text="Search For A Table In Oracle"></asp:Label>
        <br /><br />
        <asp:Label ID="lblOracleTableName" runat="server" Text="Oracle Table Name"></asp:Label>
        <asp:TextBox ID="txtOracleTableName" runat="server"></asp:TextBox>
        <asp:Button ID="btnOracleTableName" runat="server" Text="Search" 
             />
        <br /><br /> 
    <asp:GridView ID="gvOracleTableName" runat="server" CssClass="mGrid" 
            AutoGenerateSelectButton="True" AutoGenerateColumns="False" 
                DataSourceID="sdsOracleTableName" DataKeyNames="Owner,Table_Name" >
            <Columns>
                <asp:BoundField DataField="OWNER" HeaderText="OWNER" SortExpression="OWNER" />
                <asp:BoundField DataField="TABLE_NAME" HeaderText="TABLE_NAME" 
                    SortExpression="TABLE_NAME" />
                <asp:BoundField DataField="NUM_ROWS" HeaderText="NUM_ROWS" 
                    SortExpression="NUM_ROWS" />
                <asp:BoundField DataField="TABLESPACE_NAME" HeaderText="TABLESPACE_NAME" 
                    SortExpression="TABLESPACE_NAME" />
            </Columns>
        </asp:GridView>
        <asp:SqlDataSource ID="sdsOracleTableName" runat="server" 
            ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
            ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" SelectCommand="SELECT Owner, Table_name, Num_Rows, Tablespace_name
    FROM all_tables
    WHERE trim(upper(table_name)) LIKE trim(upper('%' || :TableName || '%'))">
            <SelectParameters>
                <asp:ControlParameter ControlID="txtOracleTableName" Name="TableName" 
                    PropertyName="Text" />
            </SelectParameters>
        </asp:SqlDataSource>
        <br /><br />
        <asp:GridView ID="gvSelectedTableColumns" runat="server" CssClass="mGrid"
            AutoGenerateColumns="False" DataSourceID="sdsgvSelectedTableColumns">
            <Columns>
                <asp:BoundField DataField="OWNER" HeaderText="OWNER" SortExpression="OWNER" />
                <asp:BoundField DataField="TABLE_NAME" HeaderText="TABLE_NAME" 
                    SortExpression="TABLE_NAME" />
                <asp:BoundField DataField="COLUMN_NAME" HeaderText="COLUMN_NAME" 
                    SortExpression="COLUMN_NAME" />
                <asp:BoundField DataField="DATA_TYPE" HeaderText="DATA_TYPE" 
                    SortExpression="DATA_TYPE" />
                <asp:BoundField DataField="DATA_LENGTH" HeaderText="DATA_LENGTH" 
                    SortExpression="DATA_LENGTH" />
                <asp:BoundField DataField="NULLABLE" HeaderText="NULLABLE" 
                    SortExpression="NULLABLE" />
            </Columns>
            <SelectedRowStyle BorderColor="Red" Font-Bold="True" />
        </asp:GridView>
        <asp:SqlDataSource ID="sdsgvSelectedTableColumns" runat="server" 
            ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 


            ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" SelectCommand="SELECT Owner, table_name, column_name, Data_Type, Data_Length, Nullable 
    FROM all_tab_columns 
    WHERE trim(upper(Owner)) =trim(upper(:SelectedOwner)) AND
    trim(upper(table_name)) =trim(upper(:SelectedTableName)) 
    ">
            <SelectParameters>
                <asp:ControlParameter ControlID="gvOracleTableName" Name="SelectedOwner" 
                    PropertyName="SelectedValue" />
                <asp:ControlParameter ControlID="gvOracleTableName" DefaultValue="" 
                    Name="SelectedTableName" PropertyName="SelectedValue" />
            </SelectParameters>
        </asp:SqlDataSource>

Answer 1

Can't you extend the where clause?

So

from all_tables where ... and owner = 'MYOWNER'

or

select from user_tables instead of all_tables.

Answer 2

that's what this is

WHERE trim(upper(Owner)) =trim(upper(:SelectedOwner)) AND
trim(upper(table_name)) =trim(upper(:SelectedTableName))

my problem is that when you use the sqlDataSource wizard to connect the paramters to the gridview control, it doesn't let you select which datakey of the gridview to bind it to.

Answer 3

I finally figured this one out. You can't Mighty Mouse it - it takes actual code! Specifically the gridview_SelectedIndexChanged event.

Here's how I did it.

  protected void gvOracleTableName_SelectedIndexChanged(object sender, EventArgs e)
        {
            string SelectedOwner;

            SelectedOwner = gvOracleTableName.SelectedRow.Cells[1].Text ;

            string SelectedTableName;
            SelectedTableName = gvOracleTableName.SelectedRow.Cells[2].Text;

            lblTest.Text = SelectedOwner + " " + SelectedTableName;





            string strConn, strSQL;
            strConn = @"";
            strSQL = @"SELECT Owner, table_name, column_name, Data_Type, Data_Length, Nullable FROM all_tab_columns WHERE trim(upper(Owner)) =trim(upper(:SelectedOwner)) AND trim(upper(table_name)) =trim(upper(:SelectedTableName))";
            using (OracleConnection cn = new OracleConnection(strConn))
            {
                OracleCommand cmd = new OracleCommand(strSQL, cn);

                cmd.Parameters.AddWithValue(":SelectedOwner", SelectedOwner);
                cmd.Parameters.AddWithValue(":SelectedTableName", SelectedTableName);

                cn.Open();

                OracleDataReader rdr = cmd.ExecuteReader();

                gvSelectedTableColumns.DataSource = rdr;
                gvSelectedTableColumns.DataBind();

                cn.Close();
            }

        }