从表单中的选定值中选择填充DB表中的一列

Question

我已经成功地将数据从我的vb.net表单字段提交到具有自动递增唯一ID的SQL表中。我的问题是，我有多个表单字段具有相同的字段名。例如，我将在我的数据库中的字段接受数据从TextBox1, TextBox2, TextBox3或TextBox4在我的表单。这可能吗?

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
    Using message As New MailMessage()
        Dim iReturn As Boolean
        Using con As New SqlConnection("Server=SGI-NOT-TTID01;Database=Commission;uid=****;pwd=******")
            Using com As New SqlCommand()
                With com
                    .CommandText = "INSERT INTO InputTable VALUES (@UniqueRequestNumber, @BusinessUnit, @ReasonCode, @Originator, @OnBehalfofName, @OnBehalfofEmail, @SalesTeam_CS, @SalesTeam_MISDBM, @DateEffectiveFrom, @DateEffectiveTo, @CustomerClientName, @CustomerAccNum, @MovingTo, @JustificationComments, @NewBusOrRenewal, @OthersImpacted, @ReasonCode2, @Target, @TargetPeriod, @ProposedScheme, @NewOTC, @ExistingRole, @Amount, @CustomerImapcted, @CurrentOTC, @PoundsInPocketEst, @NewRole ) "
                    .Connection = con
                    .CommandType = CommandType.StoredProcedure
                    .CommandText = "AddUniqueRequestNumberWithOutput"
                    .Parameters.AddWithValue("@UniqueRequestNumber", ParameterDirection.Output)
                    .Parameters.AddWithValue("@BusinessUnit", ComboBox1.Text)
                    .Parameters.AddWithValue("@ReasonCode", ComboBox4.Text)
                    .Parameters.AddWithValue("@Originator", TextBox1.Text)
                    .Parameters.AddWithValue("@OnBehalfofName", TextBox2.Text)
                    .Parameters.AddWithValue("@OnBehalfofEmail", TextBox3.Text)
                    .Parameters.AddWithValue("@SalesTeam_CS", ComboBox2.Text)
                    .Parameters.AddWithValue("@SalesTeam_MISDBM", ComboBox3.Text)
                    .Parameters.AddWithValue("@DateEffectiveFrom", DateTimePicker1.Text)
                    .Parameters.AddWithValue("@DateEffectiveTo", DateTimePicker2.Text)
                    .Parameters.AddWithValue("@CustomerClientName", TextBox4.Text)
                    .Parameters.AddWithValue("@CustomerAccNum", TextBox5.Text)
                    .Parameters.AddWithValue("@MovingTo", TextBox6.Text)
                    .Parameters.AddWithValue("@JustificationComments", TextBox7.Text)
                    .Parameters.AddWithValue("@NewBusOrRenewal", ComboBox6.Text)
                    .Parameters.AddWithValue("@OthersImpacted", TextBox13.Text)
                    .Parameters.AddWithValue("@ReasonCode2", ComboBox5.Text)
                    .Parameters.AddWithValue("@Target", TextBox5.Text)
                    .Parameters.AddWithValue("@TargetPeriod", ComboBox7.Text)
                    .Parameters.AddWithValue("@ProposedScheme", TextBox24.Text)
                    .Parameters.AddWithValue("@NewOTC", TextBox17.Text)
                    .Parameters.AddWithValue("@ExistingRole", TextBox19.Text)
                    .Parameters.AddWithValue("@Amount", TextBox8.Text)
                    .Parameters.AddWithValue("@CustomerImpacted", TextBox12.Text)
                    .Parameters.AddWithValue("@CurrentOTC", TextBox16.Text)
                    .Parameters.AddWithValue("@PoundsInPocketEst", TextBox10.Text)
                    .Parameters.AddWithValue("@NewRole", TextBox20.Text)
                End With
                Try
                    con.Open()
                    com.ExecuteNonQuery()
                    iReturn = True
                Catch ex As SqlException
                    MsgBox(ex.Message.ToString)
                    iReturn = False
                Finally
                    con.Close()
                End Try
                    'set to the from, to and subject fields
                    message.From = (New MailAddress(TextBox3.Text.ToString()))
                    message.[To].Add(New MailAddress("[e-mail]"))
                    message.Subject = "Request Number"
                    If Not attachmentList Is Nothing Then
                    For Each attachment As System.Net.Mail.Attachment In attachmentList
                            message.Attachments.Add(attachment)
                        Next
                    End If

                    'display submitted box
               MessageBox.Show("Your request has been submitted!", "Congratulations!")
                    'close form
                    Me.Close()
                    Dim client As New SmtpClient()
                    client.Host = "mailhost"
                    client.Send(message)



            End Using
        End Using

    End Using
    Return
End Sub

所以这个函数背后的逻辑如下:

1. 向SQL Server中的表提交值。
2. 发送电子邮件(可选择带附件)。

Answer 1

您可以编写一个私有函数来搜索文本框并返回第一个文本框的内容。

Private Function SearchBehalfText() As String
    Dim behalfOf = TextBox3.Text.Trim
    if behalfOf.Length = 0 then 'textbox3 is empty, check the next one '
        behalfOf = TextBox4.Text.Trim
        if behalfOf.Length = 0 then  'textbox4 is empty, check the next one '
             ...... mode test here if you have more textboxes, stop when all the textboxes are verified for empty
        End If
    End If
    return behalfOf
End Function

现在创建参数的代码可以写成

.Parameters.AddWithValue("@OnBehalfofEmail", SearchBehalfText())

我觉得这个场景有点弱。使用此信息无法恢复适当的文本框