Link to home
Start Free TrialLog in
Avatar of sanjshah12
sanjshah12Flag for United Kingdom of Great Britain and Northern Ireland

asked on

Loop Date records as Dates

Hi,

I am trying to retrieve loop a series of records as dates, the field eventDate is set to smalldatetime but I receive an error:

ex = {"Conversion from type 'Date' to type 'Long' is not valid."}

Open in new window



Public Function GetDisabledDates() As IEnumerable(Of DateTime)
        Dim disabledDates As New List(Of DateTime)()

'This works if added statically
        'disabledDates.Add(New DateTime(DateTime.Today.Year, DateTime.Today.Month, 7))

        Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ProjectConnectionString").ConnectionString)
        conn.Open()
        Dim cmd As SqlCommand = conn.CreateCommand()
        cmd.CommandText = "SELECT * FROM [tbl_DisabledDates] WHERE [eventDate] >= @Date"
        cmd.CommandType = CommandType.Text

        cmd.Parameters.AddWithValue("@Date", Today)

        Dim reader As SqlDataReader = cmd.ExecuteReader()

        Try

            While reader.Read()
                disabledDates.Add(New DateTime(reader("EventDate")))
            End While

        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try

        reader.Close()
        conn.Close()

        Return disabledDates

    End Function

Open in new window



Any help is appreciated.
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

>cmd.CommandText = "SELECT * FROM [tbl_DisabledDates] WHERE [eventDate] >= @Date"
For starters, I'm guessing you want to have the @Date outside of the double-quotes, and convert it to a string so you can concatenate it with the rest of the T-SQL,  like this..

cmd.CommandText = "SELECT * FROM [tbl_DisabledDates] WHERE [eventDate] >= '"  + CStr(@Date as string) + "'"

btw Is this Access VBA or VB.NET?  Neither zone is referenced in this question.
Avatar of sanjshah12

ASKER

Thanks Jim for your reply.

I will amend the query as per your suggestion.


This is VB.Net
< Added Access zone, deleted a couple of others >
Removed access zone, added vb.net
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks Gustav, that worked.
You are welcome!