question

jasonfan2000-8485 avatar image
0 Votes"
jasonfan2000-8485 asked jasonfan2000-8485 commented

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

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

dotnet-visual-basicdotnet-sqlclient
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

jasonfan2000-8485 avatar image
0 Votes"
jasonfan2000-8485 answered jasonfan2000-8485 commented

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

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Check this: ... AS [Date], ... AS [Time].


1 Vote 1 ·

It works! Now I have learnt it. Thank you very MUCH!!! You are very helpful. Have a nice day.

0 Votes 0 ·