Query Error = Incorrect sentence in ')'

[zurehman]

Hi , members

this code show error in vb.net 2008

If Con.State = 1 Then Con.Close()
 Con.Open()
 Dim strSQL As String
  Dim dt As DataTable

   strSQL = "select count(EntryNo) from(Select distinct(EntryNo) from Cash where year(Cash.PmtDat)= '" & yyyy & "')"

 dt = GetmyTable(strSQL)
 Rc = dt.Rows(0).Item("EntryNo")

please guild me
thanks

[sknake]

I think you have over-complicated your SQL statement. You could simply use:

Select COUNT(DISTINCT(EntryNo))
From Cash
Where PmtDat = 5

A full example:

IF OBJECT_ID('tempdb..#Cash', 'U') IS NOT NULL DROP TABLE #Cash
Create Table #Cash
(
  EntryNo int,
  PmtDat int
)

Insert Into #Cash (EntryNo, PmtDat) Values (1, 2)
Insert Into #Cash (EntryNo, PmtDat) Values (2, 3)
Insert Into #Cash (EntryNo, PmtDat) Values (3, 4)
Insert Into #Cash (EntryNo, PmtDat) Values (4, 5)
Insert Into #Cash (EntryNo, PmtDat) Values (5, 5)

Select COUNT(DISTINCT(EntryNo))
From #Cash
Where PmtDat = 5

[zurehman]

thanks Members
solved query is as under .... my prolem solved

strSQL = "SELECT COUNT(DISTINCT EntryNo) AS COUNT  FROM Cash where year(Cash.PmtDat)= '" & yyyy & "'"

[sknake]

Thanks for reporting back your solution

Please mark this thread as solved if you have found an answer to your question (or answered it yourself :P) and good luck!