问题描述
我正在使用Vb6!我有一个名为Datagrid1的datagrid,我从Access数据库中的Datagrid1中的subjectcode表中显示主题名,主题代码,理论对象等内容。另有一个名为feedetail的表。我的怀疑是,如果理论上的理论价值是理论手段,那么它应该从表中显示理论依据,或者说实际价值是实际的手段,那么应该在新的费用一栏中显示实际费用datagrid1。
我和sql语句混淆,并在datagrid中显示!这是我使用的代码
我想在Theory_Practical标题的下一列中显示相应的费用!我无法附加屏幕截图文件,它显示错误! !提前致谢 !
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
选择案例semesternew
案例I
semester1 = 1
案例II
semester1 = 2
案例III
semester1 = 3
案例IV
semester1 = 4
案例V
semester1 = 5
案例VI
semester1 = 6
结束选择
DataGrid1.ClearFields
rs.Open select subjectcode,Subjectname,Theory_Practical from subjectcode as s where s.Degree ='&学位和'和s.Branch ='&课程& '和s.Year1 ='&年1岁'和s.Year2 ='& year2& '和s.Semester ='&第1学期',con,1,3
设置DataGrid1.DataSource = rs
End Sub
Private Sub Command2_Click()
examfee2.Hide
examfee1 。显示
End Sub
Private Sub Command4_Click()
如果rs!Theory_Practical =theory然后
rs2.Open从Degreelevel选择Theoryfee,con, 1,3
设置DataGrid2.DataSource = rs2
ElseIf rs!Theory_Practical =实际然后
rs2.Open从Degreelevel选择Practicalfee,con,1,3
设置DataGrid2.DataSource = rs2
End If
End Sub
Private Sub Form_Load()
Set con = New ADODB.Connection
con.OpenProvider = Microsoft.Jet.OLEDB.4.0; Data Source = .\college.mdb; Persist Security Info = False
con.CursorLocation = adUseClient
设置rs =新建ADODB.Recordset
结束Sub
费用表:
标题(Year1,Year2,Theoryfee,Practicalfee)
值(2001,2003,440,320)
所有其他值只有不同的值!
主题表:
标题(Year1,Year2,Subjectcode,
值(2001,2003,RCCS10CS1,C编程,理论)
您可以使用如下查询:
SELECT subjectcode.Year1,subjectcode.Year2 ,
subjectcode.Subjectcode,subjectcode.Subjectname,
subjectcode.Theory_Practical,q.fee
FROM subjectcode
INNER JOIN(
SELECT费用.Year1,费用.Year2, 理论作为费用类型,
费用。理论费用
从费用
UNION全部
选择费用。年1,费用.Year2,实际作为费用类型,
费用
从费用)$ q
ON(subjectcodeTheory_Practical = q.FeeType)
AND(subjectcode.Year2 = q.Year2)
AND(subjectcode。 Year1 = q.Year1)
然而,你会很多重新设计您的费用表以匹配内部sql返回的数据,即理论和实际费用的不同之处:
Year1 Year2 FeeType Fee
2001 2003理论440
2001 2003实用320
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!
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! Thanks in advance !
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
Fee table:
Heading(Year1,Year2,Theoryfee,Practicalfee)
values (2001,2003,440,320)
All other values like this only with different values !
subjectcode table :
Heading(Year1,Year2,Subjectcode,Subjectname,Theory_Practical)
values (2001,2003,RCCS10CS1,C programming, Theory)
You can use a query like so:
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)
However, you would be much better off redesigning your fees table to match the data returned by the inner sql, that is, a different line for theory and practical fees:
Year1 Year2 FeeType Fee
2001 2003 Theory 440
2001 2003 Practical 320
这篇关于如何根据vb6中Datagrid中的其他列显示来自访问的相应记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!