4 Replies - 796 Views - Last Post: 16 January 2013 - 11:45 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 confusion

Posted 12 January 2013 - 11:18 PM

Hi all !
I am using Vb6 ! I have one datagrid named "Datagrid1" and i display certain contents such as subjectname, subjectcode, theory_practical from the table named "subjectcode" in Datagrid1 from access database. And i have another table named "feedetail". My doubt is, if the "theory_practical" value is theory means, then it should display the theoryfee from the table named feedetail or if "theroy_practical" value is practical means, then it should display practical fee in the new column named "Fee" in datagrid1.... i am having confusion with the sql statement and displaying in datagrid ! here is my code that i used !
Public con As New ADODB.Connection
Public rs As New ADODB.Recordset
Public rs2 As New ADODB.Recordset
Private Sub Command1_Click()
Dim semesternew As String
semesternew = semester.Caption
Select Case semesternew
Case "I"
semester1 = 1
Case "II"
semester1 = 2
Case "III"
semester1 = 3
Case "IV"
semester1 = 4
Case "V"
semester1 = 5
Case "VI"
semester1 = 6
End Select
DataGrid1.ClearFields
rs.Open "select Subjectcode,Subjectname,Theory_Practical from subjectcode as s where s.Degree='" & Degree & "' and s.Branch='" & course & "' and s.Year1='" & year1 & "' and s.Year2='" & year2 & "' and s.Semester='" & semester1 & "'  ", con, 1, 3
Set DataGrid1.DataSource = rs
End Sub
Private Sub Command2_Click()
examfee2.Hide
examfee1.Show
End Sub

Private Sub Command4_Click()
If rs!Theory_Practical = "theory" Then
rs2.Open "select Theoryfee from Degreelevel", con, 1, 3
Set DataGrid2.DataSource = rs2
ElseIf rs!Theory_Practical = "practical" Then
rs2.Open "select Practicalfee from Degreelevel", con, 1, 3
Set DataGrid2.DataSource = rs2
End If
End Sub

Private Sub Form_Load()
Set con = New ADODB.Connection
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=.\college.mdb;Persist Security Info=False"
con.CursorLocation = adUseClient
Set rs = New ADODB.Recordset
End Sub


I want to display the corresponding fee in the next column to the Theory_Practical heading ! I can't attach a screenshot file n it shows error ! so here is the link of the screenshot file ! http://tinypic.com/r/2gwtdaq/6 Thanks in advance !

Is This A Good Question/Topic? 0
  • +

Replies To: VB6 SQL Statement confusion

#2 anandrosan1  Icon User is offline

  • New D.I.C Head

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

Re: VB6 SQL Statement confusion

Posted 14 January 2013 - 03:31 AM

What sql statement suits more for my above problem?
Was This Post Helpful? 0
  • +
  • -

#3 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 572
  • View blog
  • Posts: 2,985
  • Joined: 19-May 09

Re: VB6 SQL Statement confusion

Posted 14 January 2013 - 06:09 PM

Looks fine to me...
Was This Post Helpful? 0
  • +
  • -

#4 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Re: VB6 SQL Statement confusion

Posted 16 January 2013 - 03:23 AM

seems you want to add another column in that grid,

if i am right we need to know more details for that tables you used(Like Structure and primary key and so on .. ) other wise is is too hard to get the desired result

if i am wrong please explain correctly what you want?
Was This Post Helpful? 1
  • +
  • -

#5 anandrosan1  Icon User is offline

  • New D.I.C Head

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

Re: VB6 SQL Statement confusion

Posted 16 January 2013 - 11:45 AM

View Postthava, on 16 January 2013 - 03:53 PM, said:

seems you want to add another column in that grid,

if i am right we need to know more details for that tables you used(Like Structure and primary key and so on .. ) other wise is is too hard to get the desired result

if i am wrong please explain correctly what you want?

Thanks thava for the reply ! Actually i solved the problem and i don't know how to close the topic ! and now i give the code that how i solved this problem !

rs.Open "SELECT subjectcode.Year1, subjectcode.Year2, subjectcode.Subjectcode, subjectcode.Subjectname, subjectcode.Theory_Practical, q.Fee FROM subjectcode 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


Was This Post Helpful? 1
  • +
  • -

Page 1 of 1