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