CODEWITHSUNDEEP
vb.netms-access
Daria Mihai
Daria Mihai

Reputation: 11

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

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

enter image description here

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.

Upvotes: 0

Views: 117

Answers (2)

Daria Mihai
Daria Mihai

Reputation: 11

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

Upvotes: 1

Alrik
Alrik

Reputation: 100

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.

Upvotes: 0

Related Questions