When I edit, insert null values ??Xojo

Good morning everyone, I hope I can help to understand this problem.
I have my code to edit a record, but when running, I do not save the new values ??and I inserted all null values ??..

my code

Dim EDITA As RecordSet EDITA=XE.SQLSelect("SELECT * FROM SAIT_2 WHERE KEY_ID_SESION_EMPLEADO='" + Trim(ID_SESION.Text) + "'AND CONTROL_CLAVE='" + Trim(CLAVE_CONTROL.Text)+ "'") While Not EDITA.EOF EDITA.Edit EDITA.Field("ULTIMO_GRADO_ESTUDIOS").StringValue=Trim(ULTIMOGRADOESTUDIOS.Text) EDITA.Field("INSTITUCION_ULTIMO").StringValue=Trim(ULTIMOINSTITUCION.Text) EDITA.Field("DOCUMENTO_AVALE_ULTIMO").StringValue=Trim(ULTIMODOCUMENTOAVALE.Text) EDITA.Field("DIPLOMADOS").StringValue=Trim(DIPLOMADOS.Text) EDITA.Field("INSTITUCION_DIPLOMADOS").StringValue=Trim(DIPLOMADOSINSTITUCION.Text) EDITA.Field("DOCUMENTO_AVALE_DIPLOMADOS").StringValue=Trim(DIPLOMADOSDOCUMENTOAVALE.Text) EDITA.Field("CURSOS").StringValue=Trim(CURSOS.Text) EDITA.Field("INSTITUCION_CURSOS").StringValue=Trim(CURSOSINSTITUCION.Text) EDITA.Field("DOCUMENTO_AVALE_CURSOS").StringValue=Trim(CURSOSDOCUMENTOAVALE.Text) EDITA.Field("GRADO_INGLES").StringValue=Trim(INGLES.Text) EDITA.Field("INSTITUCION_INGLES").StringValue=Trim(INGLESINSTITUCION.Text) EDITA.Field("DOCUMENTO_AVALE_INGLES").StringValue=Trim(INGLESDOCUMENTOAVALE.Text) EDITA.Field("RELIGION").StringValue=Trim(RELIGIONCUAL.Text) EDITA.Field("SINDICATO").StringValue=Trim(SINDICATOCUAL.Text) EDITA.Field("CLUB_SOCIAL").StringValue=Trim(CLUBSOCIALCUAL.Text) EDITA.Field("ESTATURA").StringValue=Trim(ESTATURA.Text) EDITA.Field("PESO").StringValue=Trim(PESO.Text) EDITA.Field("ESTADO_DE_SALUD").StringValue=Trim(ESTADODESALUD.Text) EDITA.Field("FECHA_ULTIMA_CONSULTA").StringValue=Trim(ULTIMACONSULTA.Text) EDITA.Field("MOTIVO_CONSULTA").StringValue=Trim(MOTIVOULTIMACONSULTA.Text) EDITA.Field("CIRUGIA").StringValue=Trim(INTERVENCIONQUIRURQUICA.Text) EDITA.Field("ENFERMEDAD_CRONICA").StringValue=Trim(ENFERMEDADCRONICA.Text) EDITA.Field("ANTECEDENTES_FAMILIARES").StringValue=Trim(ANTECEDENTESFAMILIARES.Text) EDITA.Field("MOTIVO_CIRUGIA").StringValue=Trim(MOTIVOCIRUGIA.Text) EDITA.Field("MOTIVO_ENFERMEDAD").StringValue=Trim(MOTIVOENFERMEDAD.Text) EDITA.Field("FAMILIAR_PADECE").StringValue=Trim(FAMILIARQUEPADECE.Text) EDITA.Field("FUMA_FREC").StringValue=Trim(TABACOFREC.Text) EDITA.Field("FREC_TOMA").StringValue=Trim(BEBIDAFREC.Text) EDITA.Field("SITUACIONES_FUMA").StringValue=Trim(SITUACIONESTABACO.Text) EDITA.Field("SITUACIONES_TOMA").StringValue=Trim(SITUACIONESBEBIDA.Text) EDITA.Field("CANTIDAD_FUMA").StringValue=Trim(CANTIDADTABACO.Text) EDITA.Field("CANTIDAD_TOMA").StringValue=Trim(CANTIDADBEBIDA.Text) EDITA.Field("COMPLEXION").StringValue=Trim(COMPLEXION.Text) EDITA.Update EDITA.MoveNext Wend XE.Commit

There’s something I do wrong?

Thanks all

Is it possible that your textfields (I assume that you are attempting to place textfield data in the record) are out of scope where you are doing this operation? That would indeed result in inserting null values.

And you could probably do this all in the db with one update statement

It would be nice to be able to insert an Empty String or NULL into a field via the built in classes, RecordSet and DatabaseRecord. Currently an empty string goes in as a NULL, from my experience. I don’t think it’s possible to use the mentioned classes and insert an empty string.

Thanks all
well, all the TextField containing data, and I want to do is an update on values ??that are already in the database, but instead of edit, modify all the columns to Null

Someone help me with this. I have not been able to resolve

Not sure what database you are using, but with MS SQL Server, I use a prepared statement to set table column fields to NULL like this (sets the value of the field “AD” in the table “Notes” to NULL value where the condition “CaseID” field equals the integer value of the variable modelID):

Dim db As SQLDatabaseMBS
Dim sql As String = _
  "UPDATE Notes                 " + _
  "         SET AD = :pos       " + _
  "  WHERE CaseID = :cID;"
  
dim v as Variant = db.Prepare(sql)
  
dim p as SQLPreparedStatementMBS = v
  
p.BindType("pos", SQLPreparedStatementMBS.kTypeNull)
p.Bind("pos",Nil)
p.BindType("cID", SQLPreparedStatementMBS.kTypeLong)
p.Bind("cID", modelID)
  
p.SQLExecute
  
If db.Error Then
    MsgBox "Database Error: " + db.ErrorMessage    
else    
    db.Commit   // Commit the recordset to the database
End If

You’ll note that I’m using the MBS SQL plugin with what I do here, but you can easily convert those statements to the Xojo plugin equivalents … the structure of the code block stays the same.

And here’s another way of setting db fields to NULL without using prepared statements (although it runs the risk of SQL injection and personally, I’ve gotten away from using this because of that security concern … as well as the fact that prepared statements don’t have to have the literals (’ and ") escaped to keep the syntactic integrity of the SQL string intact)

Dim rsXSEC As RecordSet = db.SQLSelect("SELECT * FROM xsecImages INNER JOIN caseModel ON ((xsecImages.CaseID = caseModel.CaseID) AND (caseModel.CaseName='" + _
  caseName + "') AND (caseModel.Revision='" + caseRevision + "'))")   // XSEC
  
 // get the rowID of the current record for usage in operations below
 rowID = rsXSEC.Field("CaseID").StringValue
    
 sql = "UPDATE xsecImages SET ImgX1UR = NULL, ImgX1UL = NULL, ImgX1LH = NULL, ImgX2UR = NULL, ImgX2UL = NULL, ImgX2LH = NULL, " + _
    "ImgX3UR = NULL, ImgX3UL = NULL, ImgX3LH = NULL, ImgX4UR = NULL, ImgX4UL = NULL, ImgX4LH = NULL, TitleX1 = NULL, TitleX2 = NULL, TitleX3 = NULL, TitleX4 = NULL WHERE CaseID = " + rowID   // NOTE: rowID must be dimensioned as a string in order for it to be interpreted as a number in the sql string when executed
    
 db.SQLExecute(sql)
    
 db.Commit   // Commit the recordset to the database

If db.Error Then
    MsgBox "Database Error: " + db.ErrorMessage    
else    
    db.Commit   // Commit the recordset to the database
End If

VERY curious why you’d do this this way instead of something like

dim p as SQLPreparedStatementMBS = SQLPreparedStatementMBS( db.Prepare(sql) )

That one’s easy … because Christian Schmitz suggested it to me as the way to go … I was initially surprised at the usage of a variant in that scenario as well … but it sure works flawlessly! I can’t honestly say I can technically explain the difference.

Here’s one (of many) examples in the MBS docs that show the usage of prepared statements:

dim db as SQLDatabaseMBS // your db connection
dim sql as string = "Insert into test_tbl(fid, fvarchar20) values(:fid, :fvarchar20)"
dim v as Variant = db.Prepare(sql)
dim p as SQLPreparedStatementMBS = v

p.BindType("fid", SQLPreparedStatementMBS.kTypeLong)
p.BindType("fvarchar20", SQLPreparedStatementMBS.kTypeString)
p.Bind("fid", 2345)
p.Bind("fvarchar20", "Hello World by name")

p.SQLExecute

I see that now …