sql - VB.Net/Access SQL插入子查询问题

Question

我正在制作一个可以填写的表单，并将使用按钮执行插入查询。我不知道如何引用这段代码中的另一个表。我尝试在表单中插入姓名、电话号码和电子邮件以及相应的经销商 ID。获取分销商 ID 的唯一方法是引用分销商表。有人有主意吗？谢谢!

Private Sub Button1_Click(sender As Object, e As EventArgs) 处理 Button1.Click

    provider = 0
    dataFile = 0
    provider = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
    dataFile = "XXXXXXX.mdb"
    connString = provider & dataFile
    myConnection.ConnectionString = connString
    myConnection.Open()

    '*THIS IS THE PROBLEM AREA*'
    Dim str As String
    str = "INSERT INTO [Distributor Contact] ([Name], [Phone Number], [Email], [Distributor ID]) VALUES(?,?,?,?) WHERE [Distributor ID] IN (SELECT DISTINCT [Distributor ID] FROM [Distributor] WHERE [Distributor Name]= '" & ComboBox_Dist.SelectedItem.ToString() & "'))"

    Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
    cmd.Parameters.Add(New OleDbParameter("Name", CType(TextBox2.Text, String)))
    cmd.Parameters.Add(New OleDbParameter("Phone Number", CType(TextBox3.Text, String)))
    cmd.Parameters.Add(New OleDbParameter("Email", CType(TextBox4.Text, String)))
    Me.Refresh()

    Try
        cmd.ExecuteNonQuery()
        cmd.Dispose()
        myConnection.Close()
        TextBox2.Clear()
        TextBox3.Clear()
        TextBox4.Clear()

        MsgBox("Contact Added")
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
    myConnection.Close()

    'RE-POPULATE COMBOBOX
    ComboBox_Dist.Items.Clear()
    Dim connString1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= XXXXXXXXX.mdb"
    Dim con As OleDbConnection = New OleDbConnection(connString1)
    Dim adapter As OleDbDataAdapter
    Dim cmd2 As OleDbCommand
    Dim dt As DataTable = New DataTable()

    Dim sql As String = "SELECT * FROM [Distributor Contact];"

    cmd2 = New OleDbCommand(sql, con)

    Try
        con.Open()
        adapter = New OleDbDataAdapter(cmd2)

        adapter.Fill(dt)

        'Add Items To ComboBox
        For Each row In dt.Rows
            ComboBox_Dist.Items.Add(row(1))
        Next
        con.Close()
    Catch ex As Exception
        MsgBox(ex.Message)
        con.Close()
    End Try
End Sub

Answer 1

添加 ComboBox_Dist 作为参数，然后使用 Select 而不是 Values 进行插入查询。该选择允许您引用“分销商”表。

cmd.Parameters.Add(New OleDbParameter("Email", CType(TextBox4.Text, String)))
cmd.Parameters.Add(New OleDbParameter("DistributorName", ComboBox_Dist.SelectedItem.ToString()))

str = "INSERT INTO [Distributor Contact] ([Name], [Phone Number], [Email], [Distributor ID]) "
str += "SELECT ?,?,?, [Distributor].[DistributorID] "
str += "FROM [Distributor] WHERE [Distributor].[Distributor Name] = ?"