Writing a loop or sql that inserts a different value 4 times then starts over

I am trying to write either and sql or some sort of loop that will produce something like the following insert statement:
INSERT INTO TBL_SEAT (SEAT_NUM, SEAT_ROW, SEAT_TYPE, PLANE_ID) VALUES ('A', 1, 'WINDOW', 3434278231);
INSERT INTO TBL_SEAT (SEAT_NUM, SEAT_ROW, SEAT_TYPE, PLANE_ID) VALUES ('B', 1, 'AISLE', 3434278231);
INSERT INTO TBL_SEAT (SEAT_NUM, SEAT_ROW, SEAT_TYPE, PLANE_ID) VALUES ('C', 1, 'AISLE', 3434278231);
INSERT INTO TBL_SEAT (SEAT_NUM, SEAT_ROW, SEAT_TYPE, PLANE_ID) VALUES ('D', 1, 'WINDOW', 3434278231);
INSERT INTO TBL_SEAT (SEAT_NUM, SEAT_ROW, SEAT_TYPE, PLANE_ID) VALUES ('A', 2, 'WINDOW', 3434278231);
INSERT INTO TBL_SEAT (SEAT_NUM, SEAT_ROW, SEAT_TYPE, PLANE_ID) VALUES ('B', 2, 'AISLE', 3434278231);
INSERT INTO TBL_SEAT (SEAT_NUM, SEAT_ROW, SEAT_TYPE, PLANE_ID) VALUES ('C', 2, 'AISLE', 3434278231);
INSERT INTO TBL_SEAT (SEAT_NUM, SEAT_ROW, SEAT_TYPE, PLANE_ID) VALUES ('D', 2, 'WINDOW', 3434278231);

notice how its goes A, B, C, D for one column then 1.1,1,1 for the next column then the first column starts over, but the other column continues on 1, 2, 3, 4 etc.

any help will be appreciated.

thanks
LVL 1
NickUAAsked:
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.

arif_eqbalCommented:
I think its Row-Column thing
Rows go 1,2,3..
and Columns go A,B,C...

Again Window and Aisle seats will always be fixed based on the Column i.e. A or B or C ...

So the Loop might go like this

Dim Qry as String
Dim NumberOfRows As Integer=10
Dim NumberOfColumns As Integer=5
Dim PlaneID as String="3434278231"
Dim SeatType as String

For i=1 to NumberOfRows
    For j=1 to NumberOfColumns
        If j=2 Or J=4 Then
            'Assuming B & D are Window Seats (j=1 For A, 2 for B and so on)
            'Modify this If Else as per actual Plan
            SeatType="Window"
        Else
           SeatType="Aisle"
        End If
        Qry=String.Format("INSERT INTO TBL_SEAT (SEAT_NUM, SEAT_ROW, SEAT_TYPE, PLANE_ID) VALUES ('{0}', {1},'{2}', '{3}');",Chr(64+j),i,SeatType,PlaneID)

    Next j
Next i
0
arif_eqbalCommented:
You can Either Fire the Query from within the Loop Or store in Array Your Choice....

0
NickUAAuthor Commented:
arif_eqbal
i havent been able to try it, but that looks real good!

now if i want to say the plane has 100 seats will it work for that?   I mean can i change the numbers of rows and columns dynamically and it will still work right?

thanks
james
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

arif_eqbalCommented:
You can of course
Just set the Row Number and Col Number
Say there are 100 Rows and 8 Cols A, B, C, D, E, F, G, H

Seating Arranagement like

[A] [B] [C]        [D] [E]        [F] [G] [H]

so A and H will be windows so

Dim Qry as String
Dim NumberOfRows As Integer=100 '----------> FOR 100 ROWS
Dim NumberOfColumns As Integer=8 '---------> FOR A through H Seats
Dim PlaneID as String="3434278231"
Dim SeatType as String

For i=1 to NumberOfRows
    For j=1 to NumberOfColumns
        If j=1 Or J=8 Then
            'Assuming A & H are Window Seats
            SeatType="Window"
        Else
           'More If Blocks might come here as per Seating arrangement
           SeatType="Aisle"
        End If
        Qry=String.Format("INSERT INTO TBL_SEAT (SEAT_NUM, SEAT_ROW, SEAT_TYPE, PLANE_ID) VALUES ('{0}', {1},'{2}', '{3}');",Chr(64+j),i,SeatType,PlaneID)

    Next j
Next i

0
NickUAAuthor Commented:
arif_eqbal

can you explain this part?
('{0}', {1},'{2}', '{3}')
0
arif_eqbalCommented:
OK
This is String Formatting

actually when we want to concat a variable value with a String literal we use
Greeting = "Hello " & UserName

However Now we can have
Greeting = Strimng.Format("Hello {0}", UserName)

the {0} stands for the 0th argument (or the first argument)

so in order to make a Query like
INSERT INTO EMP (EMP_CODE, EMP_NAME) Values (1,'Jack')
we can have

Qry=String.Format("INSERT INTO EMP (EMP_CODE, EMP_NAME) Values ({0},'{1}')", variable_Emp_Code, variable_Emp_Name)

the value of variable_Emp_Code will be replaced at {0} and the second variable variable_Emp_Name will be replaced at {1} and the Single Quotes ' required for string value in Query



0

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
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
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.