I'm developing a Windows Forms app using VS 2008 with a SQL Server 2005 database.

I've developed a search where users can search for data by entering one or two criteria. One of these is a date which corresponds to a date field in my database. The user clicks on a button, which feed the criteria into a stored procedure. The results are displayed in a datagridview control.

While the functionality works well with the non-date criteria, whenever a date criteria is used on it's own, no data is returned, even though I know that data matching that date exists in the db.

I've tried lots of ways of casting the date, but still can't get it to work. Code and SQL are shown below. Does someone know where I've gone wrong? 'DateAssayRequested' is the date in question.



'set up and initialise connection
conn = New SqlConnection("server=WIPM073W;uid=sa;pwd=ADMIN2005sql;database=R epeatAssays")
comm = New SqlCommand

'set properties of comm so it uses conn & recognises which stored proc to execute
comm.Connection = conn
comm.CommandText = "SPSearch"
comm.CommandType = CommandType.StoredProcedure

'create input parameters, set types and values
param = comm.CreateParameter
param.ParameterName = "@assayid"
param.Direction = ParameterDirection.Input
param.Value = txtLabNo.Text

'convert string value of txtDoB into something the datetime datatype or null value
'if nothing is entered
If Len(txtDtRequest.Text) > 0 Then

Dim datDateEnd As DateTime = CDate(txtDtRequest.Text)
param2 = comm.CreateParameter
param2.ParameterName = "@dateassrequested"
param2.Direction = ParameterDirection.Input
param2.Value = datDateEnd

Else

param2 = comm.CreateParameter
param2.ParameterName = "@dateassrequested"
param2.Direction = ParameterDirection.Input
param2.Value = txtDtRequest.Text

End If

comm.Parameters.Add(param)
comm.Parameters.Add(param2)

conn.Open()

objAdapter = New SqlDataAdapter(comm)
objDt = New DataTable
objAdapter.Fill(objDt)


If objDt.Rows.Count > 0 Then

dgrdSearch.DataSource = objDt

End If

conn.Close()


<Stored procedure SQL>

@assayid int,
@dateassayrequested varchar(20)

AS

SELECT assayid, dateassayrequested

FROM tblassay


WHERE (datalength(@assayID) = 0 OR assayid like @assayid)
AND (@dateassayrequested IS NULL OR dateassayrequested like @dateassayrequested + '%')

</SQL>

<Stored procedure SQL>

@assayid int,
@dateassayrequested varchar(20)

AS

SELECT assayid, dateassayrequested

FROM tblassay


WHERE (datalength(@assayID) = 0 OR assayid like @assayid)
AND (@dateassayrequested IS NULL OR dateassayrequested like @dateassayrequested + '%')

</SQL>

I don't have a SQL server right now... but for me your VB code is ok...

What I don't understand is this line:

@dateassayrequested varchar(20)

Why do you VARCHAR instead of DATE?

Look here: http://msdn.microsoft.com/en-us/library/bb630352.aspx

Maybe is not important but anyway, I can remember that when I have to query my sql db (direct query, without VB) I have to use this kind of format: 'dd/mm/yyyy' or 'mm/dd/yyyy' depending on your config...

So, be sure to right format the date from user input to VB and from VB to SQL.


Hope this help

Thanks very much for the response. I'll give it a try.