I am attempting to import data into an SQLite database I created.
Here is the table DDL
CREATE TABLE MASTER (
ID_ADDRESS TEXT NOT NULL,
firstNAME_1 TEXT COLLATE NOCASE DEFAULT '',
lastNAME_1 TEXT COLLATE NOCASE DEFAULT '',
firstNAME_2 TEXT COLLATE NOCASE DEFAULT '',
lastNAME_2 TEXT COLLATE NOCASE DEFAULT '',
streetADDRESS TEXT COLLATE NOCASE DEFAULT '',
City TEXT COLLATE NOCASE DEFAULT '',
State TEXT COLLATE NOCASE DEFAULT '',
ZipCode TEXT COLLATE NOCASE DEFAULT '',
Country TEXT COLLATE NOCASE DEFAULT '',
Phone TEXT COLLATE NOCASE DEFAULT '',
Email TEXT COLLATE NOCASE DEFAULT '',
SELECTED INTEGER NOT NULL DEFAULT 1,
SUSPENDED INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY ( ID_ADDRESS) )
here is the routine that does an INSERT or UPDATE [right now I am only attempting INSERTS]
Public Sub addMaster_Record(key as string, address_input() as string)
// Add or Update the Master Record
Dim i As Integer
Dim SQL As String
Dim rs As RecordSet
Dim ps As SQLitePreparedStatement
//
If SQL_MASTER_INSERT="" Then create_MASTER_SQL // creates template SQL statement, since it never changes
//
If key="*" Then key=GenerateUUID
SQL= _
"SELECT count(*) as CNT "+_
" FROM "+tableMASTER+_
" WHERE ID_ADDRESS='"+key+"'"
rs=db.SQLSelect(SQL)
If rs.field("cnt").integervalue=0 Then
//
// new record
//
SQL=SQL_MASTER_INSERT
//
ps = DB.Prepare(SQL)
//
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.Bind(0,key)
debug "Insert with Key "+key
For i=0 To address_INPUT.Ubound
ps.BindType(i+1, SQLitePreparedStatement.SQLITE_TEXT)
ps.Bind(i+1, address_INPUT(i))
// address_INPUT(0) maps to Bind[1] hence the +1
Next i
Else
// Update record [not yet implemented]
End If
//
Call ps.SQLExecute(sql)
debug db.ErrorMessage
End Sub
SQL_MASTER_INSERT statement is this
INSERT INTO MASTER(ID_ADDRESS,firstNAME_1,lastNAME_1,firstNAME_2,lastNAME_2,streetADDRESS,City,State,ZipCode,Country,Phone,Email) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)
the error I get is UNIQUE constraint failed: MASTER.ID_ADDRESS
which is the PK, and I understand it must be unique… the problem is that it IS unique
I have in the past been accused of not supplying enough information, so forgive me if I went overboard this time