總的來說，我對 SQL 和資料庫相當陌生（現在在大學的介紹課上），我遇到了一些障礙。我正在為我部門的人員構建一個帶薪休假跟蹤器（他們就像孩子一樣，無法使用我們已有的工具跟蹤自己的事情）。我目前有作業代碼，當該人輸入 PTO 總小時數時，它會將適當的記錄添加到我的資料庫中，但是在嘗試編輯該記錄時，我不斷收到語法錯誤。
我試圖根據計算到兩個標簽中的內容來減少/更新資料庫條目，以便在應用程式中更容易閱讀。我已經查看了多個 YouTube 視頻和執行緒，但仍然沒有幫助（這就是我在這里的原因）。下面是我目前在“提交 PTO”按鈕的點擊事件中的代碼：
Private Sub btnSubmit_Click(sender As Object, e As EventArgs) Handles btnSubmit.Click
'Delcare variables for the duration of PTO taken.
Dim startTime As DateTime
Dim endTime As DateTime
Dim duration As TimeSpan
'Declare double variables for Bank and Protected time.
Dim dblBank As Double = lblBank.Text
Dim dblProtected As Double = lblProtected.Text
'Ensure there is a value selected in both combo boxes or display a message box. If times are entered correctly then process the calcuations to reduce each PTO bank accordingly based off the user input.
If cboStart.Text = "" And cboEnd.Text = "" Then
MessageBox.Show("You must select a start and end time.", "Service Delivery PTO Tracker", MessageBoxButtons.OK, MessageBoxIcon.Error)
Else
'Assign the start/end combobox selection to the start/end variables And duration is equal to the difference of the selected times.
startTime = cboStart.SelectedItem
endTime = cboEnd.SelectedItem
duration = endTime - startTime
'Display the appropriate hours in the designated labels.
lblHours.Text = duration.TotalHours
lblBank.Text -= lblHours.Text
'Update the values of the PTO Bank and Protected time into the database, show a messagebox that the PTO hours have been successfully updated.
con.Open()
Dim command As New SqlCommand("UPDATE TimeBank SET Bank = '" & dblBank & "', Protected = '" & dblProtected & "')", con)
command.ExecuteNonQuery()
MessageBox.Show("PTO Updated Successfully!")
con.Close()
'Update the datagridview in real time as the PTO is input (this is for developing only right now).
LoadDataGrid()
'If the radio button for Protected Time is selected then reduce both bank and protected labels based off the PTO selection.
If radProtected.Checked Then
lblProtected.Text -= lblHours.Text
lblBank.Text -= lblHours.Text
End If
End If
'Reset the radio button for Protected Time once the PTO selection is complete.
radProtected.Checked = False
End Sub
對此的任何幫助將不勝感激。這可能是一件愚蠢的事情，但我顯然無法找到它來挽救我的生命。
uj5u.com熱心網友回復：
不要嘗試用Strings進行算術運算。Text屬性包含Strings。
在更新資料庫后更改 Protected 的值。如果您不更改該欄位，則更新該欄位是愚蠢的。
我將假設您要更新特定員工的記錄。我為員工 ID 添加了一個文本框，該 ID 是Integer. 我還猜測資料庫中有一個欄位用于Id。我叫它EmployeeID。
Connections 和Commands 需要處理以釋放非托管代碼。Using...End Using即使出現錯誤，塊也會為我們處理這個問題。為此，連接需要在Using使用它們的方法中宣告，而不是作為類級變數。
切勿將字串與要輸入到資料庫中的值連接起來。這可能會導致 sql 注入并損壞您的資料庫。始終使用其值不被資料庫視為可執行代碼的引數。我不得不猜測引數的資料型別。檢查您的資料庫以獲取實際型別。
Private Sub btnSubmit_Click(sender As Object, e As EventArgs) Handles btnSubmit.Click
'Declare double variables for Bank and Protected time.
Dim dblBank As Double = CDbl(lblBank.Text)
Dim dblProtected As Double = CDbl(lblProtected.Text)
Dim intEmployeeID = CInt(txtEmployee.Text)
'Ensure there is a value selected in both combo boxes or display a message box. If times are entered correctly then process the calcuations to reduce each PTO bank accordingly based off the user input.
If cboStart.Text = "" OrElse cboEnd.Text = "" Then
MessageBox.Show("You must select a start and end time.", "Service Delivery PTO Tracker", MessageBoxButtons.OK, MessageBoxIcon.Error)
Else
'Assign the start/end combobox selection to the start/end variables And duration is equal to the difference of the selected times.
Dim startTime = CDate(cboStart.SelectedItem)
Dim endTime = CDate(cboEnd.SelectedItem)
Dim duration = endTime - startTime
'Display the appropriate hours in the designated labels.
Dim dblHours = duration.TotalHours
lblHours.Text = dblHours.ToString
Dim dblBankBalance = dblBank - duration.TotalHours
lblBank.Text = dblBandBalance.ToString
'If the radio button for Protected Time is selected then reduce both bank and protected labels based off the PTO selection.
If radProtected.Checked Then
Dim dblProtectedBalance = dblProtected - dblHours
lblProtected.Text = dblProtectedBalance.ToString
'You already did the subtraction above
'lblBank.Text -= lblHours.Text
End If
'Update the values of the PTO Bank and Protected time into the database, show a messagebox that the PTO hours have been successfully updated.
UpdateDatabase()
'Update the datagridview in real time as the PTO is input (this is for developing only right now).
LoadDataGrid()
End If
MessageBox.Show("PTO Updated Successfully!")
'Reset the radio button for Protected Time once the PTO selection is complete.
radProtected.Checked = False
End Sub
Private ConStr As String = "Your connection string"
Private Sub UpdateDatabase(Bank As Double, Protect As Double, EmpID As Integer)
Using con As New SqlConnection(ConStr),
command As New SqlCommand("UPDATE TimeBank SET Bank = @Bank, Protected = @Protect Where EmployeeID = @ID;", con)
command.Parameters.Add("@Bank", SqlDbType.Float).Value = Bank
command.Parameters.Add("@Protect", SqlDbType.Float).Value = Protect
command.Parameters.Add("@ID", SqlDbType.Int).Value = EmpID
con.Open()
command.ExecuteNonQuery()
End Using
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/326996.html