Created database and table but can't create column.

Just starting out? Need help? Post your questions and find answers here.
User avatar
Columbo
Enthusiast
Enthusiast
Posts: 303
Joined: Wed Sep 10, 2014 7:17 am
Location: Ontario Canada
Contact:

Created database and table but can't create column.

Post 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
http://www.oldtimeradiotoday.com - Listen to or download classic old time radio broadcasts.
Marc56us
Addict
Addict
Posts: 1477
Joined: Sat Feb 08, 2014 3:26 pm

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

Post by Marc56us »

(Deleted. I misunderstood the question) :?
Last edited by Marc56us on Sun Jun 09, 2019 4:59 pm, edited 2 times in total.
User avatar
skywalk
Addict
Addict
Posts: 3972
Joined: Wed Dec 23, 2009 10:14 pm
Location: Boston, MA

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

Post 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');
The nice thing about standards is there are so many to choose from. ~ Andrew Tanenbaum
User avatar
Columbo
Enthusiast
Enthusiast
Posts: 303
Joined: Wed Sep 10, 2014 7:17 am
Location: Ontario Canada
Contact:

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

Post by Columbo »

Thank you very much and for the tips on SQL Fiddle and DB_Browser.
http://www.oldtimeradiotoday.com - Listen to or download classic old time radio broadcasts.
Post Reply