4 Replies - 685 Views - Last Post: 02 July 2013 - 08:42 PM Rate Topic: -----

#1 dave_mark  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 15-March 13

sql - You have written a subquery that can return more than one field

Posted 02 July 2013 - 06:10 PM

hi guys

is someone can solve this sql problem.. i want to display in my excel file this data: SW_Title, SW_Lang, SW,App, SW_Programmer, SW_ControlNo .in my table i want to display only the Distinct SW_Title that has the latest SW_ApproveDate.. its in the sql the error pls help. Attached is my table and my excel..

sql = "Select DISTINCT SW_Title from SRS_Software where SW_AppDate = (Select Max(SW_Appdate) From SRS_Software where SW_Section = '" & section & "')"

Dim cmd2 As OleDb.OleDbCommand = New OleDbCommand(sql, con)
Dim rdr As OleDbDataReader = cmd2.ExecuteReader

If rdr.HasRows Then
xrow = 6
num = 1
Do Until rdr.Read = False

xlWorksheet.Cells(xrow, 1).value = CInt(num)
xlWorksheet.Cells(xrow, 2).value = rdr("SW_Title")
xlWorksheet.Cells(xrow, 3).value = rdr("SW_Lang")
xlWorksheet.Cells(xrow, 4).value = rdr("SW_App")
xlWorksheet.Cells(xrow, 5).value = rdr("SW_Programmer")
xlWorksheet.Cells(xrow, 6).value = rdr("SW_ControlNo")

xrow += 1
num += 1
Loop

End If

Attached image(s)

  • Attached Image


Is This A Good Question/Topic? 0
  • +

Replies To: sql - You have written a subquery that can return more than one field

#2 Draps  Icon User is offline

  • D.I.C Head

Reputation: 34
  • View blog
  • Posts: 103
  • Joined: 20-November 10

Re: sql - You have written a subquery that can return more than one field

Posted 02 July 2013 - 06:23 PM

For checking if a row's string contains a sub string use the LIKE keyword instead of WHERE.
http://www.w3schools...ql/sql_like.asp

And if you want the data for those fields in your select you must request them all not just SW_Title from.
EG.
SELECT SW_Title, SW_Lang, SW,App, SW_Programmer, SW_ControlNo FROM SRS_Software


Hope this helps as I'm not entirely sure what your problem is...
Was This Post Helpful? 0
  • +
  • -

#3 dave_mark  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 15-March 13

Re: sql - You have written a subquery that can return more than one field

Posted 02 July 2013 - 06:31 PM

i want only to filter the Distinct Software Title (SW_Title) that has the latest SOftware ApproveDate-(SW_AppDate) and display the data in excel which include the
SW_Lang, SW_App, SW_Programmer, SW_ControlNo which is equal to the specified SW_Section.
Was This Post Helpful? 0
  • +
  • -

#4 dave_mark  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 15-March 13

Re: sql - You have written a subquery that can return more than one field

Posted 02 July 2013 - 06:40 PM

here is my closest query.. but still got an error sql = "Select Distinct SW_Title, SW_Lang, SW_App, SW_Programmer, SW_ControlNo, SW_Section,Max(SW_AppDate) where SW_Section = '" & section & "'"

Syntax error (missing operator) in query expression 'Max(SW_AppDate) where SW_Section = 'ITD''.

pls help...
Was This Post Helpful? 0
  • +
  • -

#5 nandureddy  Icon User is offline

  • D.I.C Head

Reputation: 28
  • View blog
  • Posts: 129
  • Joined: 31-January 11

Re: sql - You have written a subquery that can return more than one field

Posted 02 July 2013 - 08:42 PM

View Postdave_mark, on 02 July 2013 - 06:40 PM, said:

here is my closest query.. but still got an error sql = "Select Distinct SW_Title, SW_Lang, SW_App, SW_Programmer, SW_ControlNo, SW_Section,Max(SW_AppDate) where SW_Section = '" & section & "'"

Syntax error (missing operator) in query expression 'Max(SW_AppDate) where SW_Section = 'ITD''.

pls help...


You may need to use the 'Group By' when using the MAX().
Check out W3 schools Group by.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1