SQL Table

[aaronk]

Hello,

I have been creating a SQL table using the following code:

SQL1.ExecNonQuery("C REATE TABLE names (id TEXT, name1 TEXT, name2 TEXT, name3 TEXT)")

(need to remove the space between C and R in Create above, the forum wouldn't let me submit the query for some reason)

However I want to know what would be the quick and easy way to create a table where the name go up to 300.

Do I need to keep entering in the name in my query up to 300 or is there a faster way such as using some type of loop?

Also, what is the max number of columns can I create per table ?

 

[keirS]

Max number of columns depends on how SQLite has been compiled. The default limit is 2000. You can run a query using "PRAGMA compile_options" which will return a cursor of defaults that have been overridden.

 

[mc73]

dim qr as string
qr="C REATE TABLE names (ID TEXT"
for k=1 to 300
    qr=qr & ", name" & k & " TEXT"
next
qr=qr & ")"
sql1.execNonQuery(qr)

 

[Mahares]

I recommend using the Id as a primary key to prevent duplicates, unless of course there is a reason otherwise. Therefore, mc73 code will be slightly modified as follows:

Dim txt as string
SQL1.ExecNonQuery("DROP TABLE IF EXISTS names")
txt="CREATE TABLE names (Id INTEGER PRIMARY KEY" 
For i=1 To 300
   txt=txt & ", Name" & i & " TEXT"
Next
txt=txt & ")"
SQL1.ExecNonQuery(txt)