Click here to Skip to main content
12,635,662 members (26,553 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: VB Gridview VB.NET
I get the following error when trying to sort my datatable by date range in a gridview:
'String was not recognized as a valid DateTime.'
AccessDateFrom and To are datepickers.

Any help please BTW I'm new to this so sorry if its a simple mistake on my part.

My code:

Private Sub ReportGen_Click(sender As System.Object, e As System.EventArgs) Handles ReportGen.Click

ProjectRef = ProjectRefInput.Text
AppNameSelect = ApplicationSelection.Text
AccessDateFrom = DateFrom.Text
AccessDateTo = DateTo.Text

'Collect the data from the system
Username = System.Security.Principal.WindowsIdentity.GetCurrent.Name
ComputerName = Environment.MachineName
AccessTime = Date.Today
AppName = My.Application.Info.AssemblyName

Try

Using cnn As New OleDbConnection(ConnString)
cnn.Open()

Using cmd As New OleDbCommand(DbUpdate, cnn)
cmd.Parameters.AddWithValue("AccessDate", AccessTime)
cmd.Parameters.AddWithValue("UserName", Username)
cmd.Parameters.AddWithValue("ComputerName", ComputerName)
cmd.Parameters.AddWithValue("AppName", AppName)
cmd.ExecuteNonQuery()
Using SelectQuery As New OleDbCommand(DbSelect, cnn)

SelectQuery.Parameters.AddWithValue("AppName", AppNameSelect)

Dim ResultsTable As New OleDb.OleDbDataAdapter(SelectQuery)
Dim DataTable As New DataTable("App_Results")
ResultsTable.Fill(DataTable)
Dim DTFilterDate As DataView = New DataView(DataTable)

DTFilterDate.RowFilter = "AccessDate >= #" & AccessDateFrom & " and AccessDate <= " & AccessDateTo & "#"
ResultsTable.Fill(DataTable)
DataGridView1.DataSource = DTFilterDate

End Using
End Using
cnn.Close()
cnn.Dispose()

End Using

Catch ex As Exception
'Unable to find or update the database

Err.Number = 2
Call ErrorCodeHandle()

End Try
RowCounter = DataGridView1.RowCount
TotalRecords.Text = RowCounter

End Sub
Posted 23-Aug-12 18:06pm
Comments
Martok867 23-Aug-12 23:49pm
   
Hi Abhinav,
Thanks for your reply.
AccessDateFrom is defined as a date and shows #8/24/2012# as the passed var. However in the DB it is in the format 24/08/2012?
if i do the following:

Dim AccessDateFromFormat As Date
Dim AccessDateToFormat As Date

If Date.TryParse(AccessDateTo, AccessDateToFormat) Then
MsgBox(" Old date is :" & AccessDateFrom & "")
MsgBox("New date is :" & AccessDateFromFormat & "")

Old date is :24/08/2012

New Date is :12:00:00 AM

??
Martok867 24-Aug-12 0:10am
   
As I am filtering the gridview from a DB select query, would I be better off adding these to the query itself?
At present it only uses one var (AppName) to select the records. I would like to select with the following - AppName (or All), ProjectRef (optional - if not selected then ALL) and the date range (From and To datepickers)
DB connection , Insert and Select statemants below:

'Defind database connection string, update and select query
Dim ConnString As String = _
"Provider = Microsoft.ACE.OLEDB.12.0; Data Source = \\AppServer\TESTONLY.accdb; "

Dim DbUpdate As String = _
"INSERT INTO [ApplicationLogging] (AccessDate, UserName, ComputerName, AppName ) Values(?,?,?,?)"

Dim DbSelect As String = _
"SELECT AccessDate, UserName, ComputerName,AppName, ProjectRef FROM [ApplicationLogging] where AppName =? "

Would this be the better option?
If so can anyone assit me in redoing the select query for the option I would like?
Martok867 6-Sep-12 22:52pm
   
If I hard code the date in US format it works, if I change the date in the DB as US it works.
How can I set this to dd/MM/yyyy to return the records I want??

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Check if AccessDateFrom and AccessDateTo contain appropriate date time values.
You can use DateTime.TryParse[^] to do so.
  Permalink  
Comments
Prasad_Kulkarni 24-Aug-12 0:11am
   
Comment from OP:
Hi Abhinav,
Thanks for your reply.
AccessDateFrom is defined as a date and shows #8/24/2012# as the passed var. However in the DB it is in the format 24/08/2012?
if i do the following:

Dim AccessDateFromFormat As Date
Dim AccessDateToFormat As Date

If Date.TryParse(AccessDateTo, AccessDateToFormat) Then
MsgBox(" Old date is :" & AccessDateFrom & "")
MsgBox("New date is :" & AccessDateFromFormat & "")

Old date is :24/08/2012

New Date is :12:00:00 AM

??

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.161208.2 | Last Updated 23 Aug 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100