[RESOLVED] No data transfer in one column

[gwboolean]

I have one line (1193) in a data transfer that is not occurring

All the other data transfer occurs without fault. However, there is no data in strChangeProposed and it is a longtext field (Access) as is the field strRequirements.

            _MasterBase4_0ItemMasterDataSet.tblItemMaster(0).strRequirements = _MasterBase4_0ItemMasterDataSet.lnkChangeRequest(0).strChangeProposed

The error mentions that the field is DBNull. Why is that a problem? The input table fields will not always have data in them and I want the transfer to take place anyway.



Whole Routine:

    Private Sub SaveRevChangeRequest()
        Try
            'Assign values to global variables from form controls and save changes to Change Request
            glbstrBaseCategory = CStr(lblChangeType.Text)
            glbstrFilePath = CStr(lblEFile.Text)
            glbblnSaved = True
            Me.Validate()
            Me.LnkChangeRequestBindingSource.EndEdit()
            Me.LnkChangeRequestTableAdapter.Update(Me._MasterBase4_0ItemMasterDataSet)
            'Update Change ID
            Me.LkpChangeIDTableAdapter.Fill(Me._MasterBase4_0ItemMasterDataSet.lkpChangeID)
            Me._MasterBase4_0ItemMasterDataSet.lkpChangeID(0).intChangeID = glbintCRNum
            Me.Validate()
            Me.lkpChangeIDBindingSource.EndEdit()
            Me.LkpChangeIDTableAdapter.Update(Me._MasterBase4_0ItemMasterDataSet)
            'Update Item Record
            Me.LnkChangeRequestTableAdapter.FillByChangeNum(Me._MasterBase4_0ItemMasterDataSet.lnkChangeRequest, glbintCRNum)
            Me.TblItemMasterTableAdapter.FillBySiTechID(Me._MasterBase4_0ItemMasterDataSet.tblItemMaster, glbintIDNum)
            _MasterBase4_0ItemMasterDataSet.tblItemMaster(0).strTitle = _MasterBase4_0ItemMasterDataSet.lnkChangeRequest(0).strTitle
            _MasterBase4_0ItemMasterDataSet.tblItemMaster(0).strRevision = _MasterBase4_0ItemMasterDataSet.lnkChangeRequest(0).strRevision
            _MasterBase4_0ItemMasterDataSet.tblItemMaster(0).strRequirements = _MasterBase4_0ItemMasterDataSet.lnkChangeRequest(0).strChangeProposed
            _MasterBase4_0ItemMasterDataSet.tblItemMaster(0).strOwner = _MasterBase4_0ItemMasterDataSet.lnkChangeRequest(0).strOwner
            _MasterBase4_0ItemMasterDataSet.tblItemMaster(0).strWhereUsed = _MasterBase4_0ItemMasterDataSet.lnkChangeRequest(0).strWhere
            If glbblnEffective Then
                _MasterBase4_0ItemMasterDataSet.tblItemMaster(0).blnEffective = False
                _MasterBase4_0ItemMasterDataSet.tblItemMaster(0).blnObsolete = False
                _MasterBase4_0ItemMasterDataSet.tblItemMaster(0).blnSupersede = True
                _MasterBase4_0ItemMasterDataSet.tblItemMaster(0).blnValidated = False
            End If
            Me.tblItemMasterBindingSource.EndEdit()
            Me.TblItemMasterTableAdapter.Update(Me._MasterBase4_0ItemMasterDataSet)
        Catch ex As Exception
            MessageBox.Show(String.Format("Message: {0} {1} StackTrace: {2}", ex.Message, vbNewLine, ex.StackTrace))
            'Me.Close()
            'mnuItemMaster.Show()
        End Try
    End Sub

[TysonLPrice]

If the column doesn't allow nulls check for that and pass a value it will except (like zero or spaces). Sometimes a table is created to not allow nulls in certain columns.

[gwboolean]

I checked it out. I was able to pass any string value or any of the string columns except the one noted above. I checked the properties at the database and the only difference between that and other fields I am passing is that it is a LongText fields. However, I don't see why that would cause the exception.

[Mike Storm]

I belive your issue is that you trying to pass a DBNull Value, meaning the source field is empty and never had a value, right?

[Mike Storm]

You need to see if it is supoused to return a DBNull value on that field, and if not why it is doing it.

Now here are 2 ways to handle DBNull values:

'Option one
            _MasterBase4_0ItemMasterDataSet.tblItemMaster(0).strRequirements = If(Not IsDBNull(
                _MasterBase4_0ItemMasterDataSet.lnkChangeRequest(0).strChangeProposed),
                _MasterBase4_0ItemMasterDataSet.lnkChangeRequest(0).strChangeProposed, "Value if false")

            'Option two
            If Not IsDBNull(_MasterBase4_0ItemMasterDataSet.lnkChangeRequest(0).strChangeProposed) Then

                _MasterBase4_0ItemMasterDataSet.tblItemMaster(0).strRequirements = _MasterBase4_0ItemMasterDataSet.lnkChangeRequest(0).strChangeProposed

            Else

                _MasterBase4_0ItemMasterDataSet.tblItemMaster(0).strRequirements = "Value if dbnull"

            End If

[gwboolean]

It is true, the field is empty and has nothing in it to pass. And I see the work around for that. But I still do not understand why that would be an issue and why it matters if the passing field is empty.

[Mike Storm]

Have a look on this:

https://msdn.microsoft.com/en-us/lib...v=vs.110).aspx

See it as vacuum field.

[jmcilhinney]

A database column can be configured to allow NULL or not. If it does allow NULL then it can contain a value or not. If it doesn't allow NULL then it MUST contain a value. DBNull.Value is how database nulls are represented in ADO.NET, which means that you are trying to save a record with no value in that column. If the column doesn't allow NULL then obviously trying to save NULL to it will fail. If it is a non-nullable text field and you have no value then the obvious substitute is an empty String. An empty String is not the same as NULL, just as you having an empty pocket is not the same as having no pocket at all.

[gwboolean]

OK, I buy that. It certainly fits with what I have already observed and stated. So where is it that the column is configured to allow or not allow null. I have looked everywhere I can think of and am unable to find where this property is defined.

[Mike Storm]

Either on the dataset, or in the database itself, or in bouth.

[Mike Storm]

Have a look:

This is SQL, but Acces wont be that diferent i belive.


Now, if your issue is that the field does not allow DBNUll, you need to make sure you set bouth cases(in the database, and in the dataset) to the desired.

[jmcilhinney]

There are potentially two issues here. Firstly, do you want the column to be nullable? It makes sense for some columns to be nullable but not for others. You have to think about what the data actually represents and whether it makes sense for that field to have no value at all. For instance, if you have a Person table then it would make sense for a MiddleName column to be nullable because not everyone has a middle name, but it wouldn't make sense for GivenName and FamilyName columns to be nullable. If it makes sense for this column to be nullable then leave it as it is. If it doesn't make sense then change it in the database (it's part of the column configuration so that's where you change it) and then re-run the configuration of your DataSet in the Data Sources window.

If it does make sense for your column to be nullable then you have to deal with that in your code. Even if you change this column, you're going to have a valid nullable column at some point. The way a typed DataSet works, you have to deal with null values separately. This code:

_MasterBase4_0ItemMasterDataSet.tblItemMaster(0).strRequirements = _MasterBase4_0ItemMasterDataSet.lnkChangeRequest(0).strChangeProposed

can only possibly work if the 'strChangeProposed' field of that row contains a value because the 'strChangeProposed' property is type String. If the field doesn't contain a value then the DataRow contains DBNull.Value and that is not a String so it cannot be accessed via a String property. The typed DataRow has methods (or perhaps one method and one property) for dealing with nulls. Assuming that both your 'strChangeProposed' and 'strRequirements' columns are nullable, your code should look something like this:

If _MasterBase4_0ItemMasterDataSet.lnkChangeRequest(0).IsstrChangeProposedNull Then
    'The source field is null so null the destination field too.
    _MasterBase4_0ItemMasterDataSet.tblItemMaster(0).SetstrRequirementsNull()
Else
    'The value is a String so it can be transferred.
    _MasterBase4_0ItemMasterDataSet.tblItemMaster(0).strRequirements = _MasterBase4_0ItemMasterDataSet.lnkChangeRequest(0).strChangeProposed
End If

The names might be slightly different to 'IsstrChangeProposedNull' and 'SetstrRequirementsNull' but pretty close. I'm not sure whether the first one is a method or a property but it doesn't matter much. It will work as is either way but I prefer to include the parentheses on a method call, even if there are no arguments.

[gwboolean]

JM, thanks. I get how to deal with a DBNull. I also understand that there are some columns/fields that one would want to not allow a pass when the column/field is empty/null. But what I am really trying to understand is why this particular column/field is behaving this way.

I looked at the properties, as Mike suggested, and this is what I see:



So as far as I can tell, the null value should be allowed. The other thing I noticed was the Null Value setting, which states throw exception. I looked at most of my other fields in that table of the dataset and they are set identically to that.

My understanding of all of this would be that the exception should not be thrown when that data transaction with that field occurs.

[jmcilhinney]

So as far as I can tell, the null value should be allowed. The other thing I noticed was the Null Value setting, which states throw exception.

I'm not sure that you absorbed what I put in my previous post. You are correct that null should be allowed, and null obviously is allowed because your original error message stated that the column contained DBNull.Value. The error message has nothing to do with trying to set a field to null that doesn't support it. That error message is the result of trying to access a DBNull object via a property of type String. That is what is not allowed. As I said in my previous post - and demonstrated in code - you have to test the field first to see whether it is null and then only access its value if it has one. Internally, the code for your 'strChangeProposed' property would look something like this:

Return DirectCast(Me.Item("strChangeProposed"), String)

Obviously you can only cast something that is a String as type String, so if the field contains DBNull.Value then that cast will fail. That's why you get that error message.

[jmcilhinney]

The point of that NullValue property that you highlighted is to specify what to do when you try to access the field value when it is null. As you said, it is set to '(Throw exception)' by default and that is exactly why an exception is thrown when you try to access the field value when it's null. If you want to keep that setting then you need to do what I said, i.e. check whether the field is null first and then only get the value if it's not. The alternative would be to change that property. I don't know what the other options are but I'd expect that one of them would be to use the default value for the type, which would be a null reference for type String. If you do that, make sure that you change it for all your other nullable fields too. If you don't then the code you have will probably still fail because you'll be assigning a null reference to another field.

[gwboolean]

Maybe I did not JM. On the one hand, I am not sure why a test is necessary, since I already know that the field is empty, having made sure that the field is indeed empty of any data. Now this is where it becomes confusing to me. You are telling me (at least that is what I understand) the issue is that I am attempting to cast a Null as type string when it is not. But with the property set to allow DBNull, I still do not understand why I get this error for this particular field, but not for others that have the identical setting and none of which throw an exception if the field that I am passing from is empty.

Or to put it another way, why does that cast fail for this particular string field, but not for other string fields?

[jmcilhinney]

Or to put it another way, why does that cast fail for this particular string field, but not for other string fields?

Either all your fields are behaving the same way and there's something wrong with the way you're testing their behaviour or else something in your project is broken because the behaviour you're seeing for this field is exactly the behaviour you should be seeing. The exception that was thrown should have been thrown.

[Mike Storm]

You cant pass DBNull as string/Integer or what ever, couse DBNl its like the field does not exists, its a "Vacuum" spot.

The DBNull class represents a nonexistent value. In a database, for example, a column in a row of a table might not contain any data whatsoever. That is, the column is considered to not exist at all instead of merely not having a value. A DBNull object represents the nonexistent column. Additionally, COM interop uses the DBNull class to distinguish between a VT_NULL variant, which indicates a nonexistent value, and a VT_EMPTY variant, which indicates an unspecified value.
The DBNull type is a singleton class, which means only one DBNull object exists. The DBNull.Value member represents the sole DBNull object. DBNull.Valuecan be used to explicitly assign a nonexistent value to a database field, although most ADO.NET data providers automatically assign values of DBNull when a field does not have a valid value. You can determine whether a value retrieved from a database field is a DBNull value by passing the value of that field to the DBNull.Value.Equals method. However, some languages and database objects supply methods that make it easier to determine whether the value of a database field is DBNull.Value. These include the Visual Basic IsDBNull function, the Convert.IsDBNull method, the DataTableReader.IsDBNull method, and the IDataRecord.IsDBNull method.
Do not confuse the notion of null in an object-oriented programming language with a DBNull object. In an object-oriented programming language, null means the absence of a reference to an object. DBNull represents an uninitialized variant or nonexistent database column.

https://msdn.microsoft.com/en-us/lib...v=vs.110).aspx

So in order to handle a DBNull field you will need to validate if it is or not.

Either you use:

FieldWhatEVER = IF(Not IsDBNull(my field name, Value if true, Value if false))

Or you use:

If Not IF(Not IsDBNull(my field name, Value if true, Value if false) Then

FieldWhatEVER = my field name

End If

Or as JMC refered in post #12

[Mike Storm]

And also if in your dataset you have the property Allow null as true but in your database its set to do not allow dbnull, it will still trow you a exception.

[jmcilhinney]

You cant pass DBNull as string/Integer or what ever, couse DBNl its like the field does not exists, its a "Vacuum" spot.



So in order to handle a DBNull field you will need to validate if it is or not.

Either you use:

FieldWhatEVER = IF(Not IsDBNull(my field name, Value if true, Value if false))

Or you use:

If Not IF(Not IsDBNull(my field name, Value if true, Value if false) Then

FieldWhatEVER = my field name

End If

You don't use either of those with a typed DataSet. As I've already pointed out, a typed DataRow has a dedicated member (property or method, not sure which) for each field to indicate whether it is null. Even if you were using an untyped DataRow, it still has it's own method (IsNull) for determining whether a field is null or not. Actually, that kind of confirms that the typed DataRow has a method rather than a property. The DataRow class has IsNull and SetNull methods and the typed DataRow simply extends that for each field, e.g. instead of:

If Not myDataRow.IsNull("MyColumn") Then
    myDataRow.SetNull("MyColumn")
End If

you use:

If Not myTypedDataRow.IsMyColumnNull() Then
    myTypedDataRow.SetMyColumnNull()
End If

[gwboolean]

The first thing I checked was back in the table in the database. And it is not set to not allow dbnull. Actually, since it is an Access DB, it has no property for that (I am still going through SQL, but that is not a one or two day turnaround project). But it does have some equivalent settings which are set as they should be. This would be the first time that the table field property settings back in the database actually were not ignored by VB.

[Mike Storm]

You got a point there, i had already edited the post and typed undeneit it

Or as JMC refered in post #12

I use does couse i have the habit of set default values when is a dbnull or set it as empty string, in the case of string fields.

[gwboolean]

I can do that. But that is a lot of fields to set, since the default is always, "Throw exception". I think I am going to put this to bed for tonight and then perhaps in the morning I will just junk all of my data transactions and re-code them.

[Mike Storm]

I dont work in Access for a long time, in a quick research it seems it does not has null(seems) but it does has required, that will be about the same thing.

[jmcilhinney]

I dont work in Access for a long time, in a quick research it seems it does not has null(seems) but it does has required, that will be about the same thing.

Indeed. If a value is not required then you don't have to provide a value. If you don't provide a value then the field has no value. Not having a value is the definition of being null. So, if Required is set to No or False in Access then AllowDBNull will be set to True in your typed DataSet.

[gwboolean]

Yep, that was what I figured. So it is clear that something is messed up somewhere. I can't find it and I think junking the whole process and re-doing it is the best solution. Actually, it isn't the best solution it is just the easy way out. Thanks for the help guys.

[jmcilhinney]

Or you could try deleting the existing Data Source and creating a new one. Most of your existing code should just keep working aww is.

[gwboolean]

Yeah, that would work. However, if your suggestion includes going after the dataset that would be way too much. While it is not hard to setup a new dataset, it is not so easy to recreate all of the queries that are lost when you do that. Actually, I just went ahead and kept things pretty much as they were and did as Mike suggested. It wasn't as tedious as I thought it would be. It all works as it should now.

[Mike Storm]

Hi, i dont know what you mean by doing it like Mike sugested but keep in mind that in your case will be better to use post #20 JMC sample code.
As i mention before i set DBNulls to default values when i have to address them in my code, Text/String fields to empty string, numeric fields to 0 or booleans to false, in your case i dont see why you have a issue in one column and not in the others if they also null, i also dont know if u can set a field that has had a value in it back to Null and if thats the case.

[gwboolean]

What I thought you were referring to Mike was to set the property for null value to something other than throw an exception. Anyway, that was one of the things I got out of that and it appears to get the job done. I do understand and have no problem with doing as was suggested by JM in #20. Although, I think that before I would take that route I would just go back to defining variables from one table and then use them to pass the values into another table. To be honest I would prefer that to setting up a branch for dealing with that. Not saying that the branch approach isn't fine or even better, but we all have our personal preferences.

[Mike Storm]

Can you post the code you used, i m not understanding something.

[gwboolean]

Sure, but what I actually ended up doing was going back to what I had previously with the only change being that I went to the field properties in the table adapters and changed the null property to either Empty or Nothing. But here is the code used for my data transaction:

    Private Sub UpdateItem()
        'Update Item record
        Me.LnkChangeRequestTableAdapter.FillByChangeNum(Me._MasterBase4_0ItemMasterDataSet.lnkChangeRequest, glbintCRNum)
        Me.TblItemMasterTableAdapter.FillBySiTechID(Me._MasterBase4_0ItemMasterDataSet.tblItemMaster, glbintIDNum)
        Me._MasterBase4_0ItemMasterDataSet.tblItemMaster(0).strTitle = Me._MasterBase4_0ItemMasterDataSet.lnkChangeRequest(0).strTitle
        Me._MasterBase4_0ItemMasterDataSet.tblItemMaster(0).strRevision = Me._MasterBase4_0ItemMasterDataSet.lnkChangeRequest(0).strRevision
        Me._MasterBase4_0ItemMasterDataSet.tblItemMaster(0).strRequirements = Me._MasterBase4_0ItemMasterDataSet.lnkChangeRequest(0).strChangeProposed
        Me._MasterBase4_0ItemMasterDataSet.tblItemMaster(0).strOwner = Me._MasterBase4_0ItemMasterDataSet.lnkChangeRequest(0).strOwner
        Me._MasterBase4_0ItemMasterDataSet.tblItemMaster(0).strWhereUsed = Me._MasterBase4_0ItemMasterDataSet.lnkChangeRequest(0).strWhere
        If glbblnEffective Then
            _MasterBase4_0ItemMasterDataSet.tblItemMaster(0).blnEffective = True
            _MasterBase4_0ItemMasterDataSet.tblItemMaster(0).blnObsolete = False
            _MasterBase4_0ItemMasterDataSet.tblItemMaster(0).blnSupersede = False
        End If
        'Save item record
        Me.Validate()
        Me.tblItemMasterBindingSource.EndEdit()
        Me.TblItemMasterTableAdapter.Update(Me._MasterBase4_0ItemMasterDataSet)
    End Sub

The difference between what is above and what I was talking about (and did previously) would have looked like this:

    Private Sub UpdateItem()
        'Define variables
        Me.LnkChangeRequestTableAdapter.FillByChangeNum(Me._MasterBase4_0ItemMasterDataSet.lnkChangeRequest, glbintCRNum)
        glbstrTitle = Me._MasterBase4_0ItemMasterDataSet.lnkChangeRequest(0).strTitle
        glbstrProposed = Me._MasterBase4_0ItemMasterDataSet.lnkChangeRequest(0).strChangeProposed
        glbstrOwner = Me._MasterBase4_0ItemMasterDataSet.lnkChangeRequest(0).strOwner
        glbstrWhereCategory = Me._MasterBase4_0ItemMasterDataSet.lnkChangeRequest(0).strWhere
        glbstrRevision = Me._MasterBase4_0ItemMasterDataSet.lnkChangeRequest(0).strRevision
        'Update Item record
        Me.TblItemMasterTableAdapter.FillBySiTechID(Me._MasterBase4_0ItemMasterDataSet.tblItemMaster, glbintIDNum)
        Me._MasterBase4_0ItemMasterDataSet.tblItemMaster(0).strTitle = glbstrTitle
        Me._MasterBase4_0ItemMasterDataSet.tblItemMaster(0).strRevision = glbstrRevision
        Me._MasterBase4_0ItemMasterDataSet.tblItemMaster(0).strRequirements = glbstrProposed
        Me._MasterBase4_0ItemMasterDataSet.tblItemMaster(0).strOwner = glbstrOwner
        Me._MasterBase4_0ItemMasterDataSet.tblItemMaster(0).strWhereUsed = glbstrWhereCategory
        If glbblnEffective Then
            _MasterBase4_0ItemMasterDataSet.tblItemMaster(0).blnEffective = True
            _MasterBase4_0ItemMasterDataSet.tblItemMaster(0).blnObsolete = False
            _MasterBase4_0ItemMasterDataSet.tblItemMaster(0).blnSupersede = False
        End If
        'Save item record
        Me.Validate()
        Me.tblItemMasterBindingSource.EndEdit()
        Me.TblItemMasterTableAdapter.Update(Me._MasterBase4_0ItemMasterDataSet)
    End Sub

This is how I used to do this type of data transfer. I am not fond of it (no particular reason), but it never has a problem about passing Null values.