Create For/Do Loop to create

[Fred48]

Hi,

I would like to create a table using the For/Do loop because the table name is similar except for the last postion which can contain values 1 to 4. Below is my current code:


strCMD = "CREATE TABLE WEEK1 (" & _
"TGSN varchar (8)," & _
"MEAS_PRD varchar (2)," & _
"[HOUR] int," & _
"MCDA varchar (5)," & _
"OFFDCCS varchar (7)," & _
"BLKG varchar (7))"

cmdDatabase = New OleDbCommand(strCMD, cnn)
cmdDatabase.ExecuteNonQuery()

strCMD = "CREATE UNIQUE INDEX PrimaryKeyWEEK ON WEEK1([HOUR])"
cmdDatabase = New OleDbCommand(strCMD, cnn)
cmdDatabase.ExecuteNonQuery()

I am creating four tables, Week1, Week2, Week3 and Week4. So, instead of having four sets of code I would like to have one and somehow change the week number from static to a variable.

Any one have any idease?

 

[Andrzejek]

How about:

[blue]For i As Integer = 1 To 4[/blue]
        strCMD = "CREATE TABLE  WEEK[blue]" & i & "[/blue]  (" & _
                "TGSN            varchar (8)," & _
                "MEAS_PRD        varchar (2)," & _
                "[HOUR]          int," & _
                "MCDA            varchar (5)," & _
                "OFFDCCS         varchar (7)," & _
                "BLKG            varchar (7))"

        cmdDatabase = New OleDbCommand(strCMD, cnn)
        cmdDatabase.ExecuteNonQuery()

        strCMD = "CREATE UNIQUE INDEX PrimaryKeyWEEK ON WEEK[blue]" & i & "[/blue]([HOUR])"
        cmdDatabase = New OleDbCommand(strCMD, cnn)
        cmdDatabase.ExecuteNonQuery()[blue]
Next i[/blue]

Have fun.

---- Andy

 

[Fred48]

Andy,

Thanks for the replay and I incorporated your changes and I received an complier error. The error states, variable 'i' hides a variable in ean enclosed block. Below is the new code:

For i As Integer = 1 To 4
strCMD = "CREATE TABLE WEEK" & i & " (" & _
"TGSN varchar (8)," & _
"MEAS_PRD varchar (2)," & _
"[HOUR] int," & _
"MCDA varchar (5)," & _
"OFFDCCS varchar (7)," & _
"BLKG varchar (7))"

cmdDatabase = New OleDbCommand(strCMD, cnn)
cmdDatabase.ExecuteNonQuery()

strCMD = "CREATE UNIQUE INDEX PrimaryKeyWEEK ON WEEK" & i & "([HOUR])"
cmdDatabase = New OleDbCommand(strCMD, cnn)
cmdDatabase.ExecuteNonQuery()
Next i

 

[Fred48]

Andy,

Ignore my last email, operator error on my part. I had a similar soultion but it did not work. But, your soultion does work.

Thanks again!!