Modify table with script

please imagine the fields in mytable:


each "p" represents a page.

If I want to a have script to delete page 2 I can do something like this:

pageOrder = 2
Set rsc = Server.CreateObject("ADODB.Recordset")
sqlc = "SHOW COLUMNS FROM mytable LIKE 'p%'"
set rsc = cn.execute(sqlc)
      On Error Resume Next
      Do While Not rsc.EOF
            if InStr(rsc("field"),"q"&pageOrder)>0 then
                  sqla = "ALTER TABLE mytable DROP COLUMN "&rsc("field")
            end if
set rsc = nothing

But, I want the script also to change the fields of pages 3,4 to become 2,3 - How can I do it?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
i think you should reconsider the table layout....
instead of adding columns, but normalize it like this:

and store the values 1,2,3 etc into page-id

this avoids table structure changes
hope this helps
arielbfAuthor Commented:
I understand your approach. But  I  can't have like that - so I need it to be "my way"... Thanks anyway.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Wondering why...

Anyhow you would need to rename the columns, one by one...
Alter table xxxx change colname newcolname
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

arielbfAuthor Commented:
Yes - I know how to modify columns one by one writing sql commands
But i need to script it
Walter RitzelSenior Software EngineerCommented:
in the lines of the script you wrote, you can replace the if statement for something like this:
if page order < rset(field) then
    command.execute(""alter table ...")
end if

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry to reply late, but I "disagree" with the above suggestion. at least, I see a serious issue with it, as the order of the columns presented by "show columns" is not the good on after adding/removing/altering columns.

while you may want to ignore this information, any reader of this "solution" should be aware of those issues.
to solve that, you may want to go this way: using the information_schema views instead

still, the "ordering" issue is present, and page 12 would eventually not come after page 9 but before, as the column names would be sorted by a name (string) and not by the numerical value representation.

good luck with your project
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.