Sql syntax working in MS Access, but not in Visual Basic

Question

This is my first time posting so I'm sorry if I make any mistakes. So I have a database in access and I am trying to get the top 10 Client ID, the number of reservations made by the client and the sum of those reservations. I have this Sql code in MS Access and it works there, but when I enter it in Visual Basic, I get te following error. SQL syntax:

SELECT top 10 Clienti.CodCl, sum(Rezervari.SumaTotala) ,
count(Rezervari.CodRezervare)  
FROM Clienti 
INNER JOIN Rezervari ON (Clienti.CodCl = Rezervari.CodCl) 
  where datarezervarii BETWEEN  #6/21/2020#  AND  #6/23/2020# 
  group by Clienti.CodCl 
  order by sum(Rezervari.SumaTotala) desc

The result table in access

Visual basic error :

System.Data.OleDb.OleDbException: 'The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.'

Thank you.

Replace the SUM expression in your ORDER BY. Use ORDER BY 2 instead, where the 2 means the second column in the SELECT. Does that fix things? — Ken White
– Ken White, Commented Jun 22, 2020 at 23:24
"when I enter it in Visual Basic" - exactly how are you doing that? — Tim Williams
– Tim Williams, Commented Jun 22, 2020 at 23:26
Error message appears to be VB.Net (distinctly different from VBA). Please tag and describe appropriately. — Parfait
– Parfait, Commented Jun 22, 2020 at 23:57

Daria Mihai · Accepted Answer · 2020-06-23 11:29:05Z

1

I managed to solve the problem. For anyone looking for the same answer, this is the code that i have used:

Dim top1 As String
    top1 = "SELECT top 10 Clienti.CodCl, sum(Rezervari.SumaTotala) as suma, count(Rezervari.CodRezervare) FROM
    Clienti INNER JOIN Rezervari ON Clienti.CodCl = Rezervari.CodCl where rezervari.datarezervarii BETWEEN  #" & Inceput & "#  AND  #" & Sfarsit & "# group by Clienti.CodCl order by sum(Rezervari.SumaTotala) desc"
    Dim cmdtop1 As New OleDbCommand(top1, con)
    Dim dstop1 As New DataSet
    Dim datop1 As New OleDbDataAdapter(cmdtop1)
    datop1.Fill(dstop1, "Clienti INNER JOIN Rezervari ON Clienti.CodCl = Rezervari.CodCl")

    Dim readtop1 As OleDbDataReader
    readtop1 = cmdtop1.ExecuteReader
    readtop1.Read()

Blockquote

answered Jun 23, 2020 at 11:29

Daria Mihai

111 bronze badge

Sign up to request clarification or add additional context in comments.

Comments

Alrik · Accepted Answer · 2020-06-23 09:35:49Z

0

Give an Alias to the aggregated fields. It's likely that .NET do not call them "Expr1001" and if inthe code you are referring to them you get the error.

answered Jun 23, 2020 at 9:35

Alrik

1006 bronze badges

Collectives™ on Stack Overflow

Sql syntax working in MS Access, but not in Visual Basic

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related