Passing datasource data as parameter RSS

• • Reply
  

  dudubear11

  Member

  1 Points

  3 Posts

  Passing datasource data as parameter

  May 02, 2012 11:45 AM|LINK

  i've created a datasource1 that returns few list of names from a table in server1.

  now i want to create another datasource2 that select data from another table in server2 and filter the result based on the names on datasource1.

  eg: select * from tableOfServer2 where name in (@listOfNameInDataSource1)

  How can i pass the data of the name column on datasource1 as parameter for query on datasource2?

   

  or is there any other possible way that i can merge 2server's connection string together in sql query to create the desired datasource?

  • Edited by dudubear11 on May 02, 2012 12:05 PM
• • Reply
  

  tusharrs

  Contributor

  3230 Points

  668 Posts

  Re: Passing datasource data as parameter

  May 02, 2012 12:44 PM|LINK

  suppose your query for datasource1 is select name from table1 where name like '%a%'

  then your query for datasource 2 will be

  select * from tableOfServer2 where name in ( select name from table1 where name like '%a%' )

   

  Note: Only be careful that the query in brackets should have only one column (in this example name)

  Regards
  
  ( Mark as Answer if it helps you out )
  
  View my Blog
  • Edited by tusharrs on May 02, 2012 12:46 PM
  • Edited by tusharrs on May 02, 2012 12:46 PM
  • Edited by tusharrs on May 02, 2012 12:47 PM
• • Reply
  

  dudubear11

  Member

  1 Points

  3 Posts

  Re: Passing datasource data as parameter

  May 02, 2012 04:30 PM|LINK

  i dont think this works. table1 is table from server1 and table2 is from table from server2. and the connection string for datasource2 is linked to server2 only and therefore it cant access table of server1.

• • Reply
  

  tusharrs

  Contributor

  3230 Points

  668 Posts

  Re: Passing datasource data as parameter

  May 02, 2012 06:17 PM|LINK

  hi,

  assuming table1 from server1 and table2 from server2

  both table1 and table3 contains fieldname id and name

  table1 following data

  id name

  1  name1

  2  name2

  3  name3

  4  name4

  table2 following data (same as table1)

  id name

  1  name1

  2  name2

  3  name3

  4  name4

  This is for table1 from server1

  <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
  DataSourceID="SqlDataSource1" ondatabound="GridView1_DataBound" >
  <Columns>
  <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name"/>
  </Columns>
  </asp:GridView>

  
  <asp:SqlDataSource ID="SqlDataSource1" runat="server"
  ConnectionString="<%$ ConnectionStrings:tempConnectionString %>"
  SelectCommand="SELECT [Name] FROM [tablename] where id >1" >
  </asp:SqlDataSource>
  
  
  

  This is for table2 from server2

  <asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" >
  <Columns>
  <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name"/>
  </Columns>
  </asp:GridView>

  
  <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
  ConnectionString="<%$ ConnectionStrings:tempConnectionString %>" 
  SelectCommand="SELECT [Name] FROM [tablename] where 1=2" >
  </asp:SqlDataSource>

  In code behind

   

  void bindgrid()
  {
  
  DataView dv2 = (DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty);
  string strfilter = "";
  DataTable dt = dv2.ToTable();
  for (int i = 0; i <= dt.Rows.Count - 1; i++)
  {
  if (strfilter != "")
  strfilter += ",";
  strfilter += "'" + dt.Rows[i]["Name"].ToString().Trim() + "'";
  }

  SqlDataSource2.SelectCommand = "SELECT [Name] FROM [tablename] where name in (" + strfilter + ") ";
  DataView dv = (DataView)SqlDataSource2.Select(DataSourceSelectArguments.Empty);
  GridView1.DataSource = dv.ToTable();
  GridView1.DataBind();
  
  }

  protected void GridView1_DataBound(object sender, EventArgs e)
  {
      bindgrid();

  }

  Regards
  
  ( Mark as Answer if it helps you out )
  
  View my Blog
  • Marked as answer by dudubear11 on May 03, 2012 10:28 AM
• • Reply
  

  dudubear11

  Member

  1 Points

  3 Posts

  Re: Passing datasource data as parameter

  May 03, 2012 07:47 AM|LINK

  tusharrs

  DataView dv2 = (DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty);
  string strfilter = "";
  DataTable dt = dv2.ToTable();
  for (int i = 0; i <= dt.Rows.Count - 1; i++)
  {
  if (strfilter != "")
  strfilter += ",";
  strfilter += "'" + dt.Rows[i]["Name"].ToString().Trim() + "'";
  }

  SqlDataSource2.SelectCommand = "SELECT [Name] FROM [tablename] where name in (" + strfilter + ") ";
  DataView dv = (DataView)SqlDataSource2.Select(DataSourceSelectArguments.Empty);
  GridView1.DataSource = dv.ToTable();
  GridView1.DataBind();

  This works! thank a lot =)

• • Reply
  

  tusharrs

  Contributor

  3230 Points

  668 Posts

  Re: Passing datasource data as parameter

  May 04, 2012 05:01 AM|LINK

  Hi,

  Change the gridview1 to gridview2, the dataview data should be bind to gridview2

   

  DataView dv2 = (DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty);

  string strfilter = "";

  DataTable dt = dv2.ToTable();

  for (int i = 0; i <= dt.Rows.Count - 1; i++)

  {

   if (strfilter != "")

   strfilter += ",";

   strfilter += "'" + dt.Rows[i]["Name"].ToString().Trim() + "'";

  }

  SqlDataSource2.SelectCommand = "SELECT [Name] FROM [tablename] where name in (" + strfilter + ") ";

  DataView dv = (DataView)SqlDataSource2.Select(DataSourceSelectArguments.Empty);

  GridView2.DataSource = dv.ToTable();

  GridView2.DataBind();

  Regards
  
  ( Mark as Answer if it helps you out )
  
  View my Blog