PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
cmd.Parameters.AddWithValue question-VBForums
Results 1 to 4 of 4

Thread: cmd.Parameters.AddWithValue question

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Apr 2016
    Posts
    30

    cmd.Parameters.AddWithValue question

    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:

    Code:
            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

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,599

    Re: cmd.Parameters.AddWithValue question

    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:
    sql Code:
    1. DECLARE @reportYear INT = YEAR(getdate()) + 1
    2. DECLARE @YEAR DATE = concat('08-01-', @reportYear)
    3.  
    4. UPDATE Last_Update_table
    5. SET UCR = @YEAR
    Of course, there's no need for that @Year variable:
    sql Code:
    1. DECLARE @reportYear INT = YEAR(getdate()) + 1
    2.  
    3. UPDATE Last_Update_table
    4. SET UCR = concat('08-01-', @reportYear)
    and, of course, there's no need for the @reportYear variable either:
    sql Code:
    1. UPDATE Last_Update_table
    2. SET UCR = concat('08-01-', YEAR(getdate()) + 1)

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Apr 2016
    Posts
    30

    Re: cmd.Parameters.AddWithValue question

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

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,599

    Re: cmd.Parameters.AddWithValue question

    Quote Originally Posted by K3JAE View Post
    I overthought this badly!!
    At least it's better to overthink than to underthink.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width