# Recordset is not editable because no primary key exists

**URL:** <https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962>\
**Category:** Databases\
**Created:** [June 18, 2013, 3:30pm UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962 "2013-06-18T15:30:36Z")\
**Posts on this page:** 9\
**Page:** 3

<div class="post-metadata">

**Author:** ![Paul\_Lefebvre](https://forum.xojo.com/user_avatar/forum.xojo.com/paul_lefebvre/32/3152_2.png) [@Paul\_Lefebvre](https://forum.xojo.com/u/Paul_Lefebvre)\
**Post date:** [July 25, 2013, 6:29pm UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/41 "2013-07-25T18:29:44Z")

</div>

Remember, SQLite is a bit [loosey-goosey](http://www.merriam-webster.com/dictionary/loosey-goosey) about data types, especially when compared to other databases.

[http://www.sqlite.org/datatype3.html](http://www.sqlite.org/datatype3.html)

---

<div class="post-metadata">

**Author:** ![Carl\_Hogue](https://forum.xojo.com/letter_avatar_proxy/v4/letter/c/35a633/32.png) [@Carl\_Hogue](https://forum.xojo.com/u/Carl_Hogue)\
**Post date:** [July 26, 2013, 1:00am UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/42 "2013-07-26T01:00:41Z")

</div>

[quote=22415:@Tim Hare]Because your recordset does not contain the primary key column.  
Not on thise line, probably. Where did the type mismatch show up?[/quote]  
I finally got it all sorted out. After I changed Select ColumnName to Select PrimaryIndex,ColumnName, I neglected to change the IdxField number in the record set, thus the data type mismatch error. After correcting that problem, everything started working as expected.

---

<div class="post-metadata">

**Author:** ![Emile\_Schwarz](https://forum.xojo.com/letter_avatar_proxy/v4/letter/e/90ced4/32.png) [@Emile\_Schwarz](https://forum.xojo.com/u/Emile_Schwarz)\
**Post date:** [July 26, 2013, 7:40am UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/43 "2013-07-26T07:40:56Z")

</div>

Carl,

I too make all sorts of mistakes, especially after code copy/paste.

---

<div class="post-metadata">

**Author:** ![Carl\_Hogue](https://forum.xojo.com/letter_avatar_proxy/v4/letter/c/35a633/32.png) [@Carl\_Hogue](https://forum.xojo.com/u/Carl_Hogue)\
**Post date:** [July 26, 2013, 2:23pm UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/44 "2013-07-26T14:23:25Z")

</div>

[quote=22745:@Emile Schwarz]Carl,

I too make all sorts of mistakes, especially after code copy/paste.[/quote]  
In my case it was an oversight, I didn’t think to change the field reference after changing the select statement. The primary key is an integer whereas the data fields are either strings or pictures, thus the data type error.

---

<div class="post-metadata">

**Author:** ![Bryan\_Dodson](https://forum.xojo.com/letter_avatar_proxy/v4/letter/b/ba8739/32.png) [@Bryan\_Dodson](https://forum.xojo.com/u/Bryan_Dodson)\
**Post date:** [June 1, 2016, 3:16pm UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/45 "2016-06-01T15:16:35Z")

</div>

I am having the same problem with Microsoft SQL server. In the first SQL statement in the code below that creates the recordset rs, the field ID is an autoindex primary key, but when I attempt to edit a field in the recordset I get the error “The RecordSet cannot be edited because the uniqueness of its rows cannot be verified.”

dim i as int64  
dim s as string  
Dim InfoWindDB as MSSQLServerDatabase  
Dim RPFstatisticsDB as MSSQLServerDatabase  
Dim sql As String  
Dim rs As RecordSet  
Dim rs2 As RecordSet

InfoWindDB = New MSSQLServerDatabase  
InfoWindDB.Host = “INFOwindServr”  
InfoWindDB.UserName = “idiv\_user”  
InfoWindDB.Password = “pwd”  
InfoWindDB.DatabaseName = “Industrial\_Division”

If InfoWindDB.Connect Then  
s = “Connected to MS SQL Server”  
Else  
s = "Error connecting to MS SQL Server: " + InfoWindDB.ErrorMessage  
End If

RPFstatisticsDB = New MSSQLServerDatabase  
RPFstatisticsDB.Host = “RPFserver”  
RPFstatisticsDB.UserName = “sa”  
RPFstatisticsDB.Password = “pwd”  
RPFstatisticsDB.DatabaseName = “RPFstatistics”

If RPFstatisticsDB.Connect Then  
s = “Connected to MS SQL Server”  
Else  
s = "Error connecting to MS SQL Server: " + RPFstatisticsDB.ErrorMessage  
End If

// get a recordset of all records from ApplicationData

sql = “SELECT ID, TurbineName, [Turbine Id] FROM tblApplicationParameters WHERE isnull([Turbine ID],-1) =-1”  
rs = RPFstatisticsDB.SQLSelect(sql)  
s=rs.field(“TurbineName”).StringValue

while rs.eof\<\>true  
SQL=“SELECT [Turbine Id], [Turbine Name] FROM [WIND3 MAIN TURBINE] WHERE ([Turbine Name] like '” + s + “’)”  
rs2=InfoWindDB.SQLSelect(sql)  
If rs2 \<\> Nil Then  
rs2.MoveFirst  
i=rs2.Field(“Turbine Id”).IntegerValue

```
  rs.Edit
  If RPFstatisticsDB.Error Then
    MsgBox("Error: " + RPFstatisticsDB.ErrorMessage)
    Return
  End If
  rs.field("Turbine ID").IntegerValue=i
  rs.update
  rs2.close
  rs.MoveNext
end if

```

wend

---

<div class="post-metadata">

**Author:** ![Joost\_Rongen](https://forum.xojo.com/letter_avatar_proxy/v4/letter/j/a9a28c/32.png) [@Joost\_Rongen](https://forum.xojo.com/u/Joost_Rongen)\
**Post date:** [June 1, 2016, 3:50pm UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/46 "2016-06-01T15:50:28Z")

</div>

```auto
rs.edit rs.update
```

Since long time I donb’t do this anymore, had too much issues with leaving the responsibility to the recordset-object.

I prefer having `INSERT INTO` and `UPDATE` SQL as prepared statements.  
Within transactions you can execute a bunch of queries safe and fast.

---

<div class="post-metadata">

**Author:** ![Jeff\_Tullin](https://forum.xojo.com/user_avatar/forum.xojo.com/jeff_tullin/32/693_2.png) [@Jeff\_Tullin](https://forum.xojo.com/u/Jeff_Tullin)\
**Post date:** [June 1, 2016, 4:16pm UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/47 "2016-06-01T16:16:35Z")

</div>

Starter for 10

update table tblApplicationParameters  
set [Turbine ID] = (  
select max([Turbine ID] from [WIND3 MAIN TURBINE] WHERE ([Turbine Name] =tblApplicationParameters .TurbineName)  
where  
TurbineName in (select [Turbine Name] from [WIND3 MAIN TURBINE] )  
and [Turbine ID] is null;

---

<div class="post-metadata">

**Author:** ![Bryan\_Dodson](https://forum.xojo.com/letter_avatar_proxy/v4/letter/b/ba8739/32.png) [@Bryan\_Dodson](https://forum.xojo.com/u/Bryan_Dodson)\
**Post date:** [June 2, 2016, 7:28am UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/48 "2016-06-02T07:28:07Z")

</div>

Thanks Jeff and Joost. This solves my problem, and the update command seems to have additional benefits.

---

<div class="post-metadata">

**Author:** ![system](https://forum.xojo.com/uploads/default/original/1X/455b4dcc0e61630e9f81940004ddffa49c432a46.png) [@system](https://forum.xojo.com/u/system)\
**Post date:** [October 29, 2020, 4:42pm UTC](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962/49 "2020-10-29T16:42:54Z")

</div>



[Previous page](https://forum.xojo.com/t/recordset-is-not-editable-because-no-primary-key-exists/8962.md?page=2)