data entered are not inserting into the database

neo2morris · April 5, 2017, 6:25am

local composer = require( "composer" ) local scene = composer.newScene() local widget = require("widget") local sqlite3 = require ("sqlite3") local crypto = require ("crypto") local bash = crypto.digest(crypto.sha512, "test") local path = system.pathForFile("mobilesys.db", system.DocumentsDirectory) local db = sqlite3.open(path) --Handle the applicationExit event to close the db local function onSystemEvent( event ) if( event.type == "applicationExit" ) then db:close() end end ---------------------------------------------------------------------- -- create() Runtime:addEventListener("system",onSystemEvent) function scene:create( event ) local sceneGroup = self.view -- Code here runs when the scene is first created but has not yet appeared on screen --local background = display.newImageRect("image3.jpg",340, 570) -- background.x = display.contentCenterX -- background.y = display.contentCenterY -- sceneGroup:insert(background) local firstname = display.newText("FIRST NAME:",50,50,native.systemFont,14) firstname:setFillColor(1,0,1) sceneGroup:insert(firstname) firstnamebox = native.newTextField(240,50,170,20) firstnamebox.size = 14 firstnamebox.inputType = "text" local lastname = display.newText("LAST NAME:",50,85,native.systemFont,14) lastname:setFillColor(1,0,1) sceneGroup:insert(lastname) lastnamebox = native.newTextField(240,85,170,20) lastnamebox.sie= 14 lastnamebox.inputType = "text" local username1 = display.newText("USERNAME:",50,120, native.systemFont,14) username1:setFillColor(1,0,1) sceneGroup:insert(username1) usernamebox1 = native.newTextField(240,120,170,20) usernamebox1.size = 14 usernamebox1.inputType = "text" local password1 = display.newText("PASSWORD:",50,155,native.systemFont,14) password1:setFillColor(1,0,1) sceneGroup:insert(password1) passwordbox1 = native.newTextField(240,155,170,20) passwordbox1.size = 14 passwordbox1.inputType = "text" local repassword = display.newText("RETYPE PASSWORD:",80,190,native.systemFont,14) repassword:setFillColor(1,0,1) sceneGroup:insert(repassword) repasswordbox = native.newTextField(240,190,170,20) repasswordbox.size = 14 repasswordbox.inputType = "text" local email = display.newText("E-MAIL:",30,225,native.systemFont,14) email:setFillColor(1,0,1) sceneGroup:insert(email) emailbox = native.newTextField(240,225,170,20) emailbox.size = 14 emailbox.inputType = "text" local dob = display.newText("DATE OF BIRTH:",60,260,native.systemFont,14) dob:setFillColor(1,0,1) sceneGroup:insert(dob) dobbox = native.newTextField(240,260,170,20) dobbox.size = 14 dobbox.inputType = "date" local pnumber = display.newText("MOBILE NUMBER",60,295,native.systemFont,14) pnumber:setFillColor(1,0,1) sceneGroup:insert(pnumber) local gender = display.newText("GENDER",30,320,native.systemFont,14) gender:setFillColor(1,0,1) sceneGroup:insert(gender) genderbox = native.newTextField(240,320,170,20) genderbox.size = 14 genderbox.inputType = "text" pnumberbox = native.newTextField(240,295,170,20) pnumberbox.size = 14 pnumberbox.inputType = "int" local function submitform(event) local record = {} record.first\_name = firstnamebox.text record.last\_name = lastnamebox.text record.username = usernamebox1.text record.password = passwordbox1.text record.email = emailbox.text record.phone\_number = pnumberbox.int record.dob = dobbox.date record.gender = gender.text db.create(record) end local nextpage = widget.newButton( { left = -20, top = 450, id = "nextpage", label = "NEXT", onEvent = handleButtonEvent } ) sceneGroup:insert(nextpage) function nextpage:touch(event) if (event.phase == "ended") then local record ={} local h = [[INSERT INTO users VALUES (NULL, ']].. firstnamebox.text ..[[',']] ..lastnamebox.text.. [[',']] ..usernamebox1.text.. [[',']].. passwordbox1.text .. [[',']] ..emailbox.text.. [[',']] ..pnumberbox.text.. [[,]] ..dobbox.text.. [[',']] ..genderbox.text.. [[');]] db:exec(h) composer.removeScene("regform1",true) composer.gotoScene("regform2",{effect = "fade",time = 200}) end return true; end local cancel = widget.newButton( { left = 150, top = 450, id = "cancel", label = "CANCEL", onRelease = submitform, onEvent = handleButtonEvent } ) sceneGroup:insert(cancel) function cancel:touch(event) if (event.phase == "ended") then composer.removeScene("regform1",true) composer.gotoScene("login",{effect = "fade",time = 100}) end return true; end end -- show() function scene:show( event ) local sceneGroup = self.view local phase = event.phase if ( phase == "will" ) then -- Code here runs when the scene is still off screen (but is about to come on screen) elseif ( phase == "did" ) then -- Code here runs when the scene is entirely on screen end end -- hide() function scene:hide( event ) local sceneGroup = self.view local phase = event.phase if ( phase == "will" ) then -- Code here runs when the scene is on screen (but is about to go off screen) firstnamebox:removeSelf() firstnamebox = nil lastnamebox:removeSelf() lastnamebox = nil usernamebox1:removeSelf() usernamebox1 = nil passwordbox1:removeSelf() passwordbox1 = nil repasswordbox:removeSelf() repasswordbox = nil emailbox:removeSelf() emailbox = nil dobbox:removeSelf() dobbox = nil pnumberbox:removeSelf() pnumberbox = nil genderbox:removeSelf() genderbox = nil elseif ( phase == "did" ) then -- Code here runs immediately after the scene goes entirely off screen end end -- destroy() function scene:destroy( event ) local sceneGroup = self.view -- Code here runs prior to the removal of scene's view end -- ----------------------------------------------------------------------------------- -- Scene event function listeners -- ----------------------------------------------------------------------------------- scene:addEventListener( "create", scene ) scene:addEventListener( "show", scene ) scene:addEventListener( "hide", scene ) scene:addEventListener( "destroy", scene ) -- ----------------------------------------------------------------------------------- return scene

bjsorrentino · April 5, 2017, 9:21pm

Hi @neo2morris,

Some basic print() debugging would be valuable in this case. Is the proper data getting to your “submitform()” function, before you attempt to insert the database record? Are there any Lua-side errors? One definite issue is the invalidity of things like “pnumberbox.int” and “dobbox.date”… those are not valid properties of the text input fields, so they might cause an error on the SQLite side of things.

Brent

neo2morris · April 7, 2017, 1:52pm

local composer = require( "composer" ) local widget = require ("widget") local sqlite3 = require ("sqlite3") local scene = composer.newScene() -- ----------------------------------------------------------------------------------- -- Code outside of the scene event functions below will only be executed ONCE unless -- the scene is removed entirely (not recycled) via "composer.removeScene()" -- ----------------------------------------------------------------------------------- -- ----------------------------------------------------------------------------------- -- Scene event functions -- ----------------------------------------------------------------------------------- local path = system.pathForFile("mobilesys.db", system.DocumentsDirectory) db = sqlite3.open(path) --Handle the applicationExit event to close the db local function onSystemEvent( event ) if( event.type == "applicationExit" ) then db:close() end end Runtime:addEventListener("system",onSystemEvent) -- create() function scene:create( event ) local sceneGroup = self.view -- Code here runs when the scene is first created but has not yet appeared on screen local bg = display.newRect(0,0,500,600) bg.x=display.contentCenterX bg.y=display.contentCenterY bg:setFillColor(0,0,0.4) firstname = native.newTextField(160,40,200,30) firstname.placeholder = "First Name" firstname.font = native.newFont("Helvetica",12) firstname:resizeFontToFitHeight() lastname = native.newTextField(160,90,200,30) lastname.placeholder = "Last Name" lastname.font = native.newFont("Helvetica",12) lastname:resizeFontToFitHeight() username = native.newTextField(160,140,200,30) username.placeholder = "Username" username.font = native.newFont("Helvetica",12) username:resizeFontToFitHeight() password = native.newTextField(160,190,200,30) password.placeholder="Password" password.font = native.newFont("Helvetica",12) password.isSecure=true password:resizeFontToFitHeight() email = native.newTextField(160,240,200,30) email.placeholder = "E-mail" email.font = native.newFont("Helvetica",12) email:resizeFontToFitHeight() mobile = native.newTextField(160,290,200,30) mobile.placeholder = "Phone Number" mobile.font = native.newFont("Helvetica",12) mobile:resizeFontToFitHeight() gender = native.newTextField(160,340,200,30) gender.placeholder = "Gender" gender.font = native.newFont("Helvetica",12) gender:resizeFontToFitHeight() dob = native.newTextField(160,390,200,30) dob.placeholder = "Date Of Birth" dob.font = native.newFont("Helvetica",12) dob:resizeFontToFitHeight() local signup = widget.newButton( { top = 450, left = 20, label = "SIGNUP", onEvent = handleButtonEEvent, embose = true, shape = "roundedRect", width = 100, height = 35, conerRadius = 10, strokeWidth = 10, fillColor = { default={1,0,0,1}, over={1,0.1,0.7,0.4} }, } ) local function onSignupTouch( event ) if (event.phase == "ended") then local insertdb = [[INSERT INTO users VALUES (NULL ,']]..first\_name.text..[[',']] ..last\_name.text..[[',']]..username.varchar..[[',']]..password.varchar..[[',']] ..email.varchar..[[',']]..mobile.number..[[',']]..dob.date..[[',']]..gender.text..[[');]] db:exec(insertdb) composer.removeScene("regform1",true) composer.gotoScene("login",{effect = "fade", time = 50}) end return true end signup:addEventListener("touch",onSignup1Touch) local cancel = widget.newButton( { top = 450, left = 200, label = "CANCEL", onEvent = handleButtonEEvent, embose = true, shape = "roundedRect", width = 100, height = 35, conerRadius = 3, strokeWidth = 4, fillColor = { default={1,0,0,1}, over={1,0.1,0.7,0.4} }, } ) local function onCancelTouch( event ) if (event.phase == "ended") then composer.removeScene("regform1",true) composer.gotoScene("login",{effect = "fade", time = 50}) end return true end cancel:addEventListener("touch",onCancelTouch) --local usersdb = [[CREATE TABLE IF NOT EXITS users(id INTEGER PRIMARY autoincrement, -- first\_name,last\_name,username,password,email,mobile,dob,gender);]] --db:exec(usersdb) local function onSystemEvent( event ) if event.type == "applicationExit" then if db and db:isopen() then db:close() end end end local function submitform(event) local record = {} record.first\_name = firstname.text record.last\_name = lastname.text record.username = username.varchar record.password = password.varchar record.email = email.varchar record.phone\_number = mobile.INTEGER record.dob = dob.date record.gender = gender.text db.create(record) end end -- show() function scene:show( event ) local sceneGroup = self.view local phase = event.phase if ( phase == "will" ) then -- Code here runs when the scene is still off screen (but is about to come on screen) elseif ( phase == "did" ) then -- Code here runs when the scene is entirely on screen end end -- hide() function scene:hide( event ) local sceneGroup = self.view local phase = event.phase if ( phase == "will" ) then -- Code here runs when the scene is on screen (but is about to go off screen) firstname:removeSelf() firstname = nil lastname:removeSelf() lastname = nil username:removeSelf() username = nil password:removeSelf() password = nil email:removeSelf() email = nil mobile:removeSelf() mobile = nil gender:removeSelf() gender = nil dob:removeSelf() dob = nil elseif ( phase == "did" ) then -- Code here runs immediately after the scene goes entirely off screen end end -- destroy() function scene:destroy( event ) local sceneGroup = self.view -- Code here runs prior to the removal of scene's view end -- ----------------------------------------------------------------------------------- scene:addEventListener( "create", scene ) scene:addEventListener( "show", scene ) scene:addEventListener( "hide", scene ) scene:addEventListener( "destroy", scene ) -- ----------------------------------------------------------------------------------- return scene

sorry for the delay. i have redesign it but the same problem persist this time no error from both the simulator and simulator output.

anon63346430 · April 7, 2017, 2:06pm

You are mixing Lua and SQL. For example username.varchar will return nil. What you want is username.Text.

rob · April 7, 2017, 4:45pm

Also your “db” object is an sqlite database. There is no .create() method for the database. Normally you would format a string of SQL and pass that to the db.exec() function to do the query. Please see:

https://docs.coronalabs.com/api/library/sqlite3/index.html

Now I created a database helper library in our Business App sample (https://github.com/coronalabs-samples/business-app-sample) that has a .create() method. But you can’t mix the two. The database.lua file from the business app sample handles opening and closing the database for you

Rob

neo2morris · April 7, 2017, 5:56pm

local composer = require( "composer" ) local widget = require ("widget") local sqlite3 = require ("sqlite3") local scene = composer.newScene() -- ----------------------------------------------------------------------------------- -- Code outside of the scene event functions below will only be executed ONCE unless -- the scene is removed entirely (not recycled) via "composer.removeScene()" -- ----------------------------------------------------------------------------------- -- ----------------------------------------------------------------------------------- -- Scene event functions -- ----------------------------------------------------------------------------------- local path = system.pathForFile("mobilesys.db", system.DocumentsDirectory) db = sqlite3.open(path) --Handle the applicationExit event to close the db local function onSystemEvent( event ) if( event.type == "applicationExit" ) then db:close() end end Runtime:addEventListener("system",onSystemEvent) -- create() function scene:create( event ) local sceneGroup = self.view -- Code here runs when the scene is first created but has not yet appeared on screen local bg = display.newRect(0,0,500,600) bg.x=display.contentCenterX bg.y=display.contentCenterY bg:setFillColor(0,0,0.4) firstname = native.newTextField(160,40,200,30) firstname.placeholder = "First Name" firstname.font = native.newFont("Helvetica",12) firstname:resizeFontToFitHeight() lastname = native.newTextField(160,90,200,30) lastname.placeholder = "Last Name" lastname.font = native.newFont("Helvetica",12) lastname:resizeFontToFitHeight() username = native.newTextField(160,140,200,30) username.placeholder = "Username" username.font = native.newFont("Helvetica",12) username:resizeFontToFitHeight() password = native.newTextField(160,190,200,30) password.placeholder="Password" password.font = native.newFont("Helvetica",12) password.isSecure=true password:resizeFontToFitHeight() email = native.newTextField(160,240,200,30) email.placeholder = "E-mail" email.font = native.newFont("Helvetica",12) email:resizeFontToFitHeight() mobile = native.newTextField(160,290,200,30) mobile.placeholder = "Phone Number" mobile.font = native.newFont("Helvetica",12) mobile:resizeFontToFitHeight() gender = native.newTextField(160,340,200,30) gender.placeholder = "Gender" gender.font = native.newFont("Helvetica",12) gender:resizeFontToFitHeight() dob = native.newTextField(160,390,200,30) dob.placeholder = "Date Of Birth" dob.font = native.newFont("Helvetica",12) dob:resizeFontToFitHeight() local signup = widget.newButton( { top = 450, left = 20, label = "SIGNUP", onEvent = handleButtonEEvent, embose = true, shape = "roundedRect", width = 100, height = 35, conerRadius = 10, strokeWidth = 10, onRelease = submitForm, fillColor = { default={1,0,0,1}, over={1,0.1,0.7,0.4} }, } ) local function onSignupTouch( event ) if (event.phase == "ended") then local insertdb = [[INSERT INTO users VALUES (NULL ,']]..firstname.text..[[',']] ..lastname.text..[[',']]..username.text..[[',']]..password.text..[[',']] ..email.text..[[',']]..mobile.text..[[',']]..dob.text..[[',']]..gender.text..[[');]] db:exec(insertdb) composer.removeScene("regform1",true) composer.gotoScene("login",{effect = "fade", time = 50}) end return true end signup:addEventListener("touch",onSignupTouch) local cancel = widget.newButton( { top = 450, left = 200, label = "CANCEL", onEvent = handleButtonEEvent, embose = true, shape = "roundedRect", width = 100, height = 35, conerRadius = 3, strokeWidth = 4, fillColor = { default={1,0,0,1}, over={1,0.1,0.7,0.4} }, } ) local function onCancelTouch( event ) if (event.phase == "ended") then composer.removeScene("regform1",true) composer.gotoScene("login",{effect = "fade", time = 50}) end return true end cancel:addEventListener("touch",onCancelTouch) --local usersdb = [[CREATE TABLE IF NOT EXITS users(id INTEGER PRIMARY autoincrement, -- first\_name,last\_name,username,password,email,mobile,dob,gender);]] --db:exec(usersdb) local function onSystemEvent( event ) if event.type == "applicationExit" then if db and db:isopen() then db:close() end end end local function submitForm(event) local record = {} record.first\_name = firstname.text record.last\_name = lastname.text record.username = username.text record.password = password.text record.email = email.text record.phone\_number = mobile.text record.dob = dob.text record.gender = gender.text db.create(record) end end -- show() function scene:show( event ) local sceneGroup = self.view local phase = event.phase if ( phase == "will" ) then -- Code here runs when the scene is still off screen (but is about to come on screen) elseif ( phase == "did" ) then -- Code here runs when the scene is entirely on screen end end -- hide() function scene:hide( event ) local sceneGroup = self.view local phase = event.phase if ( phase == "will" ) then -- Code here runs when the scene is on screen (but is about to go off screen) firstname:removeSelf() firstname = nil lastname:removeSelf() lastname = nil username:removeSelf() username = nil password:removeSelf() password = nil email:removeSelf() email = nil mobile:removeSelf() mobile = nil gender:removeSelf() gender = nil dob:removeSelf() dob = nil elseif ( phase == "did" ) then -- Code here runs immediately after the scene goes entirely off screen end end -- destroy() function scene:destroy( event ) local sceneGroup = self.view -- Code here runs prior to the removal of scene's view end -- ----------------------------------------------------------------------------------- scene:addEventListener( "create", scene ) scene:addEventListener( "show", scene ) scene:addEventListener( "hide", scene ) scene:addEventListener( "destroy", scene ) -- ----------------------------------------------------------------------------------- return scene

have corrected some of the errors but still now not no error message from both the simulator and simulator output, don’t know where the error is coming from and my data is already created using sqlite3 studio. have read the sample but still

anon63346430 · April 7, 2017, 6:11pm

If you posted an actual error message it would be really helpful rather than rely on us trying to guess! One thing to check is that the fields you are trying to insert EXACTLY match the metadata of the users table.

Maybe try with a single text box and a single field in the users table, get the most basic version running and then scale it.

Q. what are you trying to achieve?

neo2morris · April 7, 2017, 6:14pm

i want new users to be able to register in order to be able to login later and am not getting any errors they are just not inserting tried one a few weeks ago and it worked but now it isn’t can’t out why

anon63346430 · April 7, 2017, 6:28pm

Why do they need to register for a local app?

neo2morris · April 7, 2017, 6:32pm

i need information from them to perform some actions

rob · April 7, 2017, 7:01pm

If you’re using the Business App sample, then why not just use it as it’s intended there. Use the database.lua module which supports CRUD operations on the database and abstracts a lot of the SQL away from you.

Rob

bjsorrentino · April 5, 2017, 9:21pm