mysql - vb.net 将具有不同记录的单个数据库表中的数据库记录显示到不同的组合框

Question

vb.net 将具有不同记录的单个数据库表中的数据库记录显示到不同的组合框

我有两个组合框，我想在每个组合框中显示不同的记录，而我只使用数据库中的一张表，此代码显示错误，嗯

我的表架构:

|西德| cps........| cf名称 |管理员名称 | clname |西尔|聚会 |
| 1..|总统。 |约翰...|方舟....|..史密斯.| 3..|荣耀..|

Private Sub Form4_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    con.ConnectionString = ("server=localhost;user id=root;database=db")
    Try
        con.Open()

        With cmd
            .Connection = con
            .CommandText = "SELECT CONCAT_WS(' ', cfname, cmname, clname,'from', cparty WHERE cpos like='President') AS fullname from candidate;"
        End With
        Dim dt As New DataTable

        With ComboBox1
            da.SelectCommand = cmd
            da.Fill(dt)
            .DataSource = dt
            .DisplayMember = "fullname"
            .ValueMember = "fullname"
        End With


        With cmd
            .Connection = con
            .CommandText = "SELECT CONCAT_WS(' ', cfname, cmname, clname,'from', cparty WHERE cpos like='Vice President') AS fullname from candidate;"
        End With
        With ComboBox2
            da.SelectCommand = cmd
            da.Fill(dt)
            .DataSource = dt
            .DisplayMember = "fullname"
            .ValueMember = "fullname"
        End With


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

Answer 1

看看你之前的问题，我认为你的命令文本应该是这样的

.Connection = con
.CommandText = "SELECT CONCAT_WS(' ', cfname, cmname, clname, cparty) as fullname " & _
               "from candidate WHERE cpos like='Vice President'"

这会将四列 cfname、cmname、clname 和 cparty 中的信息合并到名为 fullname 的单个列中，然后字段 cpos 上的 where 条件将提取“副总统”或“总裁的角色。

我还建议更改组合框上属性设置的顺序

 .DisplayMember = "fullname"
 .ValueMember = "fullname"
 .DataSource = dt

您还可以通过这种方式避免执行两个查询来从数据库中检索相同的信息

 With cmd
    .Connection = con

    ' If you have only President and Vice President in the candidate table 
    ' you could remove the where clause 
    .CommandText = "SELECT CONCAT_WS(' ', cfname, cmname, clname, cparty) as fullname, " & _
                   "cpos, cid from candidate WHERE cpos like='Vice President' OR " & _
                   "cpos like 'President'"
 End With

然后使用 DataView 作为 Combobox 的数据源来分离数据

da.SelectCommand = cmd
da.Fill(dt)
With ComboBox1
   Dim dv = new DataView(dt, "cpos like 'President'", "", DataViewRowState.CurrentRows)
   .DisplayMember = "fullname"
   .ValueMember = "fullname"
   .DataSource = dv
End With
With ComboBox2
   Dim dv1 = new DataView(dt, "cpos like 'Vice President'", "", DataViewRowState.CurrentRows)
   .DisplayMember = "fullname"
   .ValueMember = "fullname"
   .DataSource = dv1
End With