question

technette avatar image
technette 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 _ })
sqldatareader
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image Oleg commented ·
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.
0 Likes 0 ·
technette avatar image technette commented ·
Oleg, Thank you for responding. You understood correctly... I would be creating more traffic by trying to filter the data afterward. I will try creating a separate service contract for this and create a separate stored procedure as well. I'll follow up soon...
0 Likes 0 ·
technette avatar image technette commented ·
I'm getting the following error and don't know how to convert the parameter value to datetime. Error: Failed to convert parameter value from a String to a DateTime
0 Likes 0 ·
Oleg avatar image Oleg commented ·
Which value? The one you need to feed to the parameter of the stored procedure or the one you need to use to filter your data after it already came back from the data store?
0 Likes 0 ·
technette avatar image technette commented ·
It looks like I have to parse the value in the text box: ConHist.SPJOBClosedDataAsync(dtJobsClosed.Text)
0 Likes 0 ·
Show more comments

1 Answer

·
Oleg avatar image
Oleg answered
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
7 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image WilliamD commented ·
+1 for the final paragraph more than the rest (however correct the c# and LINQ is) - Oleg is totally right in saying the filter should happen on the server, not the client.
1 Like 1 ·
technette avatar image technette commented ·
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.
0 Likes 0 ·
technette avatar image technette commented ·
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
0 Likes 0 ·
Oleg avatar image Oleg commented ·
@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)
0 Likes 0 ·
technette avatar image technette commented ·
I still get the same error. When I step through the code, the value that is passed in comes in with # signs on both sides. is this correct? Does this come from the date picker? I copied the followin from the step through... ClosedDate = #3/7/2010#
0 Likes 0 ·
Oleg avatar image Oleg commented ·
Usually, the # signs on both end of the value come from the VB IDE to denote that the value is actually a datetime. When do you get the error, exactly when the Sub is invoked or later in the body of the Sub named ConHist.SPJOBClosedDataAsync? If you have the line of code reading Dim d as DateTime = DateTime.Parse(dtJobsClosed.Text) and then call ConHist.SPJOBClosedDataAsync(d) there is no way that **d** is not a valid datetime if you are already at the line of code calling the sub. Please check that you did not forget to replace **dtJobsClosed.Text** with **d** when you call the sub.
0 Likes 0 ·
Oleg avatar image Oleg commented ·
Oops, I found the source of your problem. It is caused by the line reading prms.Value = ClosedDate & "%" You don't need to append the ampersand to the right of your (I presume already valid) datetime value :) Get rif of it and you should be good to go.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.