时间戳

首页 > 解决方案 > 从 2 个表中填充 Listview

sql - 从 2 个表中填充 Listview

问题描述

我有 2 张桌子：tblstudentbasicinfo和tblzipcode.

当学生输入信息时，用户可以输入学区名称和该学区的邮政编码，根据tblzipcode.

我的问题是当我尝试填充列表视图以使用列显示学生基本信息时

数据不会显示。

这是我尝试过的 sql 查询：

Dim sql As String = "Select TagID,Surname,Firstname,Middlename,Course,Year,ZipCode,Province,Municipality,District,Contactno from tblstudentbasicinfo AS t1 Inner Join tblzipcode AS t2 On t1.ZipCode = t2.ZipCode and t1.District = t2.District"

但我得到错误：

字段列表中的“邮政编码”列不明确。

我是 sql 和 vb 的新手。有人可以告诉我如何解决这个问题吗？先感谢您

这是我在 vb 上的代码：

Private Sub Populatelstbasicinfo(TagID As String, Surname As String, Firstname As String, Middlename As String, Course As String, Year As String, ZipCode As String, Province As String, Municipality As String, District As String, contactno As String)
Dim row As String() = New String() {TagID, Surname, Firstname, Middlename, Course, Year, ZipCode, Province, Municipality, District, contactno}


lstbasicinfo.Items.Add(item)
End Sub

Private Sub Retrieve()


    lstbasicinfo.Items.Clear()
    Dim sql As String = "Select TagID,Surname,Firstname,Middlename,Course,Year,t1.ZipCode,Province,Municipality,t2.Barangay,Contactno from tblstudentbasicinfo AS t1 Inner Join tblzipcode AS t2 On t1.ZipCode = t2.ZipCode and t1.Barangay = t2.Barangay"
    cmd = New OdbcCommand(sql, con)

    Try

        con.Open()
        adapter = New OdbcDataAdapter(cmd)
        adapter.Fill(dt)

        For Each row In dt.Rows
            Populatelstbasicinfo(row(0), row(1), row(2), row(3), row(4), row(5), row(6), row(7), row(8), row(9), row(10), row(11), row(12), row(13), row(14), row(15), row(16))
        Next

        con.Close()
        dt.Rows.Clear()
    Catch ex As Exception
        MsgBox(ex.Message)
        con.Close()
    End Try
End Sub

标签： sqlvb.net

解决方案

您应该在编写查询时学习良好的做法。所以，考虑一下：

Select bi.TagID, bi.Surname, bi.Firstname, bi.Middlename, bi.Course, bi.Year,
       z.ZipCode, z.Province, z.Municipality, z.District, bi.Contactno
from tblstudentbasicinfo bi Inner Join
     tblzipcode z
     on bi.ZipCode = z.ZipCode and bi.District = z.District;

笔记：

  • 所有列都是合格的，因此您知道它们来自哪些表。
  • 这些表被赋予了别名，因此查询更易于编写和阅读。
  • 空格和一致的大小写增强了基本的可读性。

推荐阅读