I have created a stock control system in vb.net using sequel db.. i have spent hours trying to sort out my date problems.. 1stly the system.date.now was MM/dd/yyyy and i realised that the db saved the data as dd/MM/yyyy (not bring up any errors as it was 04/04/2010) now its 14/04/2010 when testing.. the system gets the date as 04/14/2010 (MM/dd/yyyy) but saves it as 04/12/2010 because it doesn't allow 14months (thinking its the 4th dec NOT 14th april).. neways before i found this problem i was using this code to read the db...
Public Sub getLogs(ByVal grid) 'UPDATES GRID (STOCK N RETURNS FORM)
Dim lst As DataGridView
Dim dt As Date = Date.Now.ToString("dd/MM/yyyy")
lst = grid
dbOpen()
Dim cmd = "SELECT UnitId, Action, Qty_Updated, Date FROM Log WHERE (CONVERT (VARCHAR(10), Date, 101) = '" + dt + "')"
Dim sqlCommand1 As New SqlCommand(cmd, connection)
Dim sqlAdapter As New SqlDataAdapter
sqlAdapter.SelectCommand = sqlCommand1
Dim sqlDataTable1 As New DataTable
sqlAdapter.Fill(sqlDataTable1)
lst.DataSource = sqlDataTable1
dbClose()
End Sub
To solve that db problem i change the regional settings to US so now the data saves fine database.. (MM/dd/yyyy) however when using the same above code it brought up an error say 'cant change date to sting' or something.. so i changed
Dim dt As Date = Date.Now.ToString("dd/MM/yyyy")
to
Dim dt As Date = Date.Now.ToString("MM/dd/yyyy")
but now the SQL statement wont work..
Dim cmd = "SELECT UnitId, Action, Qty_Updated, Date FROM Log WHERE (CONVERT (VARCHAR(10), Date, 101) = '" + dt + "')"
the 'dt' variable is fine (04/14/2010) BUT the CONVERT function doesn't seem to work.. is there a way of puting it to String so i can see what it trying to equal to (or if someone could check the convert function).. because the convert function (101) is not converting the database record (MM/dd/yyyy hh:mm:ss am) to (MM/dd/yyy)...
there is definatly a record within the database of date 04/14/2010 and the 'dt' is 04/14/2010..
Sorry if ive gone on abit.. just trying to give you the best idea of what the problem is
Thanks in advance
Public Sub getLogs(ByVal grid) 'UPDATES GRID (STOCK N RETURNS FORM)
Dim lst As DataGridView
Dim dt As Date = Date.Now.ToString("dd/MM/yyyy")
lst = grid
dbOpen()
Dim cmd = "SELECT UnitId, Action, Qty_Updated, Date FROM Log WHERE (CONVERT (VARCHAR(10), Date, 101) = '" + dt + "')"
Dim sqlCommand1 As New SqlCommand(cmd, connection)
Dim sqlAdapter As New SqlDataAdapter
sqlAdapter.SelectCommand = sqlCommand1
Dim sqlDataTable1 As New DataTable
sqlAdapter.Fill(sqlDataTable1)
lst.DataSource = sqlDataTable1
dbClose()
End Sub
To solve that db problem i change the regional settings to US so now the data saves fine database.. (MM/dd/yyyy) however when using the same above code it brought up an error say 'cant change date to sting' or something.. so i changed
Dim dt As Date = Date.Now.ToString("dd/MM/yyyy")
to
Dim dt As Date = Date.Now.ToString("MM/dd/yyyy")
but now the SQL statement wont work..
Dim cmd = "SELECT UnitId, Action, Qty_Updated, Date FROM Log WHERE (CONVERT (VARCHAR(10), Date, 101) = '" + dt + "')"
the 'dt' variable is fine (04/14/2010) BUT the CONVERT function doesn't seem to work.. is there a way of puting it to String so i can see what it trying to equal to (or if someone could check the convert function).. because the convert function (101) is not converting the database record (MM/dd/yyyy hh:mm:ss am) to (MM/dd/yyy)...
there is definatly a record within the database of date 04/14/2010 and the 'dt' is 04/14/2010..
Sorry if ive gone on abit.. just trying to give you the best idea of what the problem is
Thanks in advance
