Correct syntax please

Giganews Newsgroups
Subject: Correct syntax please
Posted by:  M Joomun (m.a.joom…@qmul.ac.uk)
Date: Thu, 14 Oct 2010

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>

Replies