how to delete or edit a record in a recordset?

Databases
1

Hi.

I use recordsets for working with SQLite-DBs.

I make a query from a table like this:

dim data as recordset = DB.Sqlselect("Select * from something")

Now I go through every record in the Recordset (data) and check some things in It, what’s not possible to check with a sql-statement:

If data <> Nil Then While Not data.EOF 'check something data.MoveNext Wend end if

If I find a record, what does not support the things I checked, I want to delete it. I thought it works with data.deleterecord - but nothing happend. Also not if I try to change a field-value in the current record like data.idxfield(1).stringvalue = "something other"

Background: After selecting and delete some records, I put the data in to a HTML-table to print and save the selected query-result.

Question:
how can I delete a record and edit a field in a recordset? What’s my problem?

2

Try “select rowid, * from something”. The query needs to include the integer primary key from the table in order for the middleware (Database API) to get delete and update to work. “rowid” is an implicit primary key in SQLite. Many experienced SQLIte users feel that it’s always better to explicitly declare your own primary key for a table. I’ll leave that for another discussion.

3

You also need to call data.Edit first.

4

hello Kem please help me this is my code to make a database in mysql, but i really need to know how can i do to delete one record from my database. could yo help me please?

Dim db As New MySQLCommunityServer
Dim rs As RecordSet

db.host = “localhost”
db.port = 3306
db.DatabaseName = “xojo”
db.UserName = “root”
db.Password = “mayd”

dim n as String
dim a as String

n = txt1.Text
a = txt2.Text

If Not db.Connect Then
Msgbox “Could not connect to database”
Else

db.SQLExecute("INSERT INTO usuario (Nombre, Apellidos) VALUES ('"+n+"' , '"+a+"')")
db.SQLExecute( "COMMIT" )

End If

rs = db.SQLSelect(“SELECT * FROM usuario”)

If rs <> Nil Then
While Not rs.EOF
Listbox1.AddRow “”
ListBox1.Cell(Listbox1.LastIndex,0) = rs.Field(“Nombre”).StringValue
ListBox1.Cell(Listbox1.LastIndex,1) = rs.Field(“Apellidos”).StringValue

  rs.MoveNext
Wend

Else
If db.Error Then MsgBox(db.ErrorMessage)

db.close
rs.Close

End If

5

[quote=175507:@Orlando Alejandro Valencia Quiroz]hello Kem please help me this is my code to make a database in mysql, but i really need to know how can i do to delete one record from my database. could yo help me please?
[/quote]
You would use a SQL DELETE command
See http://dev.mysql.com/doc/refman/5.0/en/delete.html

6 
db.sqlexecute("DELETE FROM usario WHERE MyAutoIncrementField = XX")
  1. You should have an ID or AutoIncrement field to uniquely identify each record. If not, figure out what combination of fields uniquely identifies the record and use those in the WHERE clause.
7