Binding to Gridview

Discussion in 'ASP .Net' started by Mike, Oct 7, 2006.

  1. Mike

    Mike Guest

    I'm having trouble getting a gridview to bind. I probably missing something
    completely obvious and would appreciate any help on offer.

    I'm passing parameters via querystring, and have created a stored proc as
    follows:

    qGetSearchResults:

    SELECT
    Adverts.AdvertID,
    Location.Location,
    Jobtitle.JobTitle,
    Speciality.Speciality FROM (((Adverts
    INNER JOIN Employer ON Adverts.EmployerID = Employer.EmployerID)
    INNER JOIN Jobtitle ON Adverts.JobTitleID = Jobtitle.JobTitleID)
    INNER JOIN Location ON Employer.LocationID = Location.LocationID)
    INNER JOIN Speciality ON Adverts.SpecialityID = Speciality.SpecialityID
    WHERE
    (@JobTitleID IS NULL OR Adverts.JobTitleID = @JobTitleID)
    AND (@JobTitleID IS NULL OR Adverts.JobTitleID = @JobTitleID)
    AND (@SpecialityID IS NULL OR Adverts.SpecialityID = @SpecialityID)
    AND (@LocationID IS NULL OR Employer.LocationID = @LocationID)
    AND (@KeyWords IS NULL OR Adverts.InfoText LIKE '%' + @KeyWords + '%')
    AND (((Adverts.StartDate)<=GetDate())
    AND (Adverts.EndDate)>=GetDate())
    ORDER BY Location.Location

    I have verified this works as expected in the Query Designer.

    On the page, I have placed a DataSource Control and a GridView. The code
    for both is as follows:

    <asp:GridView ID="gvSearchResults" runat="server" AllowSorting="True"
    AutoGenerateColumns="False"
    DataKeyNames="AdvertID" DataSourceID="dsSearchResults">
    <Columns>
    <asp:CommandField ShowSelectButton="True" />
    <asp:BoundField DataField="AdvertID" HeaderText="AdvertID"
    InsertVisible="False"
    ReadOnly="True" SortExpression="AdvertID" />
    <asp:BoundField DataField="Location" HeaderText="Location"
    SortExpression="Location" />
    <asp:BoundField DataField="JobTitle" HeaderText="JobTitle"
    SortExpression="JobTitle" />
    <asp:BoundField DataField="Speciality" HeaderText="Speciality"
    SortExpression="Speciality" />
    </Columns>
    </asp:GridView>

    <asp:SqlDataSource ID="dsSearchResults" runat="server"
    ConnectionString="<%$ ConnectionStrings:xxxx %>"
    SelectCommand="qGetSearchResults" SelectCommandType="StoredProcedure">
    <SelectParameters>
    <asp:QueryStringParameter Name="JobTitleID" QueryStringField="JobTitleID"
    Type="Int32" />
    <asp:QueryStringParameter Name="SpecialityID"
    QueryStringField="SpecialityID" Type="Int32" />
    <asp:QueryStringParameter Name="LocationID" QueryStringField="LocationID"
    Type="Int32" />
    <asp:QueryStringParameter Name="KeyWords" QueryStringField="KeyWords"
    Type="String" />
    </SelectParameters>
    </asp:SqlDataSource>

    If I navigate to the page using a URL and querystring like this:

    searchResults.aspx?LocationID=&SpecialityID=&KeyWords=&JobTitleID=35

    the GridView refuses to appear, despite the fact that executing the Stored
    Proc in SQL Server gives me 10 results.

    What have I missed?


    Mike
     
    Mike, Oct 7, 2006
    #1
    1. Advertising

  2. Mike

    Mr Struggler Guest

    You need to do a DataBind for the control sometime before the page renders.


    myDataGrid.DataBind




    "Mike" <> wrote in message
    news:...
    > I'm having trouble getting a gridview to bind. I probably missing
    > something completely obvious and would appreciate any help on offer.
    >
    > I'm passing parameters via querystring, and have created a stored proc as
    > follows:
    >
    > qGetSearchResults:
    >
    > SELECT
    > Adverts.AdvertID,
    > Location.Location,
    > Jobtitle.JobTitle,
    > Speciality.Speciality FROM (((Adverts
    > INNER JOIN Employer ON Adverts.EmployerID = Employer.EmployerID)
    > INNER JOIN Jobtitle ON Adverts.JobTitleID = Jobtitle.JobTitleID)
    > INNER JOIN Location ON Employer.LocationID = Location.LocationID)
    > INNER JOIN Speciality ON Adverts.SpecialityID = Speciality.SpecialityID
    > WHERE
    > (@JobTitleID IS NULL OR Adverts.JobTitleID = @JobTitleID)
    > AND (@JobTitleID IS NULL OR Adverts.JobTitleID = @JobTitleID)
    > AND (@SpecialityID IS NULL OR Adverts.SpecialityID = @SpecialityID)
    > AND (@LocationID IS NULL OR Employer.LocationID = @LocationID)
    > AND (@KeyWords IS NULL OR Adverts.InfoText LIKE '%' + @KeyWords + '%')
    > AND (((Adverts.StartDate)<=GetDate())
    > AND (Adverts.EndDate)>=GetDate())
    > ORDER BY Location.Location
    >
    > I have verified this works as expected in the Query Designer.
    >
    > On the page, I have placed a DataSource Control and a GridView. The code
    > for both is as follows:
    >
    > <asp:GridView ID="gvSearchResults" runat="server" AllowSorting="True"
    > AutoGenerateColumns="False"
    > DataKeyNames="AdvertID" DataSourceID="dsSearchResults">
    > <Columns>
    > <asp:CommandField ShowSelectButton="True" />
    > <asp:BoundField DataField="AdvertID" HeaderText="AdvertID"
    > InsertVisible="False"
    > ReadOnly="True" SortExpression="AdvertID" />
    > <asp:BoundField DataField="Location" HeaderText="Location"
    > SortExpression="Location" />
    > <asp:BoundField DataField="JobTitle" HeaderText="JobTitle"
    > SortExpression="JobTitle" />
    > <asp:BoundField DataField="Speciality" HeaderText="Speciality"
    > SortExpression="Speciality" />
    > </Columns>
    > </asp:GridView>
    >
    > <asp:SqlDataSource ID="dsSearchResults" runat="server"
    > ConnectionString="<%$ ConnectionStrings:xxxx %>"
    > SelectCommand="qGetSearchResults" SelectCommandType="StoredProcedure">
    > <SelectParameters>
    > <asp:QueryStringParameter Name="JobTitleID" QueryStringField="JobTitleID"
    > Type="Int32" />
    > <asp:QueryStringParameter Name="SpecialityID"
    > QueryStringField="SpecialityID" Type="Int32" />
    > <asp:QueryStringParameter Name="LocationID" QueryStringField="LocationID"
    > Type="Int32" />
    > <asp:QueryStringParameter Name="KeyWords" QueryStringField="KeyWords"
    > Type="String" />
    > </SelectParameters>
    > </asp:SqlDataSource>
    >
    > If I navigate to the page using a URL and querystring like this:
    >
    > searchResults.aspx?LocationID=&SpecialityID=&KeyWords=&JobTitleID=35
    >
    > the GridView refuses to appear, despite the fact that executing the Stored
    > Proc in SQL Server gives me 10 results.
    >
    > What have I missed?
    >
    >
    > Mike
    >
     
    Mr Struggler, Oct 8, 2006
    #2
    1. Advertising

  3. Mike

    Mike Guest

    I thought this happened automatically if you plonked a sqlDataSource and a
    data control on a page. It does on other pages where I have done so.

    Nevertheless, I think there must be something wrong with the stored proc.
    So I am doing a switch case for the 15 possible variations of querystring
    values and creating 15 procs, then binding the gridview programmatically in
    the Page_Load event to the correct proc depending on which values were
    passed. That seems to work. I just hope no one asks me to add a fifth
    querystring variable....


    "Mr Struggler" <lkjhlkj> wrote in message
    news:O3j$...
    > You need to do a DataBind for the control sometime before the page
    > renders.
    >
    >
    > myDataGrid.DataBind
    >
    >
    >
    >
    > "Mike" <> wrote in message
    > news:...
    >> I'm having trouble getting a gridview to bind. I probably missing
    >> something completely obvious and would appreciate any help on offer.
    >>
    >> I'm passing parameters via querystring, and have created a stored proc as
    >> follows:
    >>
    >> qGetSearchResults:
    >>
    >> SELECT
    >> Adverts.AdvertID,
    >> Location.Location,
    >> Jobtitle.JobTitle,
    >> Speciality.Speciality FROM (((Adverts
    >> INNER JOIN Employer ON Adverts.EmployerID = Employer.EmployerID)
    >> INNER JOIN Jobtitle ON Adverts.JobTitleID = Jobtitle.JobTitleID)
    >> INNER JOIN Location ON Employer.LocationID = Location.LocationID)
    >> INNER JOIN Speciality ON Adverts.SpecialityID = Speciality.SpecialityID
    >> WHERE
    >> (@JobTitleID IS NULL OR Adverts.JobTitleID = @JobTitleID)
    >> AND (@JobTitleID IS NULL OR Adverts.JobTitleID = @JobTitleID)
    >> AND (@SpecialityID IS NULL OR Adverts.SpecialityID = @SpecialityID)
    >> AND (@LocationID IS NULL OR Employer.LocationID = @LocationID)
    >> AND (@KeyWords IS NULL OR Adverts.InfoText LIKE '%' + @KeyWords + '%')
    >> AND (((Adverts.StartDate)<=GetDate())
    >> AND (Adverts.EndDate)>=GetDate())
    >> ORDER BY Location.Location
    >>
    >> I have verified this works as expected in the Query Designer.
    >>
    >> On the page, I have placed a DataSource Control and a GridView. The code
    >> for both is as follows:
    >>
    >> <asp:GridView ID="gvSearchResults" runat="server" AllowSorting="True"
    >> AutoGenerateColumns="False"
    >> DataKeyNames="AdvertID" DataSourceID="dsSearchResults">
    >> <Columns>
    >> <asp:CommandField ShowSelectButton="True" />
    >> <asp:BoundField DataField="AdvertID" HeaderText="AdvertID"
    >> InsertVisible="False"
    >> ReadOnly="True" SortExpression="AdvertID" />
    >> <asp:BoundField DataField="Location" HeaderText="Location"
    >> SortExpression="Location" />
    >> <asp:BoundField DataField="JobTitle" HeaderText="JobTitle"
    >> SortExpression="JobTitle" />
    >> <asp:BoundField DataField="Speciality" HeaderText="Speciality"
    >> SortExpression="Speciality" />
    >> </Columns>
    >> </asp:GridView>
    >>
    >> <asp:SqlDataSource ID="dsSearchResults" runat="server"
    >> ConnectionString="<%$ ConnectionStrings:xxxx %>"
    >> SelectCommand="qGetSearchResults" SelectCommandType="StoredProcedure">
    >> <SelectParameters>
    >> <asp:QueryStringParameter Name="JobTitleID" QueryStringField="JobTitleID"
    >> Type="Int32" />
    >> <asp:QueryStringParameter Name="SpecialityID"
    >> QueryStringField="SpecialityID" Type="Int32" />
    >> <asp:QueryStringParameter Name="LocationID" QueryStringField="LocationID"
    >> Type="Int32" />
    >> <asp:QueryStringParameter Name="KeyWords" QueryStringField="KeyWords"
    >> Type="String" />
    >> </SelectParameters>
    >> </asp:SqlDataSource>
    >>
    >> If I navigate to the page using a URL and querystring like this:
    >>
    >> searchResults.aspx?LocationID=&SpecialityID=&KeyWords=&JobTitleID=35
    >>
    >> the GridView refuses to appear, despite the fact that executing the
    >> Stored Proc in SQL Server gives me 10 results.
    >>
    >> What have I missed?
    >>
    >>
    >> Mike
    >>

    >
    >
     
    Mike, Oct 8, 2006
    #3
  4. Mike

    Mr Struggler Guest

    OK your using 2005, I'm not familar with that yet


    "Mike" <> wrote in message
    news:...
    >I thought this happened automatically if you plonked a sqlDataSource and a
    >data control on a page. It does on other pages where I have done so.
    >
    > Nevertheless, I think there must be something wrong with the stored proc.
    > So I am doing a switch case for the 15 possible variations of querystring
    > values and creating 15 procs, then binding the gridview programmatically
    > in the Page_Load event to the correct proc depending on which values were
    > passed. That seems to work. I just hope no one asks me to add a fifth
    > querystring variable....
    >
    >
    > "Mr Struggler" <lkjhlkj> wrote in message
    > news:O3j$...
    >> You need to do a DataBind for the control sometime before the page
    >> renders.
    >>
    >>
    >> myDataGrid.DataBind
    >>
    >>
    >>
    >>
    >> "Mike" <> wrote in message
    >> news:...
    >>> I'm having trouble getting a gridview to bind. I probably missing
    >>> something completely obvious and would appreciate any help on offer.
    >>>
    >>> I'm passing parameters via querystring, and have created a stored proc
    >>> as follows:
    >>>
    >>> qGetSearchResults:
    >>>
    >>> SELECT
    >>> Adverts.AdvertID,
    >>> Location.Location,
    >>> Jobtitle.JobTitle,
    >>> Speciality.Speciality FROM (((Adverts
    >>> INNER JOIN Employer ON Adverts.EmployerID = Employer.EmployerID)
    >>> INNER JOIN Jobtitle ON Adverts.JobTitleID = Jobtitle.JobTitleID)
    >>> INNER JOIN Location ON Employer.LocationID = Location.LocationID)
    >>> INNER JOIN Speciality ON Adverts.SpecialityID = Speciality.SpecialityID
    >>> WHERE
    >>> (@JobTitleID IS NULL OR Adverts.JobTitleID = @JobTitleID)
    >>> AND (@JobTitleID IS NULL OR Adverts.JobTitleID = @JobTitleID)
    >>> AND (@SpecialityID IS NULL OR Adverts.SpecialityID = @SpecialityID)
    >>> AND (@LocationID IS NULL OR Employer.LocationID = @LocationID)
    >>> AND (@KeyWords IS NULL OR Adverts.InfoText LIKE '%' + @KeyWords + '%')
    >>> AND (((Adverts.StartDate)<=GetDate())
    >>> AND (Adverts.EndDate)>=GetDate())
    >>> ORDER BY Location.Location
    >>>
    >>> I have verified this works as expected in the Query Designer.
    >>>
    >>> On the page, I have placed a DataSource Control and a GridView. The
    >>> code for both is as follows:
    >>>
    >>> <asp:GridView ID="gvSearchResults" runat="server" AllowSorting="True"
    >>> AutoGenerateColumns="False"
    >>> DataKeyNames="AdvertID" DataSourceID="dsSearchResults">
    >>> <Columns>
    >>> <asp:CommandField ShowSelectButton="True" />
    >>> <asp:BoundField DataField="AdvertID" HeaderText="AdvertID"
    >>> InsertVisible="False"
    >>> ReadOnly="True" SortExpression="AdvertID" />
    >>> <asp:BoundField DataField="Location" HeaderText="Location"
    >>> SortExpression="Location" />
    >>> <asp:BoundField DataField="JobTitle" HeaderText="JobTitle"
    >>> SortExpression="JobTitle" />
    >>> <asp:BoundField DataField="Speciality" HeaderText="Speciality"
    >>> SortExpression="Speciality" />
    >>> </Columns>
    >>> </asp:GridView>
    >>>
    >>> <asp:SqlDataSource ID="dsSearchResults" runat="server"
    >>> ConnectionString="<%$ ConnectionStrings:xxxx %>"
    >>> SelectCommand="qGetSearchResults" SelectCommandType="StoredProcedure">
    >>> <SelectParameters>
    >>> <asp:QueryStringParameter Name="JobTitleID"
    >>> QueryStringField="JobTitleID" Type="Int32" />
    >>> <asp:QueryStringParameter Name="SpecialityID"
    >>> QueryStringField="SpecialityID" Type="Int32" />
    >>> <asp:QueryStringParameter Name="LocationID"
    >>> QueryStringField="LocationID" Type="Int32" />
    >>> <asp:QueryStringParameter Name="KeyWords" QueryStringField="KeyWords"
    >>> Type="String" />
    >>> </SelectParameters>
    >>> </asp:SqlDataSource>
    >>>
    >>> If I navigate to the page using a URL and querystring like this:
    >>>
    >>> searchResults.aspx?LocationID=&SpecialityID=&KeyWords=&JobTitleID=35
    >>>
    >>> the GridView refuses to appear, despite the fact that executing the
    >>> Stored Proc in SQL Server gives me 10 results.
    >>>
    >>> What have I missed?
    >>>
    >>>
    >>> Mike
    >>>

    >>
    >>

    >
    >
     
    Mr Struggler, Oct 8, 2006
    #4
  5. Mike

    Mike Guest

    I did mention GridView in my OP as opposed to DataGrid, but nevermind :)
    I've found the answer after many frustrating hours.

    My requirement is a common one(I believe). I want to give the user the
    option to search on any combination of 4 criteria. This means that one or
    more of the criteria could well be NULL. Consequently, the parameter value
    will be NULL, and I set that as a default value in the stored proc for each
    parameter.

    The default for the sqlDataSource is that it doesn't bother to do anything
    if you leave any parameter as NULL, so you have to go into its properties
    and change the CancelSelectOnNullParameter value to false. It's true by
    default. When I say it doesn't do anything, I mean nothing at all. It
    doesn't even throw an exception, which I would expect it to do if it was not
    prepared to handle NULL values.

    The configuration wizard allows you to enter default values for the
    parameters as wel as their source. It doesn't allow you to supply NULL as a
    default. If you type NULL in, it treats it as a string and complains.

    I've got 3 books on ASP.NET 2.0 and this is not mentioned in any one of
    them, nor could I find it without deep burrowing through google, despite the
    fact that I must have read *almost* every article on dynamic search
    conditions and the sqlDataSource on the Internet.

    I've rewritten the page and the stored proc so many times I'm bored looking
    at it, and all the time my original code works perfectly well with that one
    amendment to one property.

    Now that I know what to google for, I find I'm not the only person to be
    driven round the bend on this one:

    http://blogs.wdevs.com/colinangusmackay/archive/2004/09/08/197.aspx
    http://www.danielroot.com/CodeSpeak/tabid/75/Default.aspx
    http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/06/06/792.aspx
    http://www.dotnetmonster.com/Uwe/Forum.aspx/asp-net/56770/SqlDataSource-not-binding
    http://www.mooredynasty.com/Blog/BlogEntries.aspx?BlogID=BLOG0001&Period=2006_01

    etc, etc.

    I nearly threw my hands up and went back to classic ASP, but now I've
    invested so much time in this one flaming page, I've got to stick with
    learning Dotnet.

    Phew. Rant over :)

    Mike

    "Mr Struggler" <lkjhlkj> wrote in message
    news:...
    > OK your using 2005, I'm not familar with that yet
    >
    >
    > "Mike" <> wrote in message
    > news:...
    >>I thought this happened automatically if you plonked a sqlDataSource and a
    >>data control on a page. It does on other pages where I have done so.
    >>
    >> Nevertheless, I think there must be something wrong with the stored proc.
    >> So I am doing a switch case for the 15 possible variations of querystring
    >> values and creating 15 procs, then binding the gridview programmatically
    >> in the Page_Load event to the correct proc depending on which values were
    >> passed. That seems to work. I just hope no one asks me to add a fifth
    >> querystring variable....
    >>
    >>
    >> "Mr Struggler" <lkjhlkj> wrote in message
    >> news:O3j$...
    >>> You need to do a DataBind for the control sometime before the page
    >>> renders.
    >>>
    >>>
    >>> myDataGrid.DataBind
    >>>
    >>>
    >>>
    >>>
    >>> "Mike" <> wrote in message
    >>> news:...
    >>>> I'm having trouble getting a gridview to bind. I probably missing
    >>>> something completely obvious and would appreciate any help on offer.
    >>>>
    >>>> I'm passing parameters via querystring, and have created a stored proc
    >>>> as follows:
    >>>>
    >>>> qGetSearchResults:
    >>>>
    >>>> SELECT
    >>>> Adverts.AdvertID,
    >>>> Location.Location,
    >>>> Jobtitle.JobTitle,
    >>>> Speciality.Speciality FROM (((Adverts
    >>>> INNER JOIN Employer ON Adverts.EmployerID = Employer.EmployerID)
    >>>> INNER JOIN Jobtitle ON Adverts.JobTitleID = Jobtitle.JobTitleID)
    >>>> INNER JOIN Location ON Employer.LocationID = Location.LocationID)
    >>>> INNER JOIN Speciality ON Adverts.SpecialityID = Speciality.SpecialityID
    >>>> WHERE
    >>>> (@JobTitleID IS NULL OR Adverts.JobTitleID = @JobTitleID)
    >>>> AND (@JobTitleID IS NULL OR Adverts.JobTitleID = @JobTitleID)
    >>>> AND (@SpecialityID IS NULL OR Adverts.SpecialityID = @SpecialityID)
    >>>> AND (@LocationID IS NULL OR Employer.LocationID = @LocationID)
    >>>> AND (@KeyWords IS NULL OR Adverts.InfoText LIKE '%' + @KeyWords + '%')
    >>>> AND (((Adverts.StartDate)<=GetDate())
    >>>> AND (Adverts.EndDate)>=GetDate())
    >>>> ORDER BY Location.Location
    >>>>
    >>>> I have verified this works as expected in the Query Designer.
    >>>>
    >>>> On the page, I have placed a DataSource Control and a GridView. The
    >>>> code for both is as follows:
    >>>>
    >>>> <asp:GridView ID="gvSearchResults" runat="server" AllowSorting="True"
    >>>> AutoGenerateColumns="False"
    >>>> DataKeyNames="AdvertID" DataSourceID="dsSearchResults">
    >>>> <Columns>
    >>>> <asp:CommandField ShowSelectButton="True" />
    >>>> <asp:BoundField DataField="AdvertID" HeaderText="AdvertID"
    >>>> InsertVisible="False"
    >>>> ReadOnly="True" SortExpression="AdvertID" />
    >>>> <asp:BoundField DataField="Location" HeaderText="Location"
    >>>> SortExpression="Location" />
    >>>> <asp:BoundField DataField="JobTitle" HeaderText="JobTitle"
    >>>> SortExpression="JobTitle" />
    >>>> <asp:BoundField DataField="Speciality" HeaderText="Speciality"
    >>>> SortExpression="Speciality" />
    >>>> </Columns>
    >>>> </asp:GridView>
    >>>>
    >>>> <asp:SqlDataSource ID="dsSearchResults" runat="server"
    >>>> ConnectionString="<%$ ConnectionStrings:xxxx %>"
    >>>> SelectCommand="qGetSearchResults" SelectCommandType="StoredProcedure">
    >>>> <SelectParameters>
    >>>> <asp:QueryStringParameter Name="JobTitleID"
    >>>> QueryStringField="JobTitleID" Type="Int32" />
    >>>> <asp:QueryStringParameter Name="SpecialityID"
    >>>> QueryStringField="SpecialityID" Type="Int32" />
    >>>> <asp:QueryStringParameter Name="LocationID"
    >>>> QueryStringField="LocationID" Type="Int32" />
    >>>> <asp:QueryStringParameter Name="KeyWords" QueryStringField="KeyWords"
    >>>> Type="String" />
    >>>> </SelectParameters>
    >>>> </asp:SqlDataSource>
    >>>>
    >>>> If I navigate to the page using a URL and querystring like this:
    >>>>
    >>>> searchResults.aspx?LocationID=&SpecialityID=&KeyWords=&JobTitleID=35
    >>>>
    >>>> the GridView refuses to appear, despite the fact that executing the
    >>>> Stored Proc in SQL Server gives me 10 results.
    >>>>
    >>>> What have I missed?
    >>>>
    >>>>
    >>>> Mike
    >>>>
    >>>
    >>>

    >>
    >>

    >
    >
     
    Mike, Oct 8, 2006
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jordan
    Replies:
    2
    Views:
    2,536
    Jordan
    Feb 10, 2004
  2. Amit
    Replies:
    6
    Views:
    13,806
    Assimalyst
    Oct 24, 2006
  3. Replies:
    2
    Views:
    863
    Kevin Grover
    Oct 20, 2006
  4. JcFx
    Replies:
    0
    Views:
    280
  5. Vivek Nallur

    value binding and function binding

    Vivek Nallur, Sep 25, 2003, in forum: Ruby
    Replies:
    0
    Views:
    137
    Vivek Nallur
    Sep 25, 2003
Loading...

Share This Page