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:

qGetSearchResul ts:

SELECT
Adverts.AdvertI D,
Location.Locati on,
Jobtitle.JobTit le,
Speciality.Spec iality FROM (((Adverts
INNER JOIN Employer ON Adverts.Employe rID = Employer.Employ erID)
INNER JOIN Jobtitle ON Adverts.JobTitl eID = Jobtitle.JobTit leID)
INNER JOIN Location ON Employer.Locati onID = Location.Locati onID)
INNER JOIN Speciality ON Adverts.Special ityID = Speciality.Spec ialityID
WHERE
(@JobTitleID IS NULL OR Adverts.JobTitl eID = @JobTitleID)
AND (@JobTitleID IS NULL OR Adverts.JobTitl eID = @JobTitleID)
AND (@SpecialityID IS NULL OR Adverts.Special ityID = @SpecialityID)
AND (@LocationID IS NULL OR Employer.Locati onID = @LocationID)
AND (@KeyWords IS NULL OR Adverts.InfoTex t LIKE '%' + @KeyWords + '%')
AND (((Adverts.Star tDate)<=GetDate ())
AND (Adverts.EndDat e)>=GetDate())
ORDER BY Location.Locati on

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="gvSearchRes ults" runat="server" AllowSorting="T rue"
AutoGenerateCol umns="False"
DataKeyNames="A dvertID" DataSourceID="d sSearchResults" >
<Columns>
<asp:CommandFie ld ShowSelectButto n="True" />
<asp:BoundFie ld DataField="Adve rtID" HeaderText="Adv ertID"
InsertVisible=" False"
ReadOnly="True" SortExpression= "AdvertID" />
<asp:BoundFie ld DataField="Loca tion" HeaderText="Loc ation"
SortExpression= "Location" />
<asp:BoundFie ld DataField="JobT itle" HeaderText="Job Title"
SortExpression= "JobTitle" />
<asp:BoundFie ld DataField="Spec iality" HeaderText="Spe ciality"
SortExpression= "Speciality " />
</Columns>
</asp:GridView>

<asp:SqlDataSou rce ID="dsSearchRes ults" runat="server"
ConnectionStrin g="<%$ ConnectionStrin gs:xxxx %>"
SelectCommand=" qGetSearchResul ts" SelectCommandTy pe="StoredProce dure">
<SelectParamete rs>
<asp:QueryStrin gParameter Name="JobTitleI D" QueryStringFiel d="JobTitleID "
Type="Int32" />
<asp:QueryStrin gParameter Name="Specialit yID"
QueryStringFiel d="SpecialityID " Type="Int32" />
<asp:QueryStrin gParameter Name="LocationI D" QueryStringFiel d="LocationID "
Type="Int32" />
<asp:QueryStrin gParameter Name="KeyWords" QueryStringFiel d="KeyWords"
Type="String" />
</SelectParameter s>
</asp:SqlDataSour ce>

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

searchResults.a spx?LocationID= &SpecialityID=& KeyWords=&JobTi tleID=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.Data Bind


"Mike" <ne****@dotnet. comwrote in message
news:uS******** ******@TK2MSFTN GP04.phx.gbl...

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:

qGetSearchResul ts:

SELECT
Adverts.AdvertI D,
Location.Locati on,
Jobtitle.JobTit le,
Speciality.Spec iality FROM (((Adverts
INNER JOIN Employer ON Adverts.Employe rID = Employer.Employ erID)
INNER JOIN Jobtitle ON Adverts.JobTitl eID = Jobtitle.JobTit leID)
INNER JOIN Location ON Employer.Locati onID = Location.Locati onID)
INNER JOIN Speciality ON Adverts.Special ityID = Speciality.Spec ialityID
WHERE
(@JobTitleID IS NULL OR Adverts.JobTitl eID = @JobTitleID)
AND (@JobTitleID IS NULL OR Adverts.JobTitl eID = @JobTitleID)
AND (@SpecialityID IS NULL OR Adverts.Special ityID = @SpecialityID)
AND (@LocationID IS NULL OR Employer.Locati onID = @LocationID)
AND (@KeyWords IS NULL OR Adverts.InfoTex t LIKE '%' + @KeyWords + '%')
AND (((Adverts.Star tDate)<=GetDate ())
AND (Adverts.EndDat e)>=GetDate())
ORDER BY Location.Locati on

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="gvSearchRes ults" runat="server" AllowSorting="T rue"
AutoGenerateCol umns="False"
DataKeyNames="A dvertID" DataSourceID="d sSearchResults" >
<Columns>
<asp:CommandFie ld ShowSelectButto n="True" />
<asp:BoundFie ld DataField="Adve rtID" HeaderText="Adv ertID"
InsertVisible=" False"
ReadOnly="True" SortExpression= "AdvertID" />
<asp:BoundFie ld DataField="Loca tion" HeaderText="Loc ation"
SortExpression= "Location" />
<asp:BoundFie ld DataField="JobT itle" HeaderText="Job Title"
SortExpression= "JobTitle" />
<asp:BoundFie ld DataField="Spec iality" HeaderText="Spe ciality"
SortExpression= "Speciality " />
</Columns>
</asp:GridView>

<asp:SqlDataSou rce ID="dsSearchRes ults" runat="server"
ConnectionStrin g="<%$ ConnectionStrin gs:xxxx %>"
SelectCommand=" qGetSearchResul ts" SelectCommandTy pe="StoredProce dure">
<SelectParamete rs>
<asp:QueryStrin gParameter Name="JobTitleI D" QueryStringFiel d="JobTitleID "
Type="Int32" />
<asp:QueryStrin gParameter Name="Specialit yID"
QueryStringFiel d="SpecialityID " Type="Int32" />
<asp:QueryStrin gParameter Name="LocationI D" QueryStringFiel d="LocationID "
Type="Int32" />
<asp:QueryStrin gParameter Name="KeyWords" QueryStringFiel d="KeyWords"
Type="String" />
</SelectParameter s>
</asp:SqlDataSour ce>

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

searchResults.a spx?LocationID= &SpecialityID=& KeyWords=&JobTi tleID=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]

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 programmaticall y 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" <lkjhlkjwrote in message
news:O3******** ******@TK2MSFTN GP03.phx.gbl...

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


"Mike" <ne****@dotnet. comwrote in message
news:uS******** ******@TK2MSFTN GP04.phx.gbl...

>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:

qGetSearchResu lts:

SELECT
Adverts.Advert ID,
Location.Locat ion,
Jobtitle.JobTi tle,
Speciality.Spe ciality FROM (((Adverts
INNER JOIN Employer ON Adverts.Employe rID = Employer.Employ erID)
INNER JOIN Jobtitle ON Adverts.JobTitl eID = Jobtitle.JobTit leID)
INNER JOIN Location ON Employer.Locati onID = Location.Locati onID)
INNER JOIN Speciality ON Adverts.Special ityID = Speciality.Spec ialityID
WHERE
(@JobTitleID IS NULL OR Adverts.JobTitl eID = @JobTitleID)
AND (@JobTitleID IS NULL OR Adverts.JobTitl eID = @JobTitleID)
AND (@SpecialityID IS NULL OR Adverts.Special ityID = @SpecialityID)
AND (@LocationID IS NULL OR Employer.Locati onID = @LocationID)
AND (@KeyWords IS NULL OR Adverts.InfoTex t LIKE '%' + @KeyWords + '%')
AND (((Adverts.Star tDate)<=GetDate ())
AND (Adverts.EndDat e)>=GetDate())
ORDER BY Location.Locati on

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:GridVie w ID="gvSearchRes ults" runat="server" AllowSorting="T rue"
AutoGenerateCo lumns="False"
DataKeyNames=" AdvertID" DataSourceID="d sSearchResults" >
<Columns>
<asp:CommandFi eld ShowSelectButto n="True" />
<asp:BoundFiel d DataField="Adve rtID" HeaderText="Adv ertID"
InsertVisible= "False"
ReadOnly="True " SortExpression= "AdvertID" />
<asp:BoundFiel d DataField="Loca tion" HeaderText="Loc ation"
SortExpression ="Location" />
<asp:BoundFiel d DataField="JobT itle" HeaderText="Job Title"
SortExpression ="JobTitle" />
<asp:BoundFiel d DataField="Spec iality" HeaderText="Spe ciality"
SortExpression ="Speciality " />
</Columns>
</asp:GridView>

<asp:SqlDataSo urce ID="dsSearchRes ults" runat="server"
ConnectionStri ng="<%$ ConnectionStrin gs:xxxx %>"
SelectCommand= "qGetSearchResu lts" SelectCommandTy pe="StoredProce dure">
<SelectParamet ers>
<asp:QueryStri ngParameter Name="JobTitleI D" QueryStringFiel d="JobTitleID "
Type="Int32" />
<asp:QueryStri ngParameter Name="Specialit yID"
QueryStringFie ld="SpecialityI D" Type="Int32" />
<asp:QueryStri ngParameter Name="LocationI D" QueryStringFiel d="LocationID "
Type="Int32" />
<asp:QueryStri ngParameter Name="KeyWords" QueryStringFiel d="KeyWords"
Type="String " />
</SelectParameter s>
</asp:SqlDataSour ce>

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

searchResults. aspx?LocationID =&SpecialityID= &KeyWords=&JobT itleID=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]

OK your using 2005, I'm not familar with that yet
"Mike" <ne****@dotnet. comwrote in message
news:eF******** ******@TK2MSFTN GP04.phx.gbl...

>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 programmaticall y
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" <lkjhlkjwrote in message
news:O3******** ******@TK2MSFTN GP03.phx.gbl...

>You need to do a DataBind for the control sometime before the page
renders.
myDataGrid.Dat aBind


"Mike" <ne****@dotnet. comwrote in message
news:uS******* *******@TK2MSFT NGP04.phx.gbl.. .

>>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:

qGetSearchRes ults:

SELECT
Adverts.Adver tID,
Location.Loca tion,
Jobtitle.JobT itle,
Speciality.Sp eciality FROM (((Adverts
INNER JOIN Employer ON Adverts.Employe rID = Employer.Employ erID)
INNER JOIN Jobtitle ON Adverts.JobTitl eID = Jobtitle.JobTit leID)
INNER JOIN Location ON Employer.Locati onID = Location.Locati onID)
INNER JOIN Speciality ON Adverts.Special ityID = Speciality.Spec ialityID
WHERE
(@JobTitleI D IS NULL OR Adverts.JobTitl eID = @JobTitleID)
AND (@JobTitleID IS NULL OR Adverts.JobTitl eID = @JobTitleID)
AND (@SpecialityID IS NULL OR Adverts.Special ityID = @SpecialityID)
AND (@LocationID IS NULL OR Employer.Locati onID = @LocationID)
AND (@KeyWords IS NULL OR Adverts.InfoTex t LIKE '%' + @KeyWords + '%')
AND (((Adverts.Star tDate)<=GetDate ())
AND (Adverts.EndDat e)>=GetDate())
ORDER BY Location.Locati on

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:GridVi ew ID="gvSearchRes ults" runat="server" AllowSorting="T rue"
AutoGenerateC olumns="False"
DataKeyNames= "AdvertID" DataSourceID="d sSearchResults" >
<Columns>
<asp:CommandF ield ShowSelectButto n="True" />
<asp:BoundFie ld DataField="Adve rtID" HeaderText="Adv ertID"
InsertVisible ="False"
ReadOnly="Tru e" SortExpression= "AdvertID" />
<asp:BoundFie ld DataField="Loca tion" HeaderText="Loc ation"
SortExpressio n="Location" />
<asp:BoundFie ld DataField="JobT itle" HeaderText="Job Title"
SortExpressio n="JobTitle" />
<asp:BoundFie ld DataField="Spec iality" HeaderText="Spe ciality"
SortExpressio n="Speciality " />
</Columns>
</asp:GridView>

<asp:SqlDataS ource ID="dsSearchRes ults" runat="server"
ConnectionStr ing="<%$ ConnectionStrin gs:xxxx %>"
SelectCommand ="qGetSearchRes ults" SelectCommandTy pe="StoredProce dure">
<SelectParame ters>
<asp:QueryStr ingParameter Name="JobTitleI D"
QueryStringFi eld="JobTitleID " Type="Int32" />
<asp:QueryStr ingParameter Name="Specialit yID"
QueryStringFi eld="Speciality ID" Type="Int32" />
<asp:QueryStr ingParameter Name="LocationI D"
QueryStringFi eld="LocationID " Type="Int32" />
<asp:QueryStr ingParameter Name="KeyWords" QueryStringFiel d="KeyWords"
Type="Strin g" />
</SelectParameter s>
</asp:SqlDataSour ce>

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

searchResults .aspx?LocationI D=&SpecialityID =&KeyWords=&Job TitleID=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]

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 CancelSelectOnN ullParameter 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/colinangusmac...09/08/197.aspx
http://www.danielroot.com/CodeSpeak/...5/Default.aspx
http://sqlblogcasts.com/blogs/tonyro...06/06/792.aspx
http://www.dotnetmonster.com/Uwe/For...ce-not-binding
http://www.mooredynasty.com/Blog/Blo...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" <lkjhlkjwrote in message
news:uQ******** ******@TK2MSFTN GP05.phx.gbl...

OK your using 2005, I'm not familar with that yet
"Mike" <ne****@dotnet. comwrote in message
news:eF******** ******@TK2MSFTN GP04.phx.gbl...

>>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 programmaticall y
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" <lkjhlkjwrote in message
news:O3******* *******@TK2MSFT NGP03.phx.gbl.. .

>>You need to do a DataBind for the control sometime before the page
renders.
myDataGrid.Da taBind


"Mike" <ne****@dotnet. comwrote in message
news:uS****** ********@TK2MSF TNGP04.phx.gbl. ..
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:

qGetSearchRe sults:

SELECT
Adverts.Adve rtID,
Location.Loc ation,
Jobtitle.Job Title,
Speciality.S peciality FROM (((Adverts
INNER JOIN Employer ON Adverts.Employe rID = Employer.Employ erID)
INNER JOIN Jobtitle ON Adverts.JobTitl eID = Jobtitle.JobTit leID)
INNER JOIN Location ON Employer.Locati onID = Location.Locati onID)
INNER JOIN Speciality ON Adverts.Special ityID = Speciality.Spec ialityID
WHERE
(@JobTitle ID IS NULL OR Adverts.JobTitl eID = @JobTitleID)
AND (@JobTitleID IS NULL OR Adverts.JobTitl eID = @JobTitleID)
AND (@SpecialityID IS NULL OR Adverts.Special ityID = @SpecialityID)
AND (@LocationID IS NULL OR Employer.Locati onID = @LocationID)
AND (@KeyWords IS NULL OR Adverts.InfoTex t LIKE '%' + @KeyWords + '%')
AND (((Adverts.Star tDate)<=GetDate ())
AND (Adverts.EndDat e)>=GetDate())
ORDER BY Location.Locati on

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:GridVie w ID="gvSearchRes ults" runat="server" AllowSorting="T rue"
AutoGenerate Columns="False"
DataKeyNames ="AdvertID" DataSourceID="d sSearchResults" >
<Columns>
<asp:Command Field ShowSelectButto n="True" />
<asp:BoundFi eld DataField="Adve rtID" HeaderText="Adv ertID"
InsertVisibl e="False"
ReadOnly="Tr ue" SortExpression= "AdvertID" />
<asp:BoundFi eld DataField="Loca tion" HeaderText="Loc ation"
SortExpressi on="Location" />
<asp:BoundFi eld DataField="JobT itle" HeaderText="Job Title"
SortExpressi on="JobTitle" />
<asp:BoundFi eld DataField="Spec iality" HeaderText="Spe ciality"
SortExpressi on="Speciality " />
</Columns>
</asp:GridView>

<asp:SqlData Source ID="dsSearchRes ults" runat="server"
ConnectionSt ring="<%$ ConnectionStrin gs:xxxx %>"
SelectComman d="qGetSearchRe sults" SelectCommandTy pe="StoredProce dure">
<SelectParam eters>
<asp:QuerySt ringParameter Name="JobTitleI D"
QueryStringF ield="JobTitleI D" Type="Int32" />
<asp:QuerySt ringParameter Name="Specialit yID"
QueryStringF ield="Specialit yID" Type="Int32" />
<asp:QuerySt ringParameter Name="LocationI D"
QueryStringF ield="LocationI D" Type="Int32" />
<asp:QuerySt ringParameter Name="KeyWords" QueryStringFiel d="KeyWords"
Type="String " />
</SelectParameter s>
</asp:SqlDataSour ce>

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

searchResult s.aspx?Location ID=&SpecialityI D=&KeyWords=&Jo bTitleID=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