3 Replies - 697 Views - Last Post: 18 January 2013 - 08:18 AM Rate Topic: -----

#1 anandrosan1  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 23
  • Joined: 20-December 12

VB6 SQL Statement error

Posted 18 January 2013 - 06:00 AM

I am having recordset named "rs" and and these recordset are placed in a command button named "Display" and the link for the screentshot of the image are give below.Here is the code for that rs
rs.Open "SELECT subjectcode.Year1, subjectcode.Year2, subjectcode.Subjectcode, subjectcode.Subjectname, subjectcode.Theory_Practical FROM subjectcode where subjectcode.Degree='" & Degree & "' and subjectcode.Branch='" & course & "' and subjectcode.Year1='" & year1 & "' and subjectcode.Year2='" & year2 & "' and subjectcode.Semester='" & semester1 & "' , q.Fee INNER JOIN ( SELECT fees.Year1, fees.Year2, 'Theory' As FeeType, fees.Theoryfee As Fee FROM fees UNION ALL SELECT fees.Year1, fees.Year2, 'Practical' As FeeType, fees.Practicalfee As Fee FROM fees)  AS q ON (subjectcode.Theory_Practical = q.FeeType) AND (subjectcode.Year2 = q.Year2) AND (subjectcode.Year1 = q.Year1)", con, 1, 3



It shows "missing operator" error and i think the error is near "q.Fee" and i don't know how to use that in that place and so i give the heading and sample value of those above tables below (Note : In the above where condition in rs statement contains values as degree,course like that and these are the labels name and you can see that in the screentshot here http://tinypic.com/v...pic=2gwtdaq&s=6 )

Subjectcode table :
Heading(Degree,Branch,Year1,Year2,Semester,Subjectcode,Subjectname,Theory_Practical,Major_Allied_Elective) values (Bsc,computer science,2001,2003,2,RCCS10CS1,C language,Theory,Major)

Fees table :
Heading(Year1,Year2,Theoryfee,Practicalfee) values (2001,2003,440,320)

I don't where to place that where condition and q.Fee in a correct order and please help me with this ! Thanks in advance !

Is This A Good Question/Topic? 0
  • +

Replies To: VB6 SQL Statement error

#2 andrewsw  Icon User is online

  • It's just been revoked!
  • member icon

Reputation: 3720
  • View blog
  • Posts: 13,007
  • Joined: 12-December 12

Re: VB6 SQL Statement error

Posted 18 January 2013 - 06:50 AM

The WHERE clause should occur after ON. A simple example:

SELECT tblStaff.FirstName, tblStaff.Surname, tblOffices.OfficeName
FROM tblOffices INNER JOIN tblStaff ON tblOffices.OfficeID = tblStaff.Office
WHERE (tblOffices.OfficeName="London");


Hint1: Get it working with just a couple of fields, then it's easy to just add more fields.

Hint2: Ignore the aliases (AS) where possible initially; again, they can be added later.

This post has been edited by andrewsw: 18 January 2013 - 06:54 AM

Was This Post Helpful? 1
  • +
  • -

#3 anandrosan1  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 23
  • Joined: 20-December 12

Re: VB6 SQL Statement error

Posted 18 January 2013 - 07:46 AM

View Postandrewsw, on 18 January 2013 - 07:20 PM, said:

The WHERE clause should occur after ON. A simple example:

SELECT tblStaff.FirstName, tblStaff.Surname, tblOffices.OfficeName
FROM tblOffices INNER JOIN tblStaff ON tblOffices.OfficeID = tblStaff.Office
WHERE (tblOffices.OfficeName="London");


Hint1: Get it working with just a couple of fields, then it's easy to just add more fields.

Hint2: Ignore the aliases (AS) where possible initially; again, they can be added later.

Andrew Friend it really worked. Don't know how to thank you. Really really thank u so much. And another thanks for your clear hint and that helped me alot ! Again Thank you so much !
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is online

  • It's just been revoked!
  • member icon

Reputation: 3720
  • View blog
  • Posts: 13,007
  • Joined: 12-December 12

Re: VB6 SQL Statement error

Posted 18 January 2013 - 08:18 AM

Quote

Andrew Friend it really worked. Don't know how to thank you. Really really thank u so much. And another thanks for your clear hint and that helped me alot ! Again Thank you so much !


You are quite welcome!
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1