Problems with Null in VarChar fields

Hello!

I have an application running ADO against an Access database. All working perfectly in Delphi 2007 but when recompiling with Delphi 2009 I get the following eror:

When Updating fields of the type VarChar with a string of length zero is says something like this "Field xxx can not contain the value Null as the Required flag is set to True, enter a value in this field". (Translated from Swedish)

The assignment is:      ADOQuery.FieldByName('UnitSerialNumber').AsString := UnitSerialNumber;

And UnitSerialNumber is of type String. 

Seems like some conversion from "empty" string to Null value is invoked in Delphi 2009!?

Any ideas? 

regards, Mikael
0
Mikael
12/29/2008 2:36:00 PM
embarcadero.delphi.ado 597 articles. 1 followers. Follow

13 Replies
1493 Views

Similar Articles

[PageSpeed] 46

Here is a complete samle of code that goes wrong:

Procedure TForm6.Button1Click(Sender: TObject);
Var ADOConnection1: TADOConnection;
    ADOQuery1: TADOQuery;
Begin
   ADOConnection1 := TADOConnection.Create(Nil);
   ADOQuery1 := TADOQuery.Create(Nil);
   ADOConnection1.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\Ny6.mdb;Persist Security Info=False';
   ADOConnection1.LoginPrompt := False;
   ADOConnection1.Mode := cmShareDenyNone;
   ADOConnection1.Provider := 'Microsoft.Jet.OLEDB.4.0';
   ADOConnection1.Connected := True;
   ADOQuery1 := TADOQuery.Create(Self);
   ADOQuery1.Connection := ADOConnection1;
   ADOQuery1.Close;
   ADOQuery1.SQL.Clear;
   ADOQuery1.SQL.Add('Select * From Units Where UnitID = 1');
   ADOQuery1.Open;
   ADOQuery1.Edit;
   ADOQuery1.FieldByName('UnitX1GPRSIP').AsString := '';    <---- The problem is here
   ADOQuery1.Post;                                                             <---- Runtime error here
   ADOConnection1.Free;
   ADOQuery1.Free;
End;

Field UnitX1GPRSIP is declared as required in the database.

If  I change the empty string to a space character ' ' then it works.

If recompiled in Delphi 2007 it works fine with a blank string!

Any ideas?

Regards, Mikael
0
Mikael
12/30/2008 2:28:55 PM
Try this using QuotedStr.  You should then be able to insert a record with a blank string.  If the variable is not blank then sVariableName := QuotedStr(sVariableName).  If the value of sVariableName = '' then is should still work...sVariableName := QuotedStr('').  I did a quick test and the insert failed with a blank string without using QuotedStr. QuotedStr is in the SysUtils unit.
0
Carl
12/30/2008 2:39:56 PM
> {quote:title=Mikael Lenfors wrote:}{quote}
>    ADOQuery1.FieldByName('UnitX1GPRSIP').AsString := '';    <---- The problem is here
>    ADOQuery1.Post;                                                             <---- Runtime error here
//SNIP
> Field UnitX1GPRSIP is declared as required in the database.
> 
> If  I change the empty string to a space character ' ' then it works.

Your question makes absolutely no sense.

First, if the field is marked as required, you should put a value in it. If it is allowed to accept nothing (null), then don't mark it as required in the first place.

Second, the empty string ('') is not the same as a single space (' '). The empty string has a value of nothing (nil, or null), while the single space is a character (#32). They're not the same at all.
0
Ken
12/30/2008 2:41:24 PM
Good try but id does not work! When doing so it saves a string of two ' 
characters in the field. You can se this if you in my example add
ShowMessage(IntToStr(Length(ADOQuery1.FieldByName('UnitX1GPRSIP').AsString)));
after the .post command.It shows a length of 2!

Regards, Mikael

<Carl Harmon> skrev i meddelandet news:60791@forums.codegear.com...
> Try this using QuotedStr.  You should then be able to insert a record with 
> a blank string.  If the variable is not blank then sVariableName := 
> QuotedStr(sVariableName).  If the value of sVariableName = '' then is 
> should still work...sVariableName := QuotedStr('').  I did a quick test 
> and the insert failed with a blank string without using QuotedStr. 
> QuotedStr is in the SysUtils unit.
>
0
Mikael
12/30/2008 3:32:41 PM
I'm sorry I didn't explain clear enough. 

My problem is the following code works fine:

   S := 'Test';
   ADOQuery1.Edit;
   ADOQuery1.FieldByName('UnitX1GPRSIP').AsString := S;
   ADOQuery1.Post;

But the following crashes in D2009 but not in D2007.

   S := '';
   ADOQuery1.Edit;
   ADOQuery1.FieldByName('UnitX1GPRSIP').AsString := S;
   ADOQuery1.Post;

(In D2007 this one saves a blank string in the database but in D2009 it seems like it tries to converts the blank string into a Null value)

We have houndreds of these Access databases at different customers so changing the definition is not that simle and we want it to be compatible with both program versions (compiled with D2007 and D2009).

Regards, Mikael


> First, if the field is marked as required, you should put a value in it. If it is allowed to accept nothing (null), then don't mark it as required in the first place.
> 
> Second, the empty string ('') is not the same as a single space (' '). The empty string has a value of nothing (nil, or null), while the single space is a character (#32). They're not the same at all.
0
Mikael
12/30/2008 3:37:52 PM
Just tried exactly the same code against a SQL server database and it works perfectly in both D2007 and D2009, saving a blank string without errors!
Seems like this is a MS access problem only!

Mikael
0
Mikael
12/30/2008 4:12:51 PM
I see what you mean...I duplicated your results.  If you open the Access table in design mode and check these two properties for the field in question:

Required
Allow Zero Text

I believe you will find Required = True (Yes) and Allow Zero Text = False.  If this is the case then I don't think an empty string will be allowed.  I wonder if the property values for this field have changed.
0
Carl
12/30/2008 4:47:17 PM
Well no, both these properties are set to true! From MSAccess I have no problem to enter an empty string in the field (as I can fron D2007).

Mikael
0
Mikael
12/30/2008 4:52:47 PM
I did another interesting test, se code below:

ADOQuery1.Edit;
ShowMessage('<' + ADOQuery1.FieldByName('UnitName').AsString + '>');  <---  Shows <>, its really a empty string before!
If ADOQuery1.FieldByName('UnitName').IsNull Then
   ShowMessage('Before IsNull');                                                 <-- Does not show, meaning IsNull is False
S := '';
ADOQuery1.FieldByName('UnitName').NewValue := S;
ShowMessage('<' + ADOQuery1.FieldByName('UnitName').AsString + '>');  <---  Shows <>, meaning it's still a empty string!
If ADOQuery1.FieldByName('UnitName').IsNull Then
   ShowMessage('After IsNull');                                                <-- Does show, meaning IsNull is true
ADOQuery1.Post;
 
Conclusion, the assignment is really converted to a Null value.....

Regards, Mikael
0
Mikael
12/30/2008 5:03:07 PM
That is interesting.  I created a test project and had it working in Delphi 2007.  I converted it to Delphi 2009 and it quit working with the same problem you are having.  I checked QC and there is a bug report along with a workaround.

http://qc.codegear.com/wc/qcmain.aspx
Report ID No. 68045  

The bug report has a workaround and I got it working.  I couldn't figure out how to re-compile the unit but I made the needed changes, then copied the db.pas file to my project folder.  Re-built the project and now it is working.  I hope this helps.
0
Carl
12/30/2008 6:10:09 PM
Thanks a lot, it works! 

Strange it didn't pop up on my google search!?

About the recompile, I just added the path to ...\source\Win32\db to my Library path and rebiult my project.

Thanks, Mikael

> http://qc.codegear.com/wc/qcmain.aspx
> Report ID No. 68045
0
Mikael
12/30/2008 7:59:55 PM
"Ken White" wrote in message news:60792@forums.codegear.com...
>
> Second, the empty string ('') is not the same as a single space (' '). The 
> empty string has a value of nothing (nil, or null), while the single space 
> is a character (#32). They're not the same at all.

Actually that depends on the database, and in this case the drivers too. 
Strictly speaking an empty string is NOT the same as null. Null is not a 
value, it is a state. An empty string is still technically a string value.

Clearly there has been change that is treating the empty string to mean null 
and is clearing the database field to null instead of setting it to that 
empty string.

-- 
Wayne Niddery - TeamB (www.teamb.com)
Tips to Get Published: http://dn.codegear.com/article/38467
Winwright, Inc. (www.winwright.ca)
0
Wayne
12/31/2008 2:10:48 AM
.... but I do agree with you that, since it is a required field, he should be 
assigning some real value to it.

-- 
Wayne Niddery - TeamB (www.teamb.com)
Tips to Get Published: http://dn.codegear.com/article/38467
Winwright, Inc. (www.winwright.ca)
0
Wayne
12/31/2008 2:12:14 AM
Reply:

Similar Artilces:

Problem with NULL fields
when I retrieve data from MySql if one of the fields is null the rest of the rows in the same column get null values, I dont know why. i use Microsoft Visual Studio 2002 mysql-4.0.20d-win mysql-connector-net-1.0.0-beta.zip Ok i think i have a solution Now insteadof using "mysql-connector-net-1.0.0-beta.zip " I use "bytefx" and everything is working. Can u recommend me who is the best MySql connection? Yaron. About year ago I tried different MySql providers. But due to the problems they had I had to use Odbc.Net and MyODBC instead. Then, at...

Problem in extracting null value from a grid view field to add to a database table field
Hi,I have a grid view and I am extracting a field value from the gridview corresponding to the chosen record via a radio button. Now for a particular record the field in grid view in asp.net is showing null value (blank). However when I am extracting this field value to add it to a database table the field in the table is populated by "&nbsp". I would like the field to be populated with null or even "". I am not sure how to do this. The following approach is failing:            If SelectedGridRow.Cells(5).Text = &qu...

problems with a null database field
I'm displaying the data from a particular row of a database in a page.  In the code behind I have this:If Not IsDBNull(mate.EARLIEST_DATE) Then    LblERD.Text = mate.EARLIEST_DATEEnd IfI'm getting this error: Conversion from type 'DBNull' to type 'String' is not valid.But I'm not getting this on my page, but on one of the pages generated by VS.  I get the error when I run template.aspx, nd this is the source page: Source File: C:\WINNT\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\wap\b577f4fa\3ae10d8a\App_Code.qtk6xv56.4.vb    Line: 1351&nbs...

NULL field matching problem...
I am working on a script that parses a csv file. The actual field delimiters in the file are pipes |. The problem I am running into is that if the field is empty, then the database is getting a '0' rather than 'NULL' as it should. The column is setup properly with NULLs allowed and the default value of NULL. Here is a sample of the file being parsed... SANDBOX|0|750|0.0|junk|N|0.0|9000/800/RP7410|Unknown|B.11.11|junk|junk|s erial number|Sandbox|hostname|0||CHTSAN|UNKNOWN|ROBERT C|JONES SR.| The problem on this line is the field between '0' and 'CHT...

Convert Delphi 5 ADO App to Delphi 2007 ADO.NET
I have an appliction tha that is writen in Delphi 5 with ADOConnetion, ADOTable, and ADOQuery components. I need to upgrade to Delphi 2007 and use ADO.NET. I have the program building in with Delphi 2007. Is there anything I need to do to convert the ADO to ADO.NET? > I need to upgrade to Delphi 2007 and use ADO.NET. Why ? -- With best regards, Dmitry Arefiev AnyDAC (www.anydac.com) - Oracle, MS SQL Server, IBM DB2, MySQL, Firebird, Interbase, PostgreSQL, Sybase ASA, SQLite, MS Access, DBX, ODBC, InMem - universal high-speed native data access engine Reasons for ...

Checking for Null values in ADO field
Hello, I am trying to check whether or not HERE is my some of my code: strSQL="SELECT ..." Dim cn As OleDbConnection = New OleDbConnection(ConfigurationSettings.AppSettings("ConnectionString")) cn.Open() Dim cmdHeader As OleDbCommand = New OleDbCommand(strSQL, cn) Dim rdHeader As OleDbDataReader rdHeader = cmdHeader.ExecuteReader So you can see that I am loading a OleDbDataReader from my table. This code works fine. Now I want to check to see if one of the fields is null. I have tried ALL of the following: If Val(0 & rdH...

ADO.NET to XML and null field
I am getting XML version of a Dataset using ds.GetXml(). If the db field is null or empty, how do I get the XML to emit a <fieldName /> rather then nothing? Thanks. Here's a couple articles on why it happens and some work arounds. http://support.microsoft.com/default.aspx?scid=kb;EN-US;296393 http://support.microsoft.com/default.aspx?scid=kb;en-us;317961 Hmmm neither of those seem like a good work around for this problem - i would even go as far as to say bug in ADO.NET. Does anyone have any suggestions for me before I code my own function to interate through a RS and ...

Problem on ASA with Delphi via ADO
Hello, I just installed ASA 60 days trial and I am testing with Delphi 6 and ADO, using TADODataset components. I am getting the following problems: when I post any record, trailing blanks are inserted in every TStringField (VARCHAR in ASA). When I post records with TMemoField or TBlobField (LONG VARCHAR), it raises an "Count field incorrect". I did't see this behaviour in other servers. Regards, Frank Hi, mybe it's help: //Procedure tForm1.SaveBtnClick(Sender:TObject); // //var // fs : TFileStream; // bs : TBlobStream; // //Begin //with Table1...

Delphi 5+ ADO ASAProv Problem
Hello, I try to connect from Delphi to ASA 7.03 with ADO. Via the ODBC Provider everthing seems to work. When I try to use ASAProv I can connect, but after connect the mode property of the connection is forced to cmUnknown and any attempt to open a result set lead to the error "Object or provider is not capable of performing requested operation". This is the same if I use the Connection string dialog or if connect manually ADOConnection1.ConnectionString:='Provider=ASAProv.70;Password=sql;Persist Encrypted=True;Persist Security Info=True;User ID=dba;Data Sourc...

Problem updating null decimal fields
Hi,I have a FormView with standard insert /update strings. I have a problem during update where if I leave the text box for the decimal database fields blank (database values are null) and try to update I get the Input string was not in a correct format. if I type a number in these boxes everything updates fine. I am not sure why I can not leave the textbox values blank. It almost seems like the text box is populated with "NULL" and then during update it is trying to write a string into a decimal field but I am not sure. Thanks...

Problem with SQL Update when a field is NULL
I am having a problem with an update statement. I am using compareallvalues with a SQL data source. When one of the old values is null the update does not go through. i.e. This does not work if something is NULL UPDATE myTable SET something = @somethingWHERE ID = @o_ID AND something = @o_something Thank You,Jason The reason is ANSI SQL NULL is an unknown so comparing NULL to NULL is not NULL but an unknown.  Try the link below for work around.  Hope this helps. http://www.sqlservercentral.com/columnists/mcoles/fourrulesfornulls_printversion.aspKind regards,Gift Peddie Will ...

Problems, problems, problems
Name: Hans Halvorsen Email: capricorn_at_online.no Product: Thunderbird Summary: Problems, problems, problems Comments: Hi, I downloaded Thunderbird some time ago, and I was over-satisfied whit it and how well it worked. In fact it worked 100% PERFECTLY UNTIL I got an newer update version of it. Since then I' ve had nothing BUT trouble! I've checked adresses, notes, password, deleted accounts and made new one's, checked sign-ins and sign-outs, passwords inside and out, upside and down, so many times that my frustration now is about to turn into pure and major ...

problem with null in not null column.
Hello. How I can correct my null in "not null column" ? Have sybase any tools for correcting this problem ? It because of power failure :(( helppp... > How I can correct my null in "not null column" ? Have sybase any tools > for correcting this problem ? Ummmm ... 1) Don't try to put a NULL into a column that does not allow NULL's. or 2) Rebuild the table so that your columns will accept NULL's. > It because of power failure :(( Huh? Could you explain this one? Right off the bat I can't see any reason why the loss...

Problems problems and problems.
Name: A.A. Product: Firefox Summary: Problems problems and problems. Comments: I am not specific, but in short I can not use This site to listen music anymore, while I can in IE explorer. Just to let you guys know, new version messed something up. http://hypem.com/ Browser Details: Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US; rv:1.9.0.5) Gecko/2008120122 Firefox/3.0.5 From URL: http://hendrix.mozilla.org/ Note to readers: Hendrix gives no expectation of a response to this feedback but if you wish to provide one you must BCC (not CC) the sender for them to see it. ...

Web resources about - Problems with Null in VarChar fields - embarcadero.delphi.ado

Problem novel - Wikipedia, the free encyclopedia
Working class, or proletarian novels are often also social problem novels . This was in many ways a reaction to rapid industrialization , and ...

Martin Place, we have a problem
A change in global conditions means that the positive economic momentum provided by a weakened Aussie dollar could soon come to a screeching ...

Apple’s App Store has a copyright infringement problem, business owner claims
... iOS apps scraping content from the web and packaging it in paid apps. While developer relations issues often get a lot of attention, the problem ...

Marco Rubio accidentally stumbles on the problem with religious pandering
... plan for the universe and for our lives that we are just not going to know the answer to. God’s ways are not our ways.” There are two problems ...

Recharge Wrap-up: Formula E battery award, Tesla NOx problem
Filed under: Government/Legal , Green , Motorsports , Plants/Manufacturing , Tesla , Emissions , Fuel Efficiency , Technology , Electric Williams ...

Open Thread: “Whisper Campaigns and Zipper Problems”
... attach names to narratives like this, then JEB! is TOAST! (at least in 2016). And second, the “well known” rumors that Rubio’s financial problems ...

Pandora CEO: This is why giving people access to music that's 'free all the time' is a problem
... the power to patch the leak. If they don’t, genuine attempts to get consumers to pay for music will increasingly fall on deaf ears. This problem ...

Just Cause 3 PC graphics problems reported
Fans complain of visual bugs and glitches as developer cites issues with latest AMD graphics drivers.

Trump: Obama Is ‘Really Out Of It’ When Calling Climate Change The ‘Greatest Problem Confronting Our ...
Trump: Obama Is ‘Really Out Of It’ When Calling Climate Change The ‘Greatest Problem Confronting Our Country’

Obama: Climate Problem Can Be Solved
... Barack Obama said Tuesday global warming posed economic and security risks that had to be tackled immediately, but insisted the climate problem ...

Resources last updated: 12/3/2015 3:07:55 AM