Click here to Skip to main content
15,897,718 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
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

Check if AccessDateFrom and AccessDateTo contain appropriate date time values.
You can use DateTime.TryParse[^] to do so.
 
Share this answer
 
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)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900