Page 1 of 1

Created database and table but can't create column.

Posted: Sun Jun 09, 2019 3:58 pm
by Columbo
I have a database called categories.db which I have opened. The user selects a category from a listview and the selected category is held in a variable called catselection. Now I want to create another database, (sub-category), which will be attached to the categories database. This database will be named by the user using an InputRequester(). When the database is created I want to create a table in the database and the table name will be the same as the database name and will have a column called ‘type’.

I am able to create the database and also the table however, I cannot get the needed column created in the table. Here is the code:

Code: Select all

Procedure addSubCategory()
    Input$ = InputRequester("AddSub-Category", "Sub-Category :", "")
    If Input$ <> ""     
       dbFile = LCase(catselection) + ".db"
       dbTable = LCase(input$)
       dbAlias = LCase(catselection)
       If CreateFile(#dbHandle, dbFile)
         CloseFile(#dbHandle)
         result =  DatabaseUpdate(#dbaseID2, "ATTACH DATABASE '" + dbFile + "' AS '" + dbAlias + "'")
         result = DatabaseUpdate(#dbaseID2, "CREATE TABLE '" + dbTable + "' (type CHAR(30))")           
         query$ = "INSERT INTO '" + dbTable + "' (type) VALUES ('" + Input$ +  "')"
         result = DatabaseUpdate(#dbaseID2, query$)
       If result > 0
         MessageRequester("Add Category", "Category added.", #PB_MessageRequester_Ok)
     Else
         MessageRequester("Add Category", "ERROR: Failed to add Category", #PB_MessageRequester_Ok)
     EndIf
   EndIf 
 
I am obviously doing something wrong in the code. Can anyone enlighten me?

Thanks

Re: Created database and table but can't create column.

Posted: Sun Jun 09, 2019 4:34 pm
by Marc56us
(Deleted. I misunderstood the question) :?

Re: Created database and table but can't create column.

Posted: Sun Jun 09, 2019 4:34 pm
by skywalk
You can avoid simple and tricky errors by using SQLfiddle or DB Browser for SQLite to try your queries before coding them.

Code: Select all

--ATTACH DATABASE 'dbFilePathName' AS dbAlias;
CREATE TABLE TI (type TEXT, type2 TEXT);
ALTER TABLE TI ADD type3 TEXT;
INSERT INTO TI (type,type2,type3) VALUES('1','2','3');

Re: Created database and table but can't create column.

Posted: Sun Jun 09, 2019 11:28 pm
by Columbo
Thank you very much and for the tips on SQL Fiddle and DB_Browser.