It seems that you have an Access database, not an SQL Server, therefore you are using the Format function from VBA. Try this:
_. . . FORMAT(Show_Time, 'hh:mm:ss AM/PM') AS Time1 . . ._
It will output AM or PM.
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Hi,
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
End Class
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.
Regards,
Jason Fan
It seems that you have an Access database, not an SQL Server, therefore you are using the Format function from VBA. Try this:
_. . . FORMAT(Show_Time, 'hh:mm:ss AM/PM') AS Time1 . . ._
It will output AM or PM.
So, you are telling me that the Access database should use syntax of VBA instead. The SQL syntax does not apply to it. Now, I understand. Thank you very much. I got one little question:
You see that FORMAT(Show_Time, 'd', 'en-US') AS Date1, FORMAT(Show_Time, 'hh:mm:ss') AS Time1.....
Those Date1 and Time1 will finally become the column heading on DataGridview1 result.
In fact, they should be 'Date' and 'Time' instead. Since they are the reserved keywords in VB, I have no idea how I can use them as the column headings. I have tried to put a quote before and after them but it does not work.
Please help!!