Stringvalue of NULL = "0" ???

I am populating a textfield from a sqlite database. If the field is NULL, a 0 (zero) appears in the textfield. Is this the way it is supposed to work? It is not hard to work around it, but it could come back to bite me if a field I am not expecting to be NULL is.

Relevant code would be useful

For a NULL field,

rs.Field(“fieldname”).Value.IsNull = True
rs.Field(“fieldname”).IntegerValue = 0
rs.Field(“fieldname”).StringValue = “”

rs.Field(“fieldname”).Value.IsNull = True

I think this will not work. You query the Value and get a Variant. Than you set IsNull to true, but that does not get back to the field.

[quote=53328:@Christian Schmitz]rs.Field(“fieldname”).Value.IsNull = True

I think this will not work. You query the Value and get a Variant. Than you set IsNull to true, but that does not get back to the field.[/quote]
I think Tim meant to use those as comparisons.

My original problem arose because I have a text field that would normally be empty, but where a user can enter a numeric value. The associated database is populated using Tim’s bottom example ( .StringValue = “”). In the window.open, there was a line of code

TextField.Text = rs.Field(“fieldname”).StringValue

This caused a 0 to appear in the text field. My work around was to test for IntegerValue < 1 then TextField.Text = “”

Check your database. I suspect the column isn’t really NULL.

I’m using the paid version of SQLiteManager and it shows the value as NULL.

I just tried it in Xojo 2013r3.3 using both REALSQLdatabase and SqliteDatabase, and I get the values I expect. StringValue is “”, not “0”.

I don’t know what I am doing to cause the problem, but it persists. In fact there are two places in my program where it happens. I am using 2013r3.3 with the built-in sqlite. I am wondering if it comes from this line in my app.open when I create the database and populate the setup fields:

[quote]
ConfigDB.SQLExecute “INSERT INTO Setups (Names, Valu) VALUES (‘LogInterval’, ‘’)”[/quote]

The value I am passing is two single quotes instead of two double quotes. Changing it to four sets of double quotes brought the same results. There was database error when the code was executed.

[quote=53646:@Dean Davidge]
The value I am passing is two single quotes instead of two double quotes. Changing it to four sets of double quotes brought the same results. There was database error when the code was executed.[/quote]
Thats not null
Thats an empty string
Null is “no value of any kind - even an empty string - has been assigned”

NULL would be
ConfigDB.SQLExecute “INSERT INTO Setups (Names) VALUES (‘LogInterval’)”

Thanks Norman. That explains some of what is happening, but doesn’t solve my problem. Once a field has a value, even if it is an empty string, is it possible to make it Null again?

I’ve used RB since version 5 and never seen the problem of empty strings returning a zero before. Should I file a bug report for this and send my code to Xojo?

I hate to even ask this, but… are you sure you’re looking at the right file in SQLiteManager? I know I’ve done it before, looking at the file I thought my code was using, only to find out it wasn’t. And yes, you can explicitly insert a NULL into a field. But you would have to code for it, which I doubt you have.

[quote=53667:@Dean Davidge]Thanks Norman. That explains some of what is happening, but doesn’t solve my problem. Once a field has a value, even if it is an empty string, is it possible to make it Null again?

I’ve used RB since version 5 and never seen the problem of empty strings returning a zero before. Should I file a bug report for this and send my code to Xojo?[/quote]

Sure - use a sql execute
update table set field = NULL where field = ‘’