This is a migrated thread and some comments may be shown as answers.

Point to another sql table at runtime

2 Answers 15 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Felice
Top achievements
Rank 1
Felice asked on 12 Sep 2014, 05:52 AM
I have many sql tables that are all structured in the same identical way (headers and type).
I would like to use one radgrid to work on all these tables (insert, update, delete) selecting the table from a drop down list.
The code here below works only partially, I can select a different table from the drop down list and it is immediately loaded but I cannot perform insert delete update and also sorting and filtering are not working.
What should I do to get all the above working? Is there a demo for such situation?
Moreover, I have added manually the "Edit" and "Delete" columns because I like to have the image buttons for these operations but in this context they both appear on the left side of the grid. I understand the reasons but I would like to get them on the sides (left and right) of the columns.


<div>
          <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged1">
 
              <asp:ListItem Value="Table_321">Table_321</asp:ListItem>
              <asp:ListItem Value="Table_621">Table_621</asp:ListItem>
          </asp:DropDownList>
          <br />
          <br />
          <br />
          <telerik:RadGrid ID="RadGrid1" runat="server" Culture="it-IT" OnNeedDataSource="RadGrid1_NeedDataSource" AllowPaging="True" AllowSorting="True" AllowAutomaticDeletes="True" AllowAutomaticInserts="True" AllowAutomaticUpdates="True">
              <ExportSettings ExportOnlyData="True">
                  <Pdf PageWidth="">
                  </Pdf>
              </ExportSettings>
              <ClientSettings>
                  <Scrolling AllowScroll="True" UseStaticHeaders="True" />
              </ClientSettings>
              <MasterTableView CommandItemDisplay="Top">
                  <CommandItemSettings ShowExportToExcelButton="True" />
                  <Columns>
                      <telerik:GridEditCommandColumn ButtonType="ImageButton">
                          <HeaderStyle Width="30px" />
                      </telerik:GridEditCommandColumn>
                      <telerik:GridButtonColumn ButtonType="ImageButton" Text="Delete" CommandName="Delete" FilterControlAltText="Filter column1 column" ConfirmDialogType="RadWindow" ConfirmText="Do you really want to delete this project and all its content?" UniqueName="Cancel">
                          <HeaderStyle Width="30px" />
                      </telerik:GridButtonColumn>
                  </Columns>
 
                  <EditFormSettings>
                      <EditColumn UniqueName="EditCommandColumn1" FilterControlAltText="Filter EditCommandColumn1 column"></EditColumn>
                  </EditFormSettings>
                  <PagerStyle AlwaysVisible="True" />
              </MasterTableView>
              <PagerStyle AlwaysVisible="True" />
          </telerik:RadGrid>
          <br />
          <br />
 
      </div>
 
      <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:BOMConnectionString %>">
          <DeleteParameters>
              <asp:Parameter Name="Id" Type="Int32" />
          </DeleteParameters>
          <InsertParameters>
              <asp:Parameter Name="Code" Type="Double" />
              <asp:Parameter Name="Description" Type="String" />
              <asp:Parameter Name="Bkgroup" Type="Double" />
              <asp:Parameter Name="Stgroup" Type="Double" />
              <asp:Parameter Name="Quantity" Type="Double" />
              <asp:Parameter Name="BomNote" Type="String" />
              <asp:Parameter Name="UnitEng" Type="String" />
              <asp:Parameter Name="AlternCost" Type="Double" />
          </InsertParameters>
          <UpdateParameters>
              <asp:Parameter Name="Code" Type="Double" />
              <asp:Parameter Name="Description" Type="String" />
              <asp:Parameter Name="Bkgroup" Type="Double" />
              <asp:Parameter Name="Stgroup" Type="Double" />
              <asp:Parameter Name="Quantity" Type="Double" />
              <asp:Parameter Name="BomNote" Type="String" />
              <asp:Parameter Name="UnitEng" Type="String" />
              <asp:Parameter Name="AlternCost" Type="Double" />
              <asp:Parameter Name="Id" Type="Int32" />
          </UpdateParameters>
      </asp:SqlDataSource>
  
and this is the code behind:
protected void DropDownList1_SelectedIndexChanged1(object sender, EventArgs e)
    {
        if (DropDownList1.SelectedIndex > -1)
        {
            RadGrid1.DataSource=null;
            RadGrid1.Rebind();
        }
    }
 
    protected void RadGrid1_NeedDataSource(object sender, GridNeedDataSourceEventArgs e)
    {
        string tableInUse = DropDownList1.SelectedItem.Text;
        RadGrid1.DataSource = SqlDataSource1;
        SqlDataSource1.DeleteCommand = "DELETE FROM [" + tableInUse + "] WHERE [Id] = @Id";
        SqlDataSource1.InsertCommand = "INSERT INTO [" + tableInUse + "] ([Code], [Description], [Bkgroup], [Stgroup], [Quantity], [BomNote], [UnitEng], [AlternCost]) VALUES (@Code, @Description, @Bkgroup, @Stgroup, @Quantity, @BomNote, @UnitEng, @AlternCost)";
        SqlDataSource1.SelectCommand = "SELECT * FROM [" + tableInUse + "] ORDER BY [Code]";
        SqlDataSource1.UpdateCommand = "UPDATE [" + tableInUse + "] SET [Code] = @Code, [Description] = @Description, [Bkgroup] = @Bkgroup, [Stgroup] = @Stgroup, [Quantity] = @Quantity, [BomNote] = @BomNote, [UnitEng] = @UnitEng, [AlternCost] = @AlternCost WHERE [Id] = @Id";
    }

Thank you for supporting,
Felice

2 Answers, 1 is accepted

Sort by
0
Accepted
Konstantin Dikov
Telerik team
answered on 16 Sep 2014, 01:22 PM
Hello Felice,

The approach that you are trying to implement, by setting the SqlDataSource controls as a DataSource control for the grid will not work, since that event will not fire when you initiate an insert or update operation and the grid will lose those settings.

For achieving the desired behavior you should apply those settings within the Page's Load event handler for example and on the SelectedIndexChanged event of the DropDownList control.

For your convenience, following is an example with such functionality, which works correctly on my end:
protected void Page_Load(object sender, EventArgs e)
{
    SetDataSource();
}
 
protected void DropDownList1_SelectedIndexChanged1(object sender, EventArgs e)
{
    if (DropDownList1.SelectedIndex > -1)
    {
        SetDataSource();
        RadGrid1.Rebind();
    }
}
 
private void SetDataSource()
{
    string tableInUse = DropDownList1.SelectedItem.Text;
    RadGrid1.DataSourceID = SqlDataSource1.ID;
    SqlDataSource1.DeleteCommand = "DELETE FROM [" + tableInUse + "] WHERE [Id] = @Id";
    SqlDataSource1.InsertCommand = "INSERT INTO [" + tableInUse + "] ([Code], [Description]) VALUES (@Code, @Description)";
    SqlDataSource1.SelectCommand = "SELECT * FROM [" + tableInUse + "] ORDER BY [Code]";
    SqlDataSource1.UpdateCommand = "UPDATE [" + tableInUse + "] SET [Code] = @Code, [Description] = @Description [Id] = @Id";
}

You could also find the entire page attached to this post.

Hope this helps.


Regards,
Konstantin Dikov
Telerik
 

Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

 
0
Felice
Top achievements
Rank 1
answered on 18 Sep 2014, 02:24 PM
Thats perfect.
Thanks for your support.

Felice
Tags
Grid
Asked by
Felice
Top achievements
Rank 1
Answers by
Konstantin Dikov
Telerik team
Felice
Top achievements
Rank 1
Share this question
or