Hello,

I'm building a Windows Forms App with VB.NET using SQL Server 2008 R2.

I've got a search form where users can search for records from the db.
The App calls a stored procedure which executes the sql below. The form
has two search criteria users have requested, 'labno' and 'DateAssayReq'.

The App works well for the date criteria and will find the correct
record(s), but will not return any records whatever I enter for the
labno, even though I know the labno exists in the db.

Can someone see what I've done wrong?

TIA.


<SQL>

@labno nvarchar(6) = NULL,
@DateAssayReq date


AS

SELECT assayid, DateAssayReq, LabNo, ReasonRepeat,
PersonReq, RepeatedYN, DateRepeated,
DtRepeatDy, PersonRepeat, ReasonNotRepeated, LIMSUpdateYN

FROM tblassay

WHERE (datalength(@labno) = 0 OR LabNo = @labno)
AND (DataLength(@DateAssayReq) = 0 OR DateAssayReq = @DateAssayReq)

</SQL>

M Joomun (m.a.joomun (AT) qmul (DOT) ac.uk) writes:
The part with datalength looks funky. "DataLength(@DateAssayReq)" will
never be 0. It will be 3 or NULL.

Use:

WHERE (@labno IS NULL OR LabNo = @labno)
AND (@DateAssayReq IS NULL OR DateAssayReq = @DateAssayReq)


Also make sure that you pass NULL for search conditions that do not apply.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On 14/10/2010 16:07, Erland Sommarskog wrote:
Thanks for your response. The syntax you gave was what I was trying
originally and there's no difference in the results returned; correct
results if 'DateAssayReq' is used as search criteria, no results
returned if labno is used, either on it's own or in conjunction with
'DateAssayReq'.

Any other ideas?

On Mon, 18 Oct 2010 09:32:39 +0100, M Joomun wrote:

Hi M Joomun,

That might be caused by a mismatch between the data type of the
parameter and the data type of the column.
Can you post the exact structure of the table, as a CREATE TABLE
statement? (You can use the generate function in SSMS to get this, or if
you have your table creation scripts in source control, you can use
those).

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

On 18/10/2010 13:19, Hugo Kornelis wrote:
Hello,

Thanks for the reply. As requested, below is the structure of the table
in question. Not sure if you wanted everything the create statement put
out, but shout if you do:

CREATE TABLE [dbo].[tblAssay](
[AssayID] [int] IDENTITY(1,1) NOT NULL,
[ADtdate] [datetime] NULL,
[ADtTime] [datetime] NULL,
[LabNo] [nvarchar](6) NULL,
[AssayTy] [smallint] NULL,
[ReasonRepeat] [tinyint] NULL,
[DateAssRequested] [datetime] NULL,
[TimeAssRequested] [datetime] NULL,
[PersonReq] [smallint] NULL,
[RepeatedYN] [smallint] NULL,
[DateRepeated] [datetime] NULL,
[DtRepeatDy] [nvarchar](9) NULL,
[PersonRepeat] [smallint] NULL,
[ReasonNotRepeated] [smallint] NULL,
[LIMSUpdateYN] [smallint] NULL,
[DateAssayReq] [date] NULL,
CONSTRAINT [aaaaatblAssay_PK] PRIMARY KEY NONCLUSTERED

M Joomun (m.a.joomun (AT) qmul (DOT) ac.uk) writes:
So far it looks good. But I think we need to see the whole picture.

So can you produce a script that includes:

1) Some INSERT statements.
2) The full code for the stored procedures.
3) Some calls to the stored procedure that demonstrates the problem?


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx