Brain Teaser

1. Senior Member
Join Date
Dec 1969
Posts
228

## Brain Teaser

I need this code on to do a pattern like Cost11, Cost12, Cost13, Cost14,. but I am getting 11, 12 or 21, 22. Any suggestions?<BR><BR>x=1<BR>y=1<BR>for i=1 to Columns<BR>for r=1 to Rows<BR>abba=("a"& trim(str(x)) & "price")<BR> <BR>myCommand2=New SQLCommand("UPDATE TwoThousandDescriptions set " & abba & " = &#039;"&request.form("Cost"&trim(str(x))&trim(str( y)))&"&#039;", myConnection)<BR><BR><BR>dim blah as string<BR>blah=("UPDATE TwoThousandDescriptions set " & abba & " = &#039;"&request.form("Cost"&trim(str(x))&trim(str( y+1)))&"&#039;")<BR>response.write (blah)<BR><BR>x +=1<BR>y +=1<BR>next<BR>next<BR>

2. Senior Member
Join Date
Dec 1969
Posts
1,849

## Looks like you are doing this too hard...

Rows = 5<BR>cols = 5<BR>for i = 1 to rows<BR> for x = 1 to cols<BR> response.write(i & x & ",")<BR> next<BR>next<BR><BR>This results in this output.<BR>11,12,13,14,15,21,22,23,24,25,31,32,33, 34,35,41,42,43,44,45,51,52,53,54,55,<BR><BR>-- Whol

3. Senior Member
Join Date
Dec 1969
Posts
96,118

## Ughhh

The worst thing about that is that it reveals that you *desparately* need to revamp your database!<BR><BR>Either you have ONE RECORD with a bunch of fields named a1price, a2price, a3price, etc.<BR><BR>Or you aren&#039;t aware that you are updating *ALL* the records in your table every time through the loop.<BR><BR>Consider what happens if Columns is 4 and Rows is 3.<BR><BR>You will end up generating the following queries (where I use the notation [CostXY] to mean "the result of doing Request.Form("CostXY") to make it more readable):<BR><BR>UPDATE TwoThousandDescriptions set a1price = &#039;[Cost12]&#039;<BR>UPDATE TwoThousandDescriptions set a2price = &#039;[Cost23]&#039;<BR>UPDATE TwoThousandDescriptions set a3price = &#039;[Cost34]&#039;<BR><BR>UPDATE TwoThousandDescriptions set a4price = &#039;[Cost45]&#039;<BR>UPDATE TwoThousandDescriptions set a5price = &#039;[Cost56]&#039;<BR>UPDATE TwoThousandDescriptions set a6price = &#039;[Cost67]&#039;<BR><BR>UPDATE TwoThousandDescriptions set a7price = &#039;[Cost78]&#039;<BR>UPDATE TwoThousandDescriptions set a8price = &#039;[Cost89]&#039;<BR>UPDATE TwoThousandDescriptions set a9price = &#039;[Cost910]&#039;<BR><BR>UPDATE TwoThousandDescriptions set a10price = &#039;[Cost1011]&#039;<BR>UPDATE TwoThousandDescriptions set a11price = &#039;[Cost1112]&#039;<BR>UPDATE TwoThousandDescriptions set a12price = &#039;[Cost1213]&#039;<BR><BR>Anyway, since there is no WHERE clause on that UPDATE, you are changing ALL THE RECORDS in the table on every UPDATE. <BR><BR>Is this *REALLY* what you want??? I sure hope not!<BR><BR>

4. Senior Member
Join Date
Dec 1969
Posts
228

## I May Be A Half-Wit, but . . .

I left off the Where clause to make things easier to read, just for the disucssion thread (maybe not a good idea. . .)<BR><BR>But about generating the pattern,11, 12, 21, 22 etc.<BR><BR>Any thoughts

5. Senior Member
Join Date
Dec 1969
Posts
228

## THANKS! IT WORKS!

You knew it would . . .

6. Senior Member
Join Date
Dec 1969
Posts
96,118

## Not at all, but I was half right...

Even with a WHERE clause in there, you are *still* doing too much work.<BR><BR>You are still doing a SEPARATE SQL UPDATE for each of the fields in each record, aren&#039;t you?<BR><BR>UPDATE TwoThousandDescriptions set a1price = &#039;[Cost11]&#039; WHERE id=1 <BR>UPDATE TwoThousandDescriptions set a2price = &#039;[Cost12]&#039; WHERE id=1<BR>UPDATE TwoThousandDescriptions set a3price = &#039;[Cost13]&#039; WHERE id=1<BR><BR>[Or whatever your WHERE clause looks like.]<BR><BR>Wouldn&#039;t it be a *lot* better to do:<BR><BR>UPDATE TwoThousandDescriptions <BR>SET a1price = &#039;[Cost11]&#039;, a2price = &#039;[Cost12]&#039;,a3price = &#039;[Cost13]&#039; <BR>WHERE id=1<BR><BR>??? Process *all* the field updates for a given record at the same time?<BR><BR>Granted, this is an efficiency issue, not a code correctness issue. So you get to decide how important it is.<BR><BR><BR><BR>

#### Posting Permissions

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