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

Question

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

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

我的表架构：

| cid | cpos........| cfname | cmname | clname | cyr | cparty | | 1..| president. | john....| ark....|..smith.| 3 ..| glory..|

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 and 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作为组合框的DataSource来分隔数据。

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