0

This is my Query 

SELECT TOP 1 MAX(CAST(Id AS int)) , Ddate, Name FROM WonPrize WHERE Ddate=@Ddate GROUP BY Id,Ddate,Name

The Query is giving me an error code on VB.NET.

IErrorInfo.GetDescription failed with E_FAIL(0x80004005)

I don't think I have an error with the syntax that I am composing

I am trying to display the MAX ID but MAX(Id) is not displaying the Number 2 Id so I tried to use this MAX(CAST(Id AS int)). and it gives me an error code. What should be my mistakes?

I am using the oledb.

Noypipyon
  • 1
  • 1
  • Exclude `Id` from `GROUP BY` clause - this field is included into output fieldset as an argument of aggregate function. PS. Where do you substitute the @Ddate placeholder with the value and how do you do it? PPS. You select data for a single date only, and it is known (@Ddate). I'd recommend to exclude this field from SELECT and GROUP BY sections. – Akina Aug 02 '18 at 04:42
  • @Akina I already excluded that Id from the group but nothing happen. anyway, I have resolved the problem on my own. Thank You for the response. – Noypipyon Aug 02 '18 at 06:46
  • @Biswapriyo there is no Access issues. – Noypipyon Aug 02 '18 at 06:47

1 Answers1

0

Due to oledb bug on VB.NET I just change the syntax.

From:

SELECT TOP 1 MAX(CAST(Id AS int)) , Ddate, Name FROM WonPrize WHERE Ddate=@Ddate GROUP BY Id,Ddate,Name

To:

SELECT TOP 1 Id, Ddate, Name FROM WonPrize WHERE Ddate=@Ddate AND Id=(SELECT MAX(Id) FROM WonPrize)

And This resolves my Problem.

oledb is not working together with 2nd and 3rd column value using MAX(value) and MIN(value) I decided to put it on WHERE statement since the MAX and MIN will only work on oledb on a single value. And I think that the First syntax is only working on SQLClient and not on OLEDB.

Noypipyon
  • 1
  • 1