Zero length strings, SQL Server6.0/VB4.0a and spaces 
Author Message
 Zero length strings, SQL Server6.0/VB4.0a and spaces

Quote:

>On a directly opened SQL Server database
>eg.
>    Set Db = OpenDatabase("", False, False,ConnectString)
>where connectstring is an ODBC connectstring for an SQL Server
>database
>doing the following
>    TestSet.Edit
>            TestSet("testfield") = ""
>    TestSet.Update
>will result in a single space being stored in the field, not a zero
>length string. The fields on the SQL Server are defined as being
>allowed to accept null values.

>I know there are additional considerations regarding zero length
>strings and Ms Access databases and our Access2.0 database (once the
>AllowZeroLength property for each field has been set) works fine.
>Can anybody shed any light on the problem?, Is it a bug?
>Whilst it is relatively easy to fix (testing for empty string and then
>'Nulling' it on save) does anybody know a better way?
>--
>Philip Harvey
>Project Manager
>Business Development Ltd.




Mon, 04 Jan 1999 03:00:00 GMT  
 Zero length strings, SQL Server6.0/VB4.0a and spaces

The following is from the Transact SQL Reference:

"Note  An empty string is not considered the same as NULL. An
empty string with no characters, supplied as ' ', will insert a
single blank space."

There really isn't a shortcut short of passing the data to a
stored procedure and then TRIMing the result. If you TRIM an
empty string in a stored procedure the result will be NULL.
I suppose you could do this in SQLPassthrough mode as well, but I
haven't tried that.

Quote:

> [snip]

> doing the following

>         TestSet.Edit
>                 TestSet("testfield") = ""
>         TestSet.Update

> will result in a single space being stored in the field, not a zero
> length string. The fields on the SQL Server are defined as being
> allowed to accept null values.
> [snip]

> Can anybody shed any light on the problem?, Is it a bug?

> Whilst it is relatively easy to fix (testing for empty string and then
> 'Nulling' it on save) does anybody know a better way?

--
Karl Costenbader, President
Competent Consulting
Sacramento, CA

Home Page: http://www.competent.com


Tue, 05 Jan 1999 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. ODBC, SQL Server6.5, & VB4.0a (16bit)

2. Zero length strings in vb4

3. Bound VB4 TextBoxs, Nulls and Zero-Length Strings

4. VB4.0 Field can't be a zero-length string

5. Allow Zero Length String in a field using SQL

6. Accessing SQL Server6.5 with RDO in VB4.0

7. Convert Zero-Length String to Null

8. Replace Function returns Nothing instead of Zero-length string

9. ADO - zero length strings

10. Nulls / Zero Length Strings -Newbie Question

11. Newbie Question - Nulls vs zero length strings ???

12. Help please, with Zero-Length String !!

 

 
Powered by phpBB® Forum Software