Recordset is not editable because no primary key exists

Databases
41

Remember, SQLite is a bit loosey-goosey about data types, especially when compared to other databases.

http://www.sqlite.org/datatype3.html

42

[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.

43

Carl,

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

44

[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.

45

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

46 
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.

47

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;

48

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