Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder
    Join Date
    Mar 2003
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question comparing variable to a record set & updating

    Hi , there is a table that has 20 columns, each column caould contain a promotion code. I have a form that a user fills out that contains a promo code field. When the user submit the form i'm comparing the promo field from the form to the values in the promo columns in the database and if a match then i'm setting the match value in the databse to nothing so the promo code cannot be used again.

    My problem is trying to compare the form field value to the 20 column values in an efficient way.... below is my thinking which i think will turn out to be very inefficient:

    If reference_code <> "" Then 'reference_code is the form field request value

    Set c3B = server.createobject("ADODB.connection")

    c3B.Open "Provider=sqloledb;" & _ ...........
    .......

    sq6 = "SELECT promo1, promo2, promo3, promo4, promo5, promo6, promo7, promo8, promo9, promo10, promo11, promo12, promo13, promo14, promo15, promo16, promo17, promo18, promo19, promo20, status FROM promo WHERE status = 'request_pending'"

    Set r68 = c3B.Execute(sq6)

    If r68.EOF Then
    ' do nothing so no error is displayed
    End If

    If reference_code = r68("promo1") Then
    sq7 = "UPDATE promo SET promo1 = ' ' WHERE promo1 = '"&r68("promo1")&"'"
    Set r69 = c3B.Execute(sq7)
    response.redirect "promo.asp?type=true"
    End If

    If reference_code = r68("promo2") Then
    sq7 = "UPDATE promo SET promo2 = ' ' WHERE promo2 = '"&r68("promo2")&"'"
    Set r69 = c3B.Execute(sq7)
    response.redirect "promo.asp?type=true"
    End If

    ... and so on until r68("promo20")


    // THANKS

  • #2
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    11,037
    Thanks
    0
    Thanked 250 Times in 246 Posts
    The Execute method of the Connection object has an optional parameter that returns the number of records affected by the query executed. You don't need to do a SELECT query, just UPDATE the table and check if that parameter is greater than zero or not. If no records are updated, then no problem, nothing is changed in the table.

    Code:
    dim count, sql, c3B
    ...
    
    count = 0
    
    sql = "UPDATE promo SET promo1='' WHERE promo1='" & reference_code & "' AND status = 'request_pending'"
    c3B.Execute(sql, count)
    if count>0 then 
      response.redirect "promo.asp?type=true"
      response.end
    enf if
    
    sql = "UPDATE promo SET promo2='' WHERE promo2='" & reference_code & "' AND status = 'request_pending'"
    c3B.Execute(sql, count)
    if count>0 then 
      response.redirect "promo.asp?type=true"
      response.end
    enf if
    
    '...and so on
    You can even just loop it so that you can update all the promo codes.
    Code:
    dim count, sql, c3B, i
    ...
    count = 0
    for i = 1 to 20
      sql = "UPDATE promo SET promo" & i & "='' WHERE promo" & i & "='" & reference_code & "' AND status = 'request_pending'"
      c3B.Execute(sql, count)
      if count>0 then 
        response.redirect "promo.asp?type=true"
        response.end
      enf if
    next


  •  

    Posting Permissions

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