Binding to Gridview

[Mike]

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]

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


myDataGrid.DataBind

 

[Mike]

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]

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

 

[Mike]

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