Hi,
This is the weirdest problem I have encountered so far!
I have 3 TextFields who’s values get saved to a database when the user clicks on the save button.
However - When it saves to the database, it the first digit is 0 (zero), it gets stripped away.
Examples:
If I type 0987654321 It saves 987654321
If I type 0j0k0j however, the leading zero stays in place ???
It seems that if the TextField contains ONLY NUMBERS that start with a zero - it gets stripped away.
If it contains ANY letters - it works as expected.
Below is the code I am using to save to the database:
[code] fldDescription.text = fldDescription.text.Titlecase
dim sql as string
if fldDescription.text<>"" and fld2.text<>"" and fld3.text<>"" Then
sql=“Insert into Boxes (Description, Box, Key, Status, Type) Values (’”+sqlify(fldDescription.text)+"’,’"+sqlify(fld2.text)+"’,’"+sqlify(fld3.text)+"’)"
db4.sqlexecute (sql)
if db4.error then
db4_error
self.close
else
db4.commit
self.Close
Window1.Show
end if
else
Msgbox (“Please ensure all fields are completed!”)
end if[/code]
First (and again), you should not be using “sqlify” but a PreparedSQLStatement.
What does your sqlify method do?
And how are those database fields defined?
Finally, you have five fields but only three values?
Backing up what Kem said, besides the obvious question of seeing exactly what it is that you do to “sqlify” things, the the first thing I was going to ask you was how you had your database fields defined. May be a long shot, but I had a recent similarly bizarre problem with attempting to save the contents of text fields to a MS SQL database. Had no problem for weeks of operation until one string contained both a hyphen (“-”) and a dot (“.”). If the hyphen only or the dot only, no problem … but with both in the string, it refused to update the record. The answer in my case turned out to be changing the database field definitions from vchar to nvarchar. Never had a problem after that. The best answer we could surmise was some possible encoding issue was at work but I was never able to prove it (everything in the data chain was the standard UTF-8 as far as I could see).
Kem,
Im using sqlify only because I started my software from a very old example, years ago.
The database fields in question (Description, Box and Key) are set as STRING.
Whoops, the last 2 fields should have been deleted (mistake in posting).
So what does sqlify do?
And does it work if you rewrite like this?
if fldDescription.text<>"" and fld2.text<>"" and fld3.text<>"" Then
dim insertRec as new DatabaseRecord
insertRec.Column( "Description" ) = fldDescription.Text
insertRec.Column( "Box" ) = fld2.Text
insertRec.Column( "Key" ) = fld3.Text
db4.InsertRecord( "Boxes", insertRec )
if db4.error then
db4_error
self.close
else
db4.commit
self.Close
Window1.Show
end if
else
Msgbox ("Please ensure all fields are completed!")
end if
Hi Kem and Don,
I have edited my code so as not to use sqlify anymore - could you please tell me if the syntax looks ok?
[code]fldDescription.text = fldDescription.text.Titlecase
if fldDescription.text<>"" and fld2.text<>"" and fld3.text<>"" Then
db4.SQLExecute ("Insert into Boxes (Description, Box, Key) Values ((fldDescription.Text),(fld2.Text),(fld3.Text))
db4.SQLExecute (“Commit”)
if db4.error then
db4_error
self.close
else
db4.commit
self.Close
Window1.Show
end if
else
Msgbox (“Please ensure all fields are completed!”)
end if[/code]
Thank you for your patience.
Kem,
The code in SQLify Method is as follows:
SQLify(Source As String)
dim completed as string
completed=ReplaceAll(source, "'", "`" )
completed=ConvertEncoding(completed, encodings.WindowsANSI)
return completed
That will compile but the database will return an error. You are using the terms, for example, “fldDescription.Text”, but not the actual values within that field. The SQL engine won’t know what to do with that.
I’d give you the correction to that, but it’s dangerous and I’d rather you use another method like the one I posted above.
Ok,
I trust you totally as you have always been knowledgeable and helped me out.
I will try you code and see what happens 
Kem,
Just tried your code:
-
It saves to the database, but for some reason my ListBox no longer updates itself and I have to reload the Listbox again to see the change.
-
The leading zero is still stripped away.
If Kem’s suggestion (which from a structural standpoint is the correct approach) doesn’t yield the answer, then just for sh*ts and giggles, try commenting out the line completed=ConvertEncoding(completed, encodings.WindowsANSI) in your method for a moment and see what happens when you attempt to save to the db.
Don,
The code Kem suggested doesn’t use SQLify, so would that make a difference?
I will try it anyway - thanks
I think the original problem is that the data type is automatically detected. “0987654321” is saved as integer, but “0j0k0j” saved as text. So by using prepared statements you can define the value as text and it should save correct.
I can recreate your results, but only if the field is defined as an integer and not a string. Are you really sure about how the fields are defined?
don’t use ConvertEncoding(completed, encodings.WindowsANSI) here!
SQLite in Xojo is UTF-8, so you make extra conversions necessary.
Wow - so many answers at the same time
Kem,
This is the line of code which created the database:
db.SQLExecute("CREATE TABLE Boxes(SRRef INTEGER PRIMARY KEY, Location STRING, Box STRING, Key STRING)")
So the first column (column 0) is the only integer column, and our code only uses columns 1, 2, and 3
Christian,
Thanks, but as you can see - Kem’s code does not use SQLify, so if I am correct, that shouldn’t play a part? or would it?
You are correct, SQLify no longer plays a role.
What kind of database is this? SQLite?
OK, I was totally able to reproduce your results. The field type “STRING” is not valid and will lead to numeric fields in SQLite (don’t ask me why). The correct definition is “TEXT”.
You’re going to hate me saying this BUT:
REALSql
(Yes I know I should be using SQLite - but I originally started my software from a very old example that used REALSql).
I attempted to convert it to SQLite, but the database files are also SHA-512 hashed, and I got totally confused and decided against converting it.
RealSQL is SQLite, so the comment I posted just above yours still applies.