assign Gridview with 2 datasources

usually you set the datasourceID equal to the sqldatasourceID

something like
GrdCustomer.DatasourceID = sqldatasource.id

and then databind after that

Hello SaraDob.
If you have sqldatasource defined on the aspx page you can change it from your vb code like this :

 GridView1.DataSourceID = "Name_of_sql_data_source"
 GridView1.DataBind()

I hope this help.

Oh sorry, I did not saw that you have got a dynamic value in your query string, in that case you can do this in the same way but you have to define a form or session parametr in your select string.
For example :

 
  <asp:SqlDataSource ID="QueryStringName" runat="server" ConnectionString="<%$ ConnectionStrings:NameOfConnectionString %>"
        SelectCommand="GridViewName" SelectCommandType="Text">
        <SelectParameters>
            <asp:FormParameter FormField="txtSearchCustomer.Text" Name="txt" Type="String" />
        </SelectParameters>
    </asp:SqlDataSource><selectparameters>
    </selectparameters>
and from the vb file :

 GridViewName.DataSourceID = "QueryStringName"
 GridViewName.DataBind()

One more time correctly :

  <asp:SqlDataSource ID="DataSourceName" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringName %>"
        SelectCommand="GridViewName" SelectCommandType="Text">
        <SelectParameters>
            <asp:FormParameter FormField="txtSearchCustomer.Text" Name="txt" Type="String" />
        </SelectParameters>
    </asp:SqlDataSource>

well..lot of suggestions.. will try and get back in few minutes.

ummm..its bit confusing for me..i'm not able to understand the way to assign a diffenet SQL query at different times on same gridview.
Basically on page load i assign SQLQuery1 to datasource.and on button click event i try to assign another SQL query to datasource.
So in button click event, how to make the original SQL query =NULL and assign the new SQl query and then bind to it same GRIDVIEW?

normally I use two sqldatasources and then just switch between them. But maybe this will help
http://social.msdn.microsoft.com/Forums/en-US/asmxandxml/thread/57d2820b-e8b9-4d29-885d-6d401473a338/

Ok.  I see what's going on.

There are two ways to defined the data source:  one is to assign a data source object (like a DataSet) to the DataSource property; the other is to assign the ID of a DataSource object to the DataSourceID property.  It's one or the other.  Your example shows that you are defining both, causing the exception.

If you have two SqlDataSource objects defined on your page, then all you need to do is change the DataSourceID property of the GridView, then call the DataBind() method--and skip the DataSource assignment, which is your problem.

    ' Assign the ID of the data source
    GrdCustomer.DataSourceID = MyFilterDataSource.ID
    GrdCustomer.DataBind()

You need to remember to set it back somehow.  This may be on the OnClick event of a "reset" button, or on the Page.OnLoad event, which is always executed before the OnClick event of your "filter search" button.

If you post the code on your aspx page, the mark-up where you declare your controls, I'll be able to help more.

     -dZ.

ok, could please you send :

1. your current GRIDVIEW tag line  <asp:Gridview id=" ....... >
2. your current SQLQuery1 aspx code <asp:SqlDataSource  ... >
3. your  button click event code
4. your page load event code

Just see if this link is helpful..

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.filterexpression.aspx

Hi,
Thanks for taking intrest in thi post and willing assistance.
I have posted both aspx page and the code behind
Please someone can solve this ASAP...appreciated.
 

<asp:GridView ID="GrdCustomer" runat="server" AutoGenerateColumns="False" 
        DataKeyNames="Customerid" DataSourceID="SQlCustomers" onselectedindexchanged="GrdCustomer_SelectedIndexchanged" 
                AllowPaging="True" 
                AllowSorting="True" Width="599px" EnableViewState="False">
        <Columns>
        <asp:commandfield showselectbutton="true"
            headertext="Select Customer"/>
            <asp:BoundField DataField="Customerid" InsertVisible="false" HeaderText="Customerid" />
            <asp:BoundField DataField="Company" HeaderText="Company" />
            <asp:BoundField DataField="City" HeaderText="City"/>
            <asp:BoundField DataField="State" HeaderText="State"/>
            <asp:BoundField DataField="Country" HeaderText="Country"/>
        </Columns>
    </asp:GridView>
    <asp:SqlDataSource ID="SQlCustomers" runat="server" 
        ConnectionString="<%$ ConnectionStrings:SalesRepActivityConnectionString %>" 
        SelectCommand="Select Customers.Customerid,Customers.Companyname As Company,Customers.City,Customers.State,StateProvince.Countryregionname as Country from Customers,Stateprovince where Customers.Stateprovinceid=StateProvince.Stateprovinceid order By CompanyName ASC">
    </asp:SqlDataSource>
    
      
    <table><tr><td>
        <asp:TextBox ID="txtSearchCustomer" runat="server"></asp:TextBox>
        <asp:RequiredFieldValidator
            ID="ReqCustomer" runat="server" ErrorMessage="*" 
            ControlToValidate="txtSearchCustomer"></asp:RequiredFieldValidator></td>
       <td><asp:Button ID="BtnCustSearch" runat="server" Text="Search" /></td></tr></table>
 
'Codebehind-==================
 
 Protected Sub BtnCustSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnCustSearch.Click
     
        Dim StrCustomerCheck As String
        Dim CmdCustometSearch As New SqlCommand
 
        Dim SerachValue = txtSearchCustomer.Text
 
 
        StrCustomerCheck = "Select Customers.Customerid,Customers.Companyname As Company,Customers.City,Customers.State,StateProvince.Countryregionname as Country from Customers,Stateprovince where Customers.Stateprovinceid=StateProvince.Stateprovinceid and CompanyName LIKE '%SerachValue%' order By CompanyName ASC"
        SQlCustomers.SelectCommand = StrCustomerCheck
        GrdCustomer.DataBind()
        
        MyConn.Close()
    End Sub

Select allOpen in new window

Your code should work:  calling the DataBind() method on the GridView should retrieve the new data from the SqlDataSource control.

If it doesn't try the following, which programmatically retreives the new values.

   -dZ.

 Protected Sub BtnCustSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnCustSearch.Click
     
        Dim StrCustomerCheck As String
        Dim SerachValue = txtSearchCustomer.Text
 
        StrCustomerCheck = "Select Customers.Customerid,Customers.Companyname As Company,Customers.City,Customers.State,StateProvince.Countryregionname as Country from Customers,Stateprovince where Customers.Stateprovinceid=StateProvince.Stateprovinceid and CompanyName LIKE '%SerachValue%' order By CompanyName ASC"
        SQlCustomers.SelectCommand = StrCustomerCheck
        GrdCustomer.DataSource = SQlCustomers.Select(DataSourceSelectArguments.Empty)
        GrdCustomer.DataBind()
    End Sub

Select allOpen in new window

I'm getting the same error dropzone:
Both DataSource and DataSourceID are defined on 'GrdCustomer' .....:((

Is that with your code or with the sample I just offered?  If the latter, you'll need to set
    GrdCustomer.DataSourceId = Nothing
in order for it to avoid that error.

    -dZ.

Sorry sorry. i overlooked it.. now that i included this, ...But When i click on search, i get no error but the gridview not seen.ummmmmmmm??
the code behind looks like this

Dim SerachValue = txtSearchCustomer.Text
 
StrCustomerCheck = "Select Customers.Customerid,Customers.Companyname As Company,Customers.City,Customers.State,StateProvince.Countryregionname as Country from Customers,Stateprovince where Customers.Stateprovinceid=StateProvince.Stateprovinceid and CompanyName LIKE '%SerachValue%' order By CompanyName ASC"

SQlCustomers.SelectCommand = StrCustomerCheck
GrdCustomer.DataSource = SQlCustomers.Select(DataSourceSelectArguments.Empty)
GrdCustomer.DataSourceID = Nothing
GrdCustomer.DataBind()
 

davrob60:,
Will changing the SQL stement make an impact on Gridview's display..ummm
Let me try

the grid view is not seen because you "new" select statement was not returning any record.
Also, remember i suggest the string.format only to troubleshoot. dont leave it this way and replace it with a parameter if you see it work.

DropZone, did you may forgot  to add wildcarts?
SaraDob: here a way to add them
 SQlCustomers.SelectParameters(0).DefaultValue = "%" & SearchValue & "%"
 

Yes, oops!  Thank you.

Here's the parametized query, with the appropriate wildcards:

StrCustomerCheck = "Select Customers.Customerid,Customers.Companyname As Company,Customers.City,Customers.State,StateProvince.Countryregionname as Country from Customers,Stateprovince where Customers.Stateprovinceid=StateProvince.Stateprovinceid and CompanyName LIKE %'@SearchValue'% order By CompanyName ASC"

As you said, davrob60, better used parametized queries instead of string contatenation.  Especially if the string comes from user input, which then allows for SQL Injection attacks.

    -dZ.

But i think the wild card must be inside the quotes?
%'@SearchValue'% -->'%@SearchValue%'

You are right.  I'm just not thinking too well today.  I feel so stupid.

Thanks for catching that.  I should really pay more attention to what I type.

    -dZ.

StrCustomerCheck = "Select Customers.Customerid,Customers.Companyname As Company,Customers.City,Customers.State,StateProvince.Countryregionname as Country from Customers,Stateprovince where Customers.Stateprovinceid=StateProvince.Stateprovinceid and CompanyName LIKE '%@SearchValue%' order By CompanyName ASC"

Select allOpen in new window

The way answer was discussed very well.Thanks .you both were right.Formatting the Select query did the magic.

well, i<m not better than you. i think it should be

%'@SearchValue'% -->'%' & @SearchValue & '%'  
here a link oin the various way of doing that
http://discuss.joelonsoftware.com/default.asp?dotnet.12.429113.20 

Perhaps you may want to mark davrob60's answer ID #23229698, as an assisted answer to share the points.  I did not catch that the select statement returned empty.

    -dZ.

It`s ok, i got points for assisted solution.
but, It`s a fair move to tell this, Thanks.
I`m glad we could help!