I am learning how to use the Visual Basic 2019/2017. Currently, I have a problem to find the right syntax of SQL within the program code to use. In order to simplify my questions, I have just created the following simple code to test and ask for help.
Very simple code:
Public Class Form1
Dim con As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:\VSProjects\vbPractice\Ch07\dbPractice701\broadway_tickets.accdb")
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load Try Dim sql As String Dim cmd As New OleDb.OleDbCommand Dim dt As New DataTable Dim da As New OleDb.OleDbDataAdapter con.Open() sql = "Select RTRIM(Name) AS Show, FORMAT(Show_Time, 'mmm dd, yyyy' ) AS Date1, FORMAT(Show_Time, 'hh:mm:ss') AS Time1 from broadway" cmd.Connection = con cmd.CommandText = sql da.SelectCommand = cmd da.Fill(dt) DataGridView1.DataSource = dt Catch ex As Exception MsgBox(ex.Message) Finally con.Close() End Try End Sub
The above has been tested. It works.
Now, I want to replace the line of SQL with this:
sql = "Select RTRIM(Name) AS Show, FORMAT(Show_Time, 'mmm dd, yyyy' ) AS Date1, FORMAT(Show_Time, 'hh:mm:ss tt') AS Time1 from broadway"
I think it is clear that the tt refers to AM/PM format of showing time. But, it does not work!!
It gave me the results like this in the DataGridview1 result:
Show Date1 Time1
Aladdin Oct 26, 2021 19:00:00 tt
Chicago Oct 27, 2021 14:30:00 tt
Harry Potter Oct 27, 2021 13:00:00 tt
This is kind of strange. Can anyone tell me what is the correct syntax of showing AM/PM
in Visual Basic 2019/.net?
Another problem is... if I change the date format like this:
sql = "Select RTRIM(Name) AS Show, FORMAT(Show_Time, 'd', 'en-US') AS Date1, FORMAT(Show_Time, 'hh:mm:ss') AS Time1 from broadway"
It gave me an error:
"The provider could not determine the Object value. For example, the row was just created, the default for the Object column was not available, and the consumer had not yet set a new Object value."
Clearly, it could not recognize the syntax of FORMAT with locale.
I am using the Microsoft Visual Studio Community 2019 version 16.11.5
with .Net Framework version 4.8.04084
I have installed Microsoft SQL Server Data Tools version 16.0.62107.28140
Did I missed any components to install?
It is kind of annoying. It looks like the syntax problem but when I look up the documentation over the web, I could not find the solution.
Please kindly help.