[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1088
  • Last Modified:

VB NET + Access mdb file: Nullable fields with Allow Zero Length Strings set to false

Hello:

I have an mdb file with a table with fields that I want to allow null values in, but that I don't want to allow zero length strings in.

I have a form in a VB .NET Windows Form project whose controls are bound to this table. Some are text fields, some and comboboxes pulling lists from lookup tables.

I am building my own adapters from scratch so I can change things in code dynamically if that makes a simpler solution.


I want an empty textbox or an empty comobox (one whose display text is blank) to be considered vbNull instread of "" so they don't cause update errors on those columns which can be null but not allowing zero length strings. Perhaps there is a better solution?

How do other people handle updating nullable non-zero string length columns based on textboxes and controls whose empty values are "" and not vbNull?

Thanks!

0
majnun
Asked:
majnun
  • 16
  • 7
1 Solution
 
majnunAuthor Commented:
Well, I'm assuming I want to distinguish between empy strings and nulls, but maybe it doesn't make any real difference... Should I change my mdb file so that fields that can be null I also allow zero length strings in?

What are the pros/cons of:

Allowing Nullable Zero Length Strings?

Allowing Nulls but not Zero Length Strings?

If I allow Nullable Zero Length Strings, will my searches for "empty" values need to include both Null and ""? That might be a compelling reason to figure out how to get empy controls to be considered vbNull and not a zero length string.

Thanks.
0
 
majnunAuthor Commented:
Here are my options as I see them:

1) Change the DB so that Zero Length Strings are Allowed for non-required fields
2) Extend the functionality of the controls to return Nothing instread of "" if there text property is ""
3) Prior to updating, test each column's allow zero length string property, if it is false, then check the control that the column is bound to, and if it is "" change it to Nothing. (Not even sure if this will work, since I am assuming if you change a textboxt.text property to Nothing, it still is "".

Are there any other options?

Which is the best approach, how do I implement it?

Thanks.
0
 
LacutahCommented:
3 is a good solution, here's how:

Let's assume your DataAdapter is named "da":
    Private WithEvents da As New Data.OleDb.OleDbDataAdapter() 'In the form's declarations

Use the "RowUpdating" event of the dataadapter to change the value to DBNull:
    Private Sub da_RowUpdating(ByVal Sender As Object, ByVal e As OleDb.OleDbRowUpdatingEventArgs) Handles da.RowUpdating
        'Only preform the following checks if the row is being inserted or updated
        If e.StatementType = StatementType.Insert Or e.StatementType = StatementType.Update Then
            Dim dc As DataColumn
            For Each dc In e.Row.Table.Columns
                'Find all columns that allow DBNull and where datatype = string
                If dc.AllowDBNull And dc.DataType Is GetType(String) Then
                    'If zero-length string, change to dbnull.
                    If e.Row(dc).value.ToString.Length = 0 Then
                        e.Row(dc).value = DBNull.Value
                    End If
                End If
            Next
        End If
    End Sub

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
majnunAuthor Commented:
Oh that looks great...

now the difficult part...I am using all this in a class, and the adapters are created dynamically so I have to wire that up at run time, which I think I can figure out...

I'll keep you posted.

Thanks, that is exactly what i was looking for (assuming it works) ;)

0
 
LacutahCommented:
If adapters are created dynamically, instead of event wiring with the handles keyword, use:

Sub MySub()
   da = new DataAdapter()
   ...
   AddHandler da.RowUpdating, AddressOf da_RowUpdating
0
 
majnunAuthor Commented:
Thanks!

I'll get right on that.
0
 
majnunAuthor Commented:
Oh...

and is there a way to test for Allow Zero Length String?

I mean, supposing someone WANTS to enter a zero length string...

hmmm....

For completion sake how do I add a test for:
 If dc.AllowDBNull And dc.DataType Is GetType(String) AND dc.AllowZeroLengthString then ...

is there a dc.AllowZeroLengthString ?
0
 
majnunAuthor Commented:
er

If dc.AllowDBNull And dc.DataType Is GetType(String) AND NOT dc.AllowZeroLengthString then ...
0
 
majnunAuthor Commented:
I get an update error message of:

Public member 'value' on type 'String' not found.
0
 
LacutahCommented:
Unfortunately, I don't see that property exposed by the datacolumn class - but since AllowDBNull is, you can infer that a zero length string is better stored as Null anyway!  (At least, that's my thinking...  Allows for better consistency anyway.)  Why store zero-length string when you can have Null.  I believe that Access automatically converts zero-length strings to Null when using it's forms / directly editing data tables.
0
 
majnunAuthor Commented:
Yep. agreed.

Any thoughts on the update error message I described above?
0
 
LacutahCommented:
What line are you running into the error on?
0
 
majnunAuthor Commented:
Ok, changed the code a bit to get passed the error listed above, now I get a new error:

 Private Sub adapter_Updating(ByVal Sender As Object, ByVal e As OleDb.OleDbRowUpdatingEventArgs)
        'Only preform the following checks if the row is being inserted or updated
        If e.StatementType = StatementType.Insert Or e.StatementType = StatementType.Update Then
            Dim dc As DataColumn
            For Each dc In e.Row.Table.Columns
                'Find all columns that allow DBNull and where datatype = string
                If dc.AllowDBNull = True And dc.DataType Is GetType(String) Then
                    MsgBox(dc.ColumnName & " Allow Null is true and type is String")
                    'If zero-length string, change to dbnull.
                    If CStr(e.Row(dc)).Length = 0 Then
                        MsgBox("Length of " & dc.ColumnName & " is zero")
                        e.Row(dc) = DBNull.Value
                        MsgBox("changed " & dc.ColumnName & " to Null")

                    End If
                End If
            Next
        End If
    End Sub

It seems to work on some fields but on others it gets thru the updating handler without crashing, but then there is update error messages that I catch in my update routine that reports:
"Cast from  type 'DBNULL' to type 'String' is not valid"

I am certain the culprit is this line of code in the updating handler:
e.Row(dc) = DBNull.Value

I'll look into trying to determine what is different about the fields and that work and the ones that fail.
0
 
majnunAuthor Commented:
It works, unless it's a combobox with a databound list, in which case i get the "Cast from..." error.

Thoughts?
0
 
LacutahCommented:
Think I may have it - the combobox is setting the value to dbnull (whereas a text box will only give you zero-length string), this would crash the code when trying to get the length of "dbnull"...

                If dc.AllowDBNull = True And dc.DataType Is GetType(String) Then
                    MsgBox(dc.ColumnName & " Allow Null is true and type is String")
                    'If zero-length string, change to dbnull.
'******Next Line Altered ************
                    If e.row(dc) <> dbnull.value andalso CStr(e.Row(dc)).Length = 0 Then
                        MsgBox("Length of " & dc.ColumnName & " is zero")
                        e.Row(dc) = DBNull.Value
                        MsgBox("changed " & dc.ColumnName & " to Null")

                    End If
0
 
majnunAuthor Commented:
Good catch! ... but i had to split the test up over two if-then's since it was the ctype test itself that caused the error...

This seems to do the trick:

 Private Sub adapter_Updating(ByVal Sender As Object, ByVal e As OleDb.OleDbRowUpdatingEventArgs)
        'Need to change empty strings into Nulls if nulls are allowed since often a text field will also have a AllowZeroLengthString setting to false in Access and all textboxes return a zero length string and not vbNULL

        'Only preform the following checks if the row is being inserted or updated
        If e.StatementType = StatementType.Insert Or e.StatementType = StatementType.Update Then
            Dim dc As DataColumn
            For Each dc In e.Row.Table.Columns
                'Find all columns that allow DBNull and where datatype = string

                If dc.AllowDBNull = True And dc.DataType Is GetType(String) Then
                    MsgBox(dc.ColumnName & " Allow Null is true and type is String")
                    'If zero-length string, change to dbnull.
                    If Not e.Row(dc) Is DBNull.Value Then
                        If CStr(e.Row(dc)).Length = 0 Then
                            MsgBox("Length of " & dc.ColumnName & " is zero")
                            e.Row(dc) = DBNull.Value
                            MsgBox("changed " & dc.ColumnName & " to Null")
                        End If
                    End If
                End If
            Next
        End If
    End Sub


I am getting another error message, but I think that is because my adapter is slightly messed up, I think this part of the code works properly, but I will hold off closing out this question until I am sure.

In the meantime thanks for the help!
0
 
majnunAuthor Commented:
Yeah I'm almost positive that the current error message i am getting is caused by my adapter's insert command, so I will go ahead and award points to close this question.

I am accepting your answer above, but anyone reading this later: please realize the code I posted underneath is the code that acutally works.

Thanks again for your help!
0
 
majnunAuthor Commented:
!!!HOLD ON A MINUTE!!!!

Now comboboxes work, but textboxes still get zerolength string errors...

hmmmm....

0
 
majnunAuthor Commented:
Doh... it seems during testing I didn't have the fields set to "AllowZeroLengthStrings = FALSE" which of course means it looked like it was working...

oh so sad really...

i think we are totally on the right track tho.
0
 
majnunAuthor Commented:
Here's the general delima...

by the time the adapter is updating, the type of the columns are set (in this case to string). Then when I try to set the value of that column in a row, it doesn't like trying to set it to DBNull.value since it is trying to cast the DBNull.value into a string, which I guess is impossible.

It might not even be possible to do what we are trying to do.


0
 
majnunAuthor Commented:
Still feel like helping me even though I prematurely awarded points? Or are we done even though the solution I accepted really doesn't work?
0
 
LacutahCommented:
This is weird, I've never had trouble with "e.Row(dc) = DBNull.Value", never had DBNull.Value cast to "String"...

I guess one work-around would be to write the SQL yourself, putting "NULL" in the update/insert statement by hand...

IE, instead of "Insert into tblCustomers (fname, lname, company) VALUES (?,?,?)"
Re-Writing it on the fly as:
        "Insert into tblCustomers (fname, lname, company) VALUES (?,?,[Null])"
and allowing the command parameters to insert the first and last names...
0
 
LacutahCommented:
BTW, I wasn't ignoring you, I was simply "Out of the Office" for a week or so (lots of yard work...)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 16
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now