Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 151
  • Last Modified:

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
0
NickUA
Asked:
NickUA
  • 4
  • 2
1 Solution
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now