如何根据vb6中Datagrid中的其他列显示来自访问的相应记录 [英] how to display corresponding record from access based on other column in Datagrid in vb6

问题描述

我正在使用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中的其他列显示来自访问的相应记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！