• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1568
  • Last Modified:

How do you list date by month in dataview sort

I have a dataset and want to create a different listing for each month through a dataview sort. If I have a month column in the database the below code works, the problem is the data in the database is listed in a date format 12/12/2009. In an sql command I can use month(dateorder)=5 but I get an error if I use this code in the dataview sort. Any suggestions?


This works if the column name in the database is month
dv = New DataView(ds.Tables(0), "month=5", "dateorder Desc", DataViewRowState.CurrentRows)
 
This doesn't work because column dateorder is in date format 12/12/2009. It says function not found.
dv = New DataView(ds.Tables(0), "month(dateorder)=5", "dateorder Desc", DataViewRowState.CurrentRows)

Open in new window

0
designaire
Asked:
designaire
  • 9
  • 5
1 Solution
 
oobaylyCommented:
You could try the SubString function:
http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression(VS.80).aspx

Your example has made it ambiguous as to what the date format is.
' Assuming the data is the format dd/MM/yyyy
dv = New DataView(ds.Tables(0), "SUBSTRING(dateorder, 4, 2)=5", "dateorder Desc", DataViewRowState.CurrentRows)

Open in new window

0
 
designaireAuthor Commented:
Thanks but it didn't work...

Type mismatch in function argument: Substring(), argument 1, expected System.String.
0
 
oobaylyCommented:
Ah yes, how about converting the Date to a String. A bit kludgy, but the range functions available in ADO.Net Expressions isn't great compared to SQL Server.
' Assuming the data is the format dd/MM/yyyy
dv = New DataView(ds.Tables(0),_
  "SUBSTRING(CONVERT(dateorder, 'System.String'), 4, 2)=5", _
  "dateorder Desc", DataViewRowState.CurrentRows)

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
designaireAuthor Commented:
Cannot perform '=' operation on System.String and System.Int32.

I tried...
"SUBSTRING(CONVERT(dateorder, 'System.String'), 4, 2)=April",
Cannot perform '=' operation on System.String and System.Int32.

and
"SUBSTRING(CONVERT(dateorder, 'System.String'), 4, 2)"=5,
Conversion from string "SUBSTRING(CONVERT(dateorder, 'Sy" to type 'Double' is not valid.

"SUBSTRING(CONVERT(dateorder, 'System.String'), 4, 2)" = "5",
or
"SUBSTRING(CONVERT(dateorder, 'System.String'), 4, 2)" = "may",

I don't get any error message but it doesn't display any rows and there is a may or 5 row
0
 
oobaylyCommented:
The expression needs to be in a single string, as the DataView needs to evaluate it. Your last two example compile, but don't work for the following reason. The compiler sees

dv = New DataView(ds.Tables(0),_
  "SUBSTRING(CONVERT(dateorder, 'System.String'), 4, 2)" = "5", _
  "dateorder Desc", DataViewRowState.CurrentRows)

as

dv = New DataView(ds.Tables(0),_
  False, _
  "dateorder Desc", DataViewRowState.CurrentRows)

I did however forget that we're dealing with strings, so "05" <> "5", so we either have to change the RHS to "05", or change both sides of the expression to an integer

// Comparison using Strings
dv = New DataView(ds.Tables(0),_
  "SUBSTRING(CONVERT(dateorder, 'System.String'), 4,2) = '05'", _
  "dateorder Desc", DataViewRowState.CurrentRows)
 
// Comparison using Integers
dv = New DataView(ds.Tables(0),_
  CONVERT(SUBSTRING(CONVERT(dateorder, 'System.String'), 4,2), 'System.Int32') = 5", _
  "dateorder Desc", DataViewRowState.CurrentRows)

Open in new window

0
 
designaireAuthor Commented:
The data is in the format dd/MM/yyyy....

Comparison using Strings gives no results again..

The Comparison using Integers says..
Input string was not in a correct format.

The exception directs me to this...
 The first three statements run correctly.
Console.WriteLine(Double.Parse("32,115"))
Console.WriteLine(Double.Parse("32115"))
Console.WriteLine(Double.Parse("32.115"))
' The following statement throws a FormatException.
' Console.WriteLine(Double.Parse("32 115"))
Similarly, Boolean.Parse throws this exception if the string argument is not either "True" or "False".
' This statement runs correctly.
Console.WriteLine(Boolean.Parse("True"))
' This statement throws a FormatException.
' Console.WriteLine(Boolean.Parse("True"))

I hate to add a month column, the date is already in there. I don't understand it enough to try anything myself.
0
 
oobaylyCommented:
I'm not getting why it's not working for you. The following code uses both sets of expressions I've given you, and both DataViews created have the correct number of rows (31 for May).

If that doesn't work, it would appear that dateorder isn't a DateTime type. You could of course test that by using the following code to confirm what datatype is stored in dateorder:
MessageBox.Show(ds.Tables(0).Columns("dateorder").DataType.ToString())

Finally, another option would be to add another column to the select query
ie.
SELECT dateorder, MONTH(dateorder) AS [dateordermonth] ...
Dim table As New DataTable()
table.Columns.Add("dateorder", GetType(DateTime))
 
For i As Integer = 0 To 365
  table.Rows.Add(DateTime.Now.AddDays(i))
Next
 
Dim dvString As New DataView(table, _
  "SUBSTRING(CONVERT(dateorder, 'System.String'), 4,2) = '05'", _
  "dateorder DESC", DataViewRowState.CurrentRows)
 
Dim dvInteger As New DataView(table, _
  "CONVERT(SUBSTRING(CONVERT(dateorder, 'System.String'), 4,2), 'System.Int32') = 5", _
  "dateorder DESC", DataViewRowState.CurrentRows)

Open in new window

0
 
designaireAuthor Commented:
I looked at the database and it is TimeDate. It wouldn't surprise me if I'm doing something wrong, I'm very new at this. Here's the entire page. I have a few lines of code commented out becuase I was trying different things. I'm going to try changing the sql command.
<%@ Import Namespace="System.Data.OleDb" %>
<%@ Import Namespace="System.Data" %>
 
<script runat="server">
       
    Sub Page_Load()
        Dim sql, sqlJan
        Dim StringConnection = "Provider=Microsoft.Jet.OLEDB.4.0; data source=" & Server.MapPath("~\App_Data\db1.mdb")
        'StringConnection.Open()
        sql = "SELECT * FROM tblImg"
        
        Dim ds As New DataSet()
        Dim objectconnection As New OleDbConnection(StringConnection)
        Dim command As New OleDbCommand(sql, objectconnection)
        Dim objAdapter As New OleDbDataAdapter(sql, objectconnection)
        objAdapter.SelectCommand = command
        objAdapter.Fill(ds, "Events")
        'Dim objDataView As New DataView(objDataSet.Tables("Events"))
        Dim objDataView As New DataView(ds.Tables("Events"))
        objAdapter.Dispose()
        command.Dispose()
        
       
        Dim dv As DataView
        'dv = New DataView(ds.Tables(0), "SUBSTRING(dateorder, 4, 2)=5", "dateorder Desc", DataViewRowState.CurrentRows)
        'dv = New DataView(ds.Tables(0), "SUBSTRING(CONVERT(dateorder, 'System.String'), 4, 2)" = "5", "dateorder Desc", DataViewRowState.CurrentRows)
 
        'dv = New DataView(ds.Tables(0), "SUBSTRING(CONVERT(dateorder, 'System.String'), 4,2) = '5'", "dateorder Desc", DataViewRowState.CurrentRows)
 
        dv = New DataView(ds.Tables(0), "CONVERT(SUBSTRING(CONVERT(dateorders, 'System.String'), 4,2), 'System.Int32') = 5", "dateorders Desc", DataViewRowState.CurrentRows)
        
        'dv = New DataView(ds.Tables(0), "month = 5", "dateorder Desc", DataViewRowState.CurrentRows)
        'dv = New DataView(ds.Tables(0), CStr(Month(Date.FromOADate("dateorder"))) = 5, "dateorder Desc", DataViewRowState.CurrentRows)
        tblImg.DataSource = dv
        tblImg.DataBind()
        'StringConnection.Close()
    End Sub
   Public Function imageURL(ByVal img_id) As String
        Return ("retrieveImages.aspx?id=" & img_id)
    End Function
    
    Protected Sub repReview_PreRender(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim myRepeater As Repeater = DirectCast(sender, Repeater)
        Dim counter As Integer = myRepeater.Items.Count
        If counter = 0 Then
            myRepeater.Visible = False
        End If
    End Sub
   
</script>
 
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>Store & Display Images From Database</title>
    <style type="text/css">
    body
    {
        font-family:Verdana;
        font-size:12px;
    }
    </style>
</head>
<body>
<form runat="server" action="Default3.aspx">
 
<asp:Repeater ID="tblImg" runat="server" OnPreRender="repReview_PreRender"> 
 
 
<HeaderTemplate>
<table border="1" width="100%">
<tr><td colspan="7"><div align="center">janet</div></td></tr>
</HeaderTemplate>
<ItemTemplate>
<tr>
<td><%#Container.DataItem("img_id")%></td>
<td><%#Container.DataItem("dateorder")%></td>
<td><%#Container.DataItem("title")%></td>
<td><%#Container.DataItem("times")%></td>
<td><%#Container.DataItem("description")%></td>
<td> <asp:Image ID="imgSaved" runat="server" ImageUrl='<%# imageURL(DataBinder.Eval(Container.DataItem, "img_id")) %>'
                                AlternateText='<%#DataBinder.Eval(Container.DataItem,"img_id") %>' /></td>
</tr>
</ItemTemplate>
<FooterTemplate>
</table>
</FooterTemplate>
</asp:Repeater>
    &nbsp;&nbsp;&nbsp;<br />
    <br />
<asp:label id="txtLabel" runat="server" />
 
<asp:Repeater ID="Jan" runat="server" OnPreRender="repReview_PreRender"> 
<HeaderTemplate>
<table width="622" border="1" align="center">
<tr><td colspan="7"><div align="center">vvv</div></td></tr>
</HeaderTemplate>
 
<ItemTemplate>
<tr>
<td><%#Container.DataItem("title")%></td>
<td><%#Container.DataItem("times")%></td>
<td><%#Container.DataItem("description")%></td>
<td> <asp:Image ID="imgSaved" runat="server" ImageUrl='<%# imageURL(DataBinder.Eval(Container.DataItem, "img_id")) %>'
                                AlternateText='<%#DataBinder.Eval(Container.DataItem,"img_id") %>' /></td>
</tr>
</ItemTemplate>
<FooterTemplate>
 
</table>
</FooterTemplate>
 
</asp:Repeater>
    &nbsp;&nbsp;
 
 
</form>
 
</body>
</html>

Open in new window

0
 
designaireAuthor Commented:
Sorry I just changed the column to dateorders instead of dateorder  in case in was come kind of predefined word. That didn't work either.
0
 
designaireAuthor Commented:
This works fine...maybe I'll just use this

SELECT dateorder, MONTH(dateorder) AS [dateordermonth] ...
0
 
designaireAuthor Commented:
It seems like it might be access. I have the date set as short date but when it is read from the database it sets a time at 12:00am. Its doesn't say 12:00am in the datebase it just has the short date. Unless there's a way to change that.
0
 
oobaylyCommented:
It's been a good while since I've used access. How were the dates inserted into the table? I can't see why it would make assumptions about the time.

Also, what happens if you add a computed column to the DataTable that casts the Date to a String, and display it in the rendered table. this way you can verify what format is being used to convert the DateTime value to a String.
'' Add this after retrieving the table
ds.Tables("Events").Columns.Add("computed_dateorder", GetType(String), "CONVERT(dateorders, 'System.String')")
 
<!-- Add this to your repeater -->
<td><%#Container.DataItem("computed_dateorder")%></td>

Open in new window

0
 
designaireAuthor Commented:
Just got back to working on this...
The results form the above are as I expected...

5/21/2009 12:00:00 AM

I don't know why you get the time...There is no time in the database.
0
 
designaireAuthor Commented:
I used this code...Thanks

Finally, another option would be to add another column to the select query
ie.
SELECT dateorder, MONTH(dateorder) AS [dateordermonth] ...
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 9
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now