New to SQL and having issues with Sytnax (VB.NET)-CodePudding

I am fairly new to SQL and databases in general (in an intro class in college right now) and I have run into a little snag. I am building a paid time off tracker for people in my department (they are like children and can't keep track of their own things with the tools we already have). I currently have working code that when the person inputs the total PTO hours it adds the appropriate record to my database, but when trying to edit that record I keep getting a syntax error.

I am trying to reduce/update the database entry based off what is calculated into two labels that makes it easier to read in the application. I have reviewed multiple YouTube videos and threads that still haven't helped (hence why I am here). Below is the code I currently have inside the click event for the "Submit PTO" button:

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

Any help on this would be greatly appreciated. It is probably something stupid easy, but I apparently can't find it to save my life.

CodePudding user response：

Don't try to do arithmetic with Strings. Text properties contain Strings.

You change the value of Protected after you update the database. It is silly to update that field if you don't change it.

I am going to assume that you want to update the record of a particular employee. I have added a text box for an employee ID that is an Integer. I also guessed that there is a field in the database that has for the Id. I called it EmployeeID.

Connections and Commands need to be disposed to release unmanaged code. Using...End Using blocks handle this for us even if there is an error. To do this connections need to be declared in the Using in the method where they are used, not as class level variables.

Never concatenate strings with values to be entered in a database. This can lead to sql injection and damage your database. Always use parameters whose values are not considered as executable code by the database. I had to guess at the datatypes for the parameters. Check your database for actual types.

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