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

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.

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.
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