What are the correct syntax of SQL in Visual Basic 2019?

jasonfan2000 21 Reputation points
2021-10-25T09:32:35.937+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,482 questions
VB
VB
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
2,540 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 110.7K Reputation points
    2021-10-25T10:30:05.46+00:00

    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.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. jasonfan2000 21 Reputation points
    2021-10-25T15:37:23.057+00:00

    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!!