Android Question android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: siswa.user (code 1555)

[Alam122]

*** Service (starter) Create ***
** Service (starter) Start **
** Activity (main) Create, isFirst = true **
/storage/emulated/0/Android/data/b4a.example/files
** Activity (main) Resume **
** Activity (main) Pause, UserClosed = true **
** Activity (act_login) Create, isFirst = true **
** Activity (act_login) Resume **
** Activity (act_login) Pause, UserClosed = true **
** Activity (act_daftar) Create, isFirst = true **
** Activity (act_daftar) Resume **
Error occurred on line: 72 (act_daftar)
android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: siswa.user (code 1555)
    at android.database.sqlite.SQLiteConnection.nativeExecuteForChangedRowCount(Native Method)
    at android.database.sqlite.SQLiteConnection.executeForChangedRowCount(SQLiteConnection.java:735)
    at android.database.sqlite.SQLiteSession.executeForChangedRowCount(SQLiteSession.java:754)
    at android.database.sqlite.SQLiteStatement.executeUpdateDelete(SQLiteStatement.java:64)
    at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1754)
    at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1682)
    at anywheresoftware.b4a.sql.SQL.ExecNonQuery(SQL.java:74)
    at java.lang.reflect.Method.invoke(Native Method)
    at anywheresoftware.b4a.shell.Shell.runVoidMethod(Shell.java:777)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:354)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:255)
    at java.lang.reflect.Method.invoke(Native Method)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:144)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:180)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:176)
    at anywheresoftware.b4a.objects.ViewWrapper$1.onClick(ViewWrapper.java:80)
    at android.view.View.performClick(View.java:6294)
    at android.view.View.onKeyUp(View.java:12470)
    at android.widget.TextView.onKeyUp(TextView.java:7475)
    at android.view.KeyEvent.dispatch(KeyEvent.java:2715)
    at android.view.View.dispatchKeyEvent(View.java:11713)
    at android.view.ViewGroup.dispatchKeyEvent(ViewGroup.java:1834)
    at android.view.ViewGroup.dispatchKeyEvent(ViewGroup.java:1834)
    at android.view.ViewGroup.dispatchKeyEvent(ViewGroup.java:1834)
    at android.view.ViewGroup.dispatchKeyEvent(ViewGroup.java:1834)
    at com.android.internal.policy.DecorView.superDispatchKeyEvent(DecorView.java:440)
    at com.android.internal.policy.PhoneWindow.superDispatchKeyEvent(PhoneWindow.java:1819)
    at android.app.Activity.dispatchKeyEvent(Activity.java:3267)
    at com.android.internal.policy.DecorView.dispatchKeyEvent(DecorView.java:354)
    at android.view.ViewRootImpl$ViewPostImeInputStage.processKeyEvent(ViewRootImpl.java:4733)
    at android.view.ViewRootImpl$ViewPostImeInputStage.onProcess(ViewRootImpl.java:4605)
    at android.view.ViewRootImpl$InputStage.deliver(ViewRootImpl.java:4147)
    at android.view.ViewRootImpl$InputStage.onDeliverToNext(ViewRootImpl.java:4200)
    at android.view.ViewRootImpl$InputStage.forward(ViewRootImpl.java:4166)
    at android.view.ViewRootImpl$AsyncInputStage.forward(ViewRootImpl.java:4293)
    at android.view.ViewRootImpl$InputStage.apply(ViewRootImpl.java:4174)
    at android.view.ViewRootImpl$AsyncInputStage.apply(ViewRootImpl.java:4350)
    at android.view.ViewRootImpl$InputStage.deliver(ViewRootImpl.java:4147)
    at android.view.ViewRootImpl$InputStage.onDeliverToNext(ViewRootImpl.java:4200)
    at android.view.ViewRootImpl$InputStage.forward(ViewRootImpl.java:4166)
    at android.view.ViewRootImpl$InputStage.apply(ViewRootImpl.java:4174)
    at android.view.ViewRootImpl$InputStage.deliver(ViewRootImpl.java:4147)
    at android.view.ViewRootImpl$InputStage.onDeliverToNext(ViewRootImpl.java:4200)
    at android.view.ViewRootImpl$InputStage.forward(ViewRootImpl.java:4166)
    at android.view.ViewRootImpl$AsyncInputStage.forward(ViewRootImpl.java:4326)
    at android.view.ViewRootImpl$ImeInputStage.onFinishedInputEvent(ViewRootImpl.java:4487)
    at android.view.inputmethod.InputMethodManager$PendingEvent.run(InputMethodManager.java:2435)
    at android.view.inputmethod.InputMethodManager.invokeFinishedInputEventCallback(InputMethodManager.java:1998)
    at android.view.inputmethod.InputMethodManager.finishedInputEvent(InputMethodManager.java:1989)
    at android.view.inputmethod.InputMethodManager$ImeInputEventSender.onInputEventFinished(InputMethodManager.java:2412)
    at android.view.InputEventSender.dispatchInputEventFinished(InputEventSender.java:141)
    at android.os.MessageQueue.nativePollOnce(Native Method)
    at android.os.MessageQueue.next(MessageQueue.java:325)
    at android.os.Looper.loop(Looper.java:142)
    
Message longer than Log limit (4000). Message was truncated.
** Service (starter) Destroy (ignored)**
** Activity (act_daftar) Pause, UserClosed = true **

#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.
    Public xui As XUI
    
    Public ko As SQL
    Dim rp As RuntimePermissions
    Dim B64 As Base64
    Dim epass As String
    
    Public DBFileName = "data.db" As String
    Public DBFileDir As String
    Public DBTableName  = "siswa" As String
    
    Public lstTable, lstRowID As List
    Public DisplayMode = 1 As Int
    Public SelectedRow, SelectedCol As Int
    
    Public RowIDList As List
    Public CurrentIndex = -1 As Int        ' index of the current entry

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.
    
    Private txtnama, txtuser, txtpass, txtno As B4XView
    Private Button1 As B4XView
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("hal_daftar")
    ko.Initialize(File.DirDefaultExternal, "data.db", False)
    rp.GetSafeDirDefaultExternal("Connect Sqlite")
End Sub

private Sub Button1_Click
    'cek data
    If txtnama.Text ="" Then
        Msgbox("Masukkan Nama","info")
        Return
    End If

    If txtuser.Text ="" Then
        Msgbox("Masukkan Username","info")
        Return
    End If
    
    If txtpass.Text ="" Then
        Msgbox("Password","info")
        Return
    End If
    
    If txtno.Text ="" Then
        Msgbox("Masukkan No.Handphone","info")
        Return
    End If
    
    'checks if the data already exist in the database
    Public co As ResultSet
    co = ko.ExecQuery2("SELECT * FROM " & DBTableName & " WHERE nama = ? AND user = ? AND password = ? AND notelepon = ?", Array As String(txtnama.Text, txtuser.Text, epass, txtno.Text))
    If co.NextRow = True Then
        ToastMessageShow("Data Sudah Ada", False)
        Else
        ko.ExecNonQuery("INSERT INTO "& DBTableName &" (nama, user, password, notelepon) VALUES ('" & txtnama.Text &"','" & txtuser.Text & "','" & epass & "','" & txtno.Text & "')")
        'if yes display a MessageBox
        Msgbox("Data Berhasil di Simpan","info")
    
        'memanggil enkripsi
        encript
    
    Activity.Finish
    ko.Close
    StartActivity(act_login)
    End If
End Sub

Sub encript
    epass = B64.EncodeStoS(txtpass.Text, "UTF8")
End Sub

 

[Alam122]

I got this error message when i add user as primary key, how i can pass add data and just read it

 

[Jorge M A]

co = ko.ExecQuery2("SELECT * FROM " & DBTableName & " WHERE nama = ? AND user = ? AND password = ? AND notelepon = ?", Array As String(txtnama.Text, txtuser.Text, epass, txtno.Text))

Just select the record ONLY with the primary key data. What is your primary key?

 

[Alam122]

Just select the record ONLY with the primary key data. What is your primary key?

user

 

[Erel]

1. Big mistake to use ExecNonQuery instead of ExecNonQuery2 with parameterized command.
2. Big mistake to use Msgbox.

Watch the SQL video tutorial for more information: https://www.b4x.com/etp.html

The primary key must be unique.

You can use INSERT OR REPLACE instead of INSERT.