我正在尝试一次添加和员工以及他们的工作。 我有一个 ID 表，我正在尝试将两条记录插入其中。 我有一个"查找"按钮，可以在另一个填充了作业的表单上打开一个组合框。 做出选择后，它将填充第一个窗体上的 txtJobName 文本框。 它像它应该的那样填充作业名称，但我无法拉取 SelectedIndex 以插入到 TJobEmployees 表中。
'Variables
Dim strSelect As String
Dim strInsert As String
Dim strFirstName As String = ""
Dim strLastName As String = ""
Dim strJob As String = frmSelectJobName.strSelectedJob
Dim intJobID As Integer
Dim cmdSelect As OleDb.OleDbCommand
Dim cmdInsert As OleDb.OleDbCommand
Dim dr As OleDb.OleDbDataReader
Dim intNextHighestRecordID As Integer
Dim intRowsAffected As Integer
Dim result As DialogResult
strFirstName = txtFirstName.Text
strLastName = txtLastName.Text
strJob = txtJobName.Text
intJobID = CInt(frmSelectJobName.lstJobs.SelectedValue)
If Validation() = True Then
If OpenDatabaseConnectionSQLServer() = False Then
' No, warn the user ...
MessageBox.Show(Me, "Database connection error." & vbNewLine &
"The application will now close.",
Me.Text + " Error",
MessageBoxButtons.OK, MessageBoxIcon.Error)
' and close the form/application
Me.Close()
End If
' always ask before adding!!!!
result = MessageBox.Show("Are you sure you want to Add Employee: Job-" & txtJobName.Text & "?", "Confirm Submission", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question)
' this will figure out which button was selected. Cancel and No does nothing, Yes will allow insert
Select Case result
Case DialogResult.Cancel
MessageBox.Show("Action Canceled")
Case DialogResult.No
MessageBox.Show("Action Canceled")
Case DialogResult.Yes
strSelect = "SELECT MAX(intEmployeeID) + 1 AS intNextHighestRecordID " &
" FROM TEmployees"
' Execute command
cmdSelect = New OleDb.OleDbCommand(strSelect, m_conAdministrator)
dr = cmdSelect.ExecuteReader
' Read result( highest ID )
dr.Read()
' Null? (empty table)
If dr.IsDBNull(0) = True Then
' Yes, start numbering at 1
intNextHighestRecordID = 1
Else
' No, get the next highest ID
intNextHighestRecordID = CInt(dr.Item(0))
End If
' add the child record
strInsert = "Insert into TEmployees (intEmployeeID, strFirstName, strLastName)" &
" Values (" & intNextHighestRecordID & ",'" & strFirstName & "'," & "'" & strLastName & "')"
cmdInsert = New OleDb.OleDbCommand(strInsert, m_conAdministrator)
intRowsAffected = cmdInsert.ExecuteNonQuery()
'add the parent record
strInsert = "Insert into TJobEmployees (intJobID, intEmployeeID)" &
" Values (" & intJobID & ",'" & intNextHighestRecordID & "')"
' Insert the record(s)
cmdInsert = New OleDb.OleDbCommand(strInsert, m_conAdministrator)
intRowsAffected = cmdInsert.ExecuteNonQuery()
If intRowsAffected > 0 Then
MessageBox.Show("Job has been added")
Me.Close()
End If
End Select
CloseDatabaseConnection()
Form1_Load(sender, e)
End If
你应该对这段代码做很多重构。从这个开始，它将帮助您到达您想去的地方：
- 删除过时的匈牙利符号。
- 让作业选取器窗体公开两个属性："选定作业名称"和"选定作业 ID">
- 为作业选取器表单指定一个更有意义的名称，因为它不会只返回作业名称。
JobPickerForm.
实例 - 化作业选取器窗体的实例：
Using f As New frmSelectJobName - 从属性中获取所选值：例如
selectedJobID = f.SelectedJobID - 询问用户是否要在验证和连接检查之前真正添加员工。
- 您正在使用 SQL Server。使用 SqlClient 命名空间中的对象，而不是 OleDb。
- 将数据库中的员工 ID 字段声明为
IDENTITY字段，这样您就不必使用 hackyMAX(ID) + 1。 - 对查询使用存储过程。当前的方法对SQL注入开放。虽然您仍然可以使用内联 SQL，但我发现 SP 更适合我自己。
- 返回添加到雇员表中的雇员的
SCOPE_IDENTITY。这将为您提供添加的员工的主键值。 - 使用
Using构造实例化所有 SqlClient 对象。在本地声明并打开您的 SqlConnection 到子/函数，而不是其他地方。 - 无论 Form_Load(( 中有什么，都把它放到一个单独的 Sub 中，然后调用该 sub。您正在传递一个
sender，从您的代码片段中，我不知道这段代码在什么事件中。
做这些，如果你的问题仍然存在，用你重构的代码更新你的原始帖子，我会继续通过编辑我的答案来提供帮助。
下面是重构的 JobPickerForm 属性的示例。
Public Class JobPickerForm
Public ReadOnly Property SelectedJobID As Integer = 0
Public ReadOnly Property SelectedJobName As String = String.Empty
Public Sub New()
' This call is required by the designer.
InitializeComponent()
' Add any initialization after the InitializeComponent() call.
' Whatever code you have that loads the JobsListBox.....
End Sub
Private Sub SelectButtonClickEvent(sender As Object, e As EventArgs) Handles SelectButton.Click
_SelectedJobID = CInt(JobsListBox.ValueMember)
_SelectedJobName = JobsListBox.DisplayMember.ToString
End Sub
End Class
下面是使用新的 JobPickerForm 的示例：
Dim selectedJobName As String = String.Empty
Dim selectedJobID As Integer = 0
Using f As New JobPickerForm
f.ShowDialog()
selectedJobID = f.SelectedJobID
selectedJobName = f.SelectedJobName
End Using