Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: 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 17:06pm
Comments
Martok867 at 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 at 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 at 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 at 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
0 OriginalGriff 474
1 Gihan Liyanage 338
2 ChauhanAjay 180
3 Sergey Alexandrovich Kryukov 163
4 Vinay Mistry 160
0 Sergey Alexandrovich Kryukov 9,011
1 OriginalGriff 7,941
2 CPallini 2,603
3 Richard MacCutchan 2,121
4 Abhinav S 1,928


Advertise | Privacy | Mobile
Web03 | 2.8.140827.1 | Last Updated 23 Aug 2012
Copyright © CodeProject, 1999-2014
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