Problem with SQLlite(Select and Insert statement)

B

badal405

Member
Licensed User
Longtime User
Hi Erel,
I have been facing problem with SQLlite while trying to insert new data or in select statement. Let me explain you about the project. This is my first project and simple registration form. The new user will create a user and after the login with the same user. Database copying and initializing successfully even when i create a new user, it is also saving successfully but when i go for login with the same user it is telling no user found. Here i could no attached the project due to size issues. So am giving a link below just for your kind consideration.
Waiting for your reply.
Thanks and best regards.

andRegistration - Download - 4shared - Md Shaukat Hossain Badal
 
B

badal405

Member
Licensed User
Longtime User
badal405 said:
Hi Erel,
I have been facing problem with SQLlite while trying to insert new data or in select statement. Let me explain you about the project. This is my first project and simple registration form. The new user will create a user and after the login with the same user. Database copying and initializing successfully even when i create a new user, it is also saving successfully but when i go for login with the same user it is telling no user found. Here i could no attached the project due to size issues. So am giving a link below just for your kind consideration.
Waiting for your reply.
Thanks and best regards.

andRegistration - Download - 4shared - Md Shaukat Hossain Badal
Click to expand...

Erel said:
The download link doesn't seem to work. Are you using File - Export as zip? It will make it smaller.

You can also post the relevant code.
Click to expand...

I have also attached the code. Please see the attachment too.
B4X: 
#Region  Project Attributes 
   #ApplicationLabel: B4A Example
   #VersionCode: 1
   #VersionName: 
   'SupportedOrientations possible values: unspecified, landscape or portrait.
   #SupportedOrientations: unspecified
   #CanInstallToExternalStorage: False
#End Region

#Region  Activity Attributes 
   #FullScreen: False
   #IncludeTitle: True
#End Region

Sub Process_Globals
   'These global variables will be declared once when the application starts.
   'These variables can be accessed from all modules.

End Sub

Sub Globals
   'These global variables will be redeclared each time the activity is created.
   'These variables can only be accessed from this module.
   Dim dbSQL As SQL
   Dim sSQLQuery As String 
   Dim txtUser As EditText
   Dim txtPassword As EditText
   Dim pnlRegistration As Panel
   Dim txtRegUserID As EditText
   Dim txtRegPass As EditText
   Dim txtRegRetypePass As EditText
   Dim pnlLogin As Panel
End Sub

Sub Activity_Create(FirstTime As Boolean)
   'Do not forget to load the layout file created with the visual designer. For example:
   'Activity.LoadLayout("Layout1")
   Activity.LoadLayout("registration")
   pnlRegistration.Visible=False
   If FirstTime Then
      'If File.Exists(File.DirRootExternal,"registration.db")=False Then
      If File.Exists(File.DirInternal ,"registration.db")=False Then
         'Msgbox(File.DirAssets,"")
         Try
            'File.Copy(File.DirAssets,"registration.db" ,File.DirRootExternal ,"registration.db")
            File.Copy(File.DirAssets,"registration.db" , File.DirInternal  ,"registration.db")
            'File.Copy(File.DirAssets,"registration.db" ,File.DirDefaultExternal ,"registration.db")
            ToastMessageShow("Successfully copy the database.",True)
         'Msgbox("No database found.")
         Catch
            ToastMessageShow("Can not find the database file to copy.",True)
            Return
         End Try
      Else
         'ToastMessageShow("Database found in " & File.DirInternal,True) 
         Msgbox("Database found in " & File.DirInternal,"Database")
      End If
      'dbSQL.Initialize(File.DirRootExternal, "registration.db", True)
      'dbSQL.Initialize(File.DirDefaultExternal, "registration.db", True)
      dbSQL.Initialize(File.DirInternal , "registration.db", True)
   End If
End Sub

Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub



Sub btnLogin_Click
Dim cCursor As Cursor
   If txtUser.Text="" Then
      Msgbox("You must enter the username.","Message")
      'Exit Sub
      Return
   End If
   'Try
      sSQLQuery="select * from user where user_name='" & txtUser.Text & "'"
      cCursor=dbSQL.ExecQuery(sSQLQuery)
      cCursor.Position=0
      If cCursor.RowCount=0 Then
         'Msgbox("Invlid username. Please type the correct user.","Wrong User")
         ToastMessageShow("No data found. Please create new user then login again.",True)
      Else
'         cCursor.Position=0
         If cCursor.GetString2(0)=txtUser.Text Then
            If cCursor.GetString2(1)=txtPassword.Text Then
               ToastMessageShow("Successfully login.",True)
               cCursor.Close 
            Else
               ToastMessageShow("Invalid password.",True)
               Return
            End If
         Else
            ToastMessageShow("Invalid username.",True)
            Return
         End If
      End If
'   Catch
'      ToastMessageShow("No such table found.",True)
'   End Try
End Sub
Sub btnCancel_Click
   Activity.Finish 
End Sub
Sub btnRegister_Click
   pnlRegistration.Visible=True
   pnlLogin.Visible=False
End Sub
Sub btnSave_Click
Dim answ As Int 
   If txtRegUserID.Text<>"" AND txtRegPass.Text<>"" Then
      answ = Msgbox2("Do you really want to save the data?","Attention","Yes","","No",Null)
      If answ=DialogResponse.POSITIVE Then
         sSQLQuery="insert into user values(?,?)"
         'Try
            dbSQL.ExecNonQuery2(sSQLQuery,Array As String(txtRegUserID,txtRegPass))
            ToastMessageShow("Data has been saved successfully.",True)
            txtRegUserID.Text=""
            txtRegPass.Text=""
            txtRegRetypePass.Text=""
            txtRegUserID.RequestFocus 
'         Catch
'            ToastMessageShow("There is a problem with Data and couldn't save.",True)
'            Return
'         End Try
         
      End If
   End If
End Sub
Sub btnRegCancel_Click
   pnlRegistration.Visible=False
   pnlLogin.Visible=True
End Sub
 

Attachments

  • andRegistration1.zip
    98.1 KB · Views: 246
Erel

Erel

B4X founder
Staff member
Licensed User
Longtime User
1. The SQL object should be a process global variable. This is very important.
2. Check DBUtils code module. Will help you with common tasks.
3. You are not always closing the cursor.

And...
4. The problem is in this code:
B4X: 
dbSQL.ExecNonQuery2(sSQLQuery,Array As String(txtRegUserID,txtRegPass))
You should change it to:
B4X: 
dbSQL.ExecNonQuery2(sSQLQuery,Array As String(txtRegUserID.Text,txtRegPass.Text))
 
B

badal405

Member
Licensed User
Longtime User
Thanks Erel. It is working now and feeling good too. I have some other inquiry.
1. After i installed the program i tried to see where it is installed. I search by program name but couldn't find any clue. where i can get my program installation path?
2. I also want to know what is the location of my database? I tried to search by my database name (registration.db) but nothing return
[BTW, I am using EsFile Explorer.]
3. How could i locate my sdcard from basic4android. Which method are indicating to sdcard.(For example: File.DirRootExternal,File.DirInternal,File.DirAssets or File.DirDefaultExternal)?

BTW, I am enjoying a lot with B4A.Thanks again
 
Last edited:
B

badal405

Member
Licensed User
Longtime User
Hi Erel,
Thank you very much for the help. It is working fine. Now i have another query related with online database communication. I have downloaded a project from the thread where you have demonstrated how to connect with web database like mysql. There i have found two method which i have mentioned below. Is it built in method of HTTP lib? Because I have seen another example from another source also mentioned the same method.

Sub hc_ResponseError (Response As HttpResponse, Reason As String, StatusCode As Int, TaskId As Int)

End Sub

Sub hc_ResponseSuccess (Response As HttpResponse, TaskId As Int)

End Sub

waiting for your reply.
Thanks and best regards.
 
You must log in or register to reply here.
Top