cmd.Parameters.AddWithValue question

[K3JAE]

I am wondering how to use the SQL variable as a cmd.Parameters.AddWithValue "value."
In this case the update_table would be written '08-01-2020'

The STATIC value needs to be exactly that. Each year on 08-01-yyyy I need to be reminded to file Federal paperwork. Once the paperwork is filed then I check it off as complete and write the next year's "reminder date" so it is not overlooked again.

I am updating the DB to a new date 1 year in the future which will be used to check against and then kick a reminder up that the date is approaching. That part of the code works fine. I'm not sure how to properly use the @Year value.
The area in question is red emboldened:

        If ccbUCR.Checked = True Then
            Try
                cmd.CommandType = CommandType.Text
                cmd.CommandText = ("DECLARE @reportYear int = YEAR(getdate()) + 1
                                    Declare @Year date = concat('08-01-', @reportYear)

                                    Update Last_Update_table
                                    SET UCR = @ucr")

                cmd.Parameters.AddWithValue("@ucr", "@Year")
                cmd.Connection = con
                con.Open()
                cmd.ExecuteNonQuery()

                MsgBox("UCR Update Successfully Added", MsgBoxStyle.Information, "Add")
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
            cmd.Connection.Close()
        End If

[jmcilhinney]

The clue is in the name: AddWithValue. It's a value. It's not part of the code. If the value you supply is "@Year" then that is exactly the VALUE that will be used. That text won't be inserted into the code first and then interpreted as the variable @Year. Think about how parameters work in VB. Could you write a method and declare a parameter and then call that method and pass a value to that parameter that would magically become part of the code of the method? Of course not. The same god for SQL parameters.

You don't need any parameters at all in that SQL. There's no data going in from your code so there's no need for a parameter. If what you want to do is set UCR to the value of @Year within the SQL then do that:

DECLARE @reportYear int = YEAR(getdate()) + 1
Declare @Year date = concat('08-01-', @reportYear)
 
Update Last_Update_table
SET UCR = @Year

Of course, there's no need for that @Year variable:

DECLARE @reportYear int = YEAR(getdate()) + 1
 
Update Last_Update_table
SET UCR = concat('08-01-', @reportYear)

and, of course, there's no need for the @reportYear variable either:

Update Last_Update_table
SET UCR = concat('08-01-', YEAR(getdate()) + 1)

[K3JAE]

OK, thanks for that clear explanation. That makes perfect sense and as usual, I overthought this badly!! LOL

[jmcilhinney]

I overthought this badly!!

At least it's better to overthink than to underthink.