A
Assimalyst
Hi,
I am trying to create a search page using a number of fields, mostly
strings, one is an integer. That allows the user to use any combination
of the fields to search with.
I have a datasource configured using the asp.net (v2.0) tools and the
parameters are passed from controls to a stored procedure. I have set
default values for the strings to '%' and left the default for the
integer blank. When i test the query in configuration it works fine
with a blank integer. But when i run it in the webpage the datasource
returns null if the integer field is blank. I have checked this with
the following code:
protected void gvSearchResults_DataBound(object sender, EventArgs e)
{
DataView DV =
(DataView)this.DataSourceSearchResultsGV.Select(DataSourceSelectArguments.Empty);
if (DV != null)
{
. . . .
If i remove the integer field from the query it all works fine, if i
enter an integer it works fine. So it seems to be some sort of issue
converting the blank textbox control text (that the integer is passed
from) for use in the datasource.
Any ideas how to fix this?
Here's the query:
@pkiBarcodeNumber varchar(8),
@szFirstName varchar(24),
@szSurname varchar(40),
@szDateOfBirth varchar(8),
@szPostcode varchar(8)
AS
SELECT pkiBarcodeNumber, szFirstName, szSurname, szDateOfBirth,
szPostcode
FROM tblApplicationForms
WHERE pkiBarcodeNumber = CASE WHEN @pkiBarcodeNumber IS NULL THEN
pkiBarcodeNumber ELSE @pkiBarcodeNumber END
AND szFirstName LIKE '%' + @szFirstName + '%'
AND szSurname LIKE '%' + @szSurname + '%'
AND szDateOfBirth LIKE '%' + @szDateOfBirth + '%'
AND szPostcode LIKE '%' + @szPostcode + '%'
ORDER BY pkiBarcodeNumber
Thanks
I am trying to create a search page using a number of fields, mostly
strings, one is an integer. That allows the user to use any combination
of the fields to search with.
I have a datasource configured using the asp.net (v2.0) tools and the
parameters are passed from controls to a stored procedure. I have set
default values for the strings to '%' and left the default for the
integer blank. When i test the query in configuration it works fine
with a blank integer. But when i run it in the webpage the datasource
returns null if the integer field is blank. I have checked this with
the following code:
protected void gvSearchResults_DataBound(object sender, EventArgs e)
{
DataView DV =
(DataView)this.DataSourceSearchResultsGV.Select(DataSourceSelectArguments.Empty);
if (DV != null)
{
. . . .
If i remove the integer field from the query it all works fine, if i
enter an integer it works fine. So it seems to be some sort of issue
converting the blank textbox control text (that the integer is passed
from) for use in the datasource.
Any ideas how to fix this?
Here's the query:
@pkiBarcodeNumber varchar(8),
@szFirstName varchar(24),
@szSurname varchar(40),
@szDateOfBirth varchar(8),
@szPostcode varchar(8)
AS
SELECT pkiBarcodeNumber, szFirstName, szSurname, szDateOfBirth,
szPostcode
FROM tblApplicationForms
WHERE pkiBarcodeNumber = CASE WHEN @pkiBarcodeNumber IS NULL THEN
pkiBarcodeNumber ELSE @pkiBarcodeNumber END
AND szFirstName LIKE '%' + @szFirstName + '%'
AND szSurname LIKE '%' + @szSurname + '%'
AND szDateOfBirth LIKE '%' + @szDateOfBirth + '%'
AND szPostcode LIKE '%' + @szPostcode + '%'
ORDER BY pkiBarcodeNumber
Thanks