asked
Datareader read dates greater than
How do I get the datareader to read and return data with ClosedDate greater than the parameter that is passed in? Dim dte As DateTime If reader("ClosedDate") Is DBNull.Value Then dte = DateTime.MinValue Else dte = Convert.ToDateTime(reader("ClosedDate")) End If list.Add(New JobKeys_vw() With { _ .Contract = reader("Contract").ToString(), _ .ContractDesc = reader("ContractDesc").ToString(), _ .ContractTerms = reader("ContractTerms").ToString(), _ .Job = reader("Job").ToString(), _ .JobTitle = reader("JobTitle").ToString(), _ .JobFunding = reader("JobFunding").ToString(), _ .ClientName = reader("ClientName").ToString(), _ .EntityName = reader("EntityName").ToString(), _ .ProjMgrName = reader("ProjMgrName").ToString(), _ .OfficeName = reader("OfficeName").ToString(), _ .ClosedDate = dte _ })
Do you mean to filter the data after it already came back from the SQL Server? In this case, it would be cheaper to change the procedure to accept a parameter which will restrict returning rows to inlude only those which satisfy your criteria. Otherwise, you will have to filter the data after it already came back and this is not a very good option because you end up with more traffic than you actually need. Maybe I just don't understand the question though in which case could you please provide more details? Thank you.
We already discussed the downside of the filtering the data after it already came back from the data store, but nevertheless, if this approach is chosen then you can simply use LINQ to filter your results after your list has already been populated. While this is clearly the most expensive option, it will work. In C# the syntax is: List filteredList = (List )list.Where(item => item.ClosedDate > your_value); -- And I do believe that it is translated to
VB.NET like this: Dim filteredList As List(Of JobKeys_vw) = _ DirectCast(list.Where(Function(item) item.ClosedDate > your_value), _ List(Of JobKeys_vw)) There is another option (without using the LINQ and the lambda expressions: Modify your code to only add the item to the list if the reader("ClosedDate")) > your_value, i.e. If dte > your_value Then list.Add(New JobKeys_vw() With { _ .Contract = reader("Contract").ToString(), _ .ContractDesc = reader("ContractDesc").ToString(), _ .ContractTerms = reader("ContractTerms").ToString(), _ .Job = reader("Job").ToString(), _ .JobTitle = reader("JobTitle").ToString(), _ .JobFunding = reader("JobFunding").ToString(), _ .ClientName = reader("ClientName").ToString(), _ .EntityName = reader("EntityName").ToString(), _ .ProjMgrName = reader("ProjMgrName").ToString(), _ .OfficeName = reader("OfficeName").ToString(), _ .ClosedDate = dte _ }) End If And finally, the best method would be to simply add another procedure, similar to the one you already have, but including the where clause in the end, so you don't have to change your code. Please clarify the source of the problem with the date conversion, it should be something simple to adjust. Oleg
Oleg, I agree that the stored procedure should have the where clause. This is the option that I am going with. In all the spaces where you specify "your_value" is where the problem is. On the interface, the user uses the date picker to choose the date. But this date is not going to the server as a datetime. The error that I am receiving is telling me that I must parse that date before I can store it in a variable (By val ClosedDate as DateTime). This is what I need to resolve.
I corrected he syntax for parsing the value... still get an error saying that "cannot convert parameter value string to datetime format. The code stops here: Using dcmd As SqlCommand = New SqlCommand("dbo.StoredProcedure", conn) dcmd.CommandType = CommandType.StoredProcedure Dim prms As New SqlParameter("@ClosedDate", SqlDbType.DateTime) prms.Value = ClosedDate & "%" dcmd.Parameters.Add(prms) Using reader As SqlDataReader = dcmd.ExecuteReader
@technette Yes, this is what I tried to mention in the comment under your question. Suppose you have a text box named **dtJobsClosed**. When your get it's **Text** property value it is a string which needs to be converted to DateTime. This simply means that instead of
ConHist.SPJOBClosedDataAsync(dtJobsClosed.Text) ' you need to do something like this: Dim d as DateTime = DateTime.Parse(dtJobsClosed.Text) ConHist.SPJOBClosedDataAsync(d)By the way, usually DatePicker controls expose the property named Date (or sometimes it is called Value), so you can also try
ConHist.SPJOBClosedDataAsync(dtJobsClosed.Date) ' or .Value, whichever applies to your control ConHist.SPJOBClosedDataAsync(dtJobsClosed.Value)
