Date problems

[majhl]

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=RepeatAssays")
        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>

 

[ripp3r]

<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

 

[majhl]

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