[RESOLVED] DataAdapter thinks everything is NULL

[_cerberus_]

I'm at a complete loss and very confused. I have two different WinForms that perform almost exactly the same - that is to retrieve data from the database and be able to edit & save that data back, or to create new records. On form "A" everything works as expected with no errors. On form "B" it thinks controls are NULL if that column in the database is set to "Not NULL." Form "B" uses almost identical code to form "A" for building everything, as well as editing and saving records. If I change a "problem" column to accept nulls, then the error goes away for that column. This is not an issue on form "A" even though it has a similar database setup as well.

IF I do a CType(bs_Record.current, datarowview).Item("Room") I get the value expected.
If I do a dt_Record.Rows.Item(772).item("Room") I get the value expected.

Here is the error I am getting:



I am not understanding why the DataAdapter thinks the controls are NULL, even though the BindingSource AND DataTable both have the information in them.


This is the code to build the DA, DT, and BS:

Using SQLcmd As New SqlCommand With {
	.Connection = Vars.sqlConn,
	.CommandText = "SELECT * FROM [BAD].[data_Lab_Asset] ORDER BY [BGL_ID] DESC"}
	da_Record.SelectCommand = SQLcmd
	Dim sqlCB As New SqlCommandBuilder(da_Record)
	AddHandler da_Record.RowUpdated, AddressOf OnRowUpdated

	da_Record.Fill(dt_Record)

	dt_Record.Columns("OP_Status_ID").DefaultValue = 3

	'** ALL Check boxes need a Default Value for the "Add New" to work
	dt_Record.Columns("Retired").DefaultValue = False
	dt_Record.Columns("Storage").DefaultValue = False
	dt_Record.Columns("Transferred").DefaultValue = False
	dt_Record.Columns("Removed").DefaultValue = False
	dt_Record.Columns("Calibration_Req").DefaultValue = False
	dt_Record.Columns("TQC_Req").DefaultValue = False
	dt_Record.Columns("TQC_Built").DefaultValue = False
	dt_Record.Columns("TQC_Worklist_ID").DefaultValue = 0

	bs_Record.DataSource = dt_Record
	bsnav_Record.BindingSource = bs_Record
End Using

Here is a snippet of the Binding code:

Private Sub BindControls()
	Try
		txtRecID.DataBindings.Add("Text", bs_Record, "ID_Lab_Asset")

		cboxSupportGroup.DataBindings.Add("SelectedValue", bs_Record, "Support_Group_ID")
		cboxArea.DataBindings.Add("SelectedValue", bs_Record, "Work_Area_ID")
		txtRoom.DataBindings.Add("Text", bs_Record, "Room")

		cboxClass.DataBindings.Add("SelectedValue", bs_Record, "Class_ID")
		cboxSubclass.DataBindings.Add("SelectedValue", bs_Record, "Subclass_ID")
		cboxSubcategory.DataBindings.Add("SelectedValue", bs_Record, "Subcategory_ID")

		chkCalReq.DataBindings.Add("Checked", bs_Record, "Calibration_Req")
		dtpCalDue.DataBindings.Add("Text", bs_Record, "Calibration_Due", True, DataSourceUpdateMode.OnValidation, " ", "MM/dd/yyyy")

		dtpPurch.DataBindings.Add("Text", bs_Record, "Date_Purchased", True, DataSourceUpdateMode.OnValidation, " ", "MM/dd/yyyy")
		dtpRecvd.DataBindings.Add("Text", bs_Record, "Date_Received", True, DataSourceUpdateMode.OnValidation, " ", "MM/dd/yyyy")
		txtRecvdFrom.DataBindings.Add("Text", bs_Record, "Received_From")

		txtComments.DataBindings.Add("Text", bs_Record, "Comments")

	Catch ex As Exception
		CustExErrorMsg(Name, Reflection.MethodBase.GetCurrentMethod().Name, GetExceptionInfo(ex))
	End Try
End Sub

Code to create new row in BindingSource:

Private Sub btnNew_Click(sender As Object, e As EventArgs) Handles BNavAddNewItem.Click
	Try
		bs_Record.AddNew()

		bs_Related.Filter = "BIOMED_Tag = 'XXX'"
		bs_Validation.Filter = "Lab_Asset_ID = 'XXX'"

		dtpPurch.Value = New Date(Date.Now.Year, Date.Now.Month, Date.Now.Day - 1)
		dtpRecvd.Value = New Date(Date.Now.Year, Date.Now.Month, Date.Now.Day - 1)
		dtpInUse.Value = New Date(Date.Now.Year, Date.Now.Month, Date.Now.Day - 1)

		EnableControls()

	Catch ex As Exception
		CustExErrorMsg(Name, Reflection.MethodBase.GetCurrentMethod().Name, GetExceptionInfo(ex))
	End Try
End Sub

And finally the code to save the record:

Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles BNavSaveItem.Click
	Try
		If IsComplete() Then
			If blnNew Then If IsDuplicate() Then GoTo TheEnd

			Me.Validate()
			bs_Record.EndEdit()
			da_Record.Update(dt_Record)

			If blnNew Then bs_Record.Position = bs_Record.Find("ID_Lab_Asset", ID_New)

TheEnd:
			IsDirty = False
			blnEdit = False
			blnNew = False
			DisableControls()
		End If

	Catch ex As Exception
		CustExErrorMsg(Name, Reflection.MethodBase.GetCurrentMethod().Name, GetExceptionInfo(ex))
	End Try
End Sub

[Peter Swinkels]

After glancing at your code, I see it looks pretty clean, however you used a GoTo statement where it looks like an If/EndIf block could be used. I strongly recommend using modern structured programming techniques for control flow unless you absolutely have no alternative. Also ,the error message you posted implies the controls being "null" isn't the issue. It looks like there's a null data being returned by your data adapter. Could you post a working sample for people to test?

[_cerberus_]

After glancing at your code, I see it looks pretty clean, however you used a GoTo statement where it looks like an If/EndIf block could be used. I strongly recommend using modern structured programming techniques for control flow unless you absolutely have no alternative. Also ,the error message you posted implies the controls being "null" isn't the issue. It looks like there's a null data being returned by your data adapter. Could you post a working sample for people to test?

Peter,
I'm not sure what the "modern structured programming techniques for control flow" would be for VB.net - I'm used to coding in VBA for Excel. I'm open to anything you have to offer as an example for what would be correct.

Unfortunately, I cannot provide a working sample as it does contain some proprietary information that would be difficult to cut out.

You are correct that the control being "null" is not the issue. I have "check" code that runs before the save to make sure certain controls have been populated - everything comes out clean. I have no idea why the data adapter would be returning any null values if the control, data table, and binding source all have the correct information. It's almost like all the data is being lost between the BS.EndEdit and the DA.Update - or the DA is just not seeing the DT/BS correctly?

I'm sure the issue is something simple I'm overlooking... it's always the simplest fix for the most complex problems.

[jmcilhinney]

So debug your code. That's why VS has a debugger. Place a breakpoint at the top of the relevant code and then step through it line by line, examining the state at each step to ensure that everything is what you expect it to be. If it's not then you'll be able to work out exactly what and where.

I have no idea why the data adapter would be returning any null values

That's a nonsensical statement. The data adapter is not returning any null values. The data adapter is getting null values from the DataTable. The DataTable is where all the data is stored so the DataTable is what you need to be looking at. One option, for debugging at least, is to call GetChanges on the DataTable, which will return a new DataTable containing only the changed rows. That will make it easier to examine just the rows that have changed and are being saved.

[Peter Swinkels]

@_cerberus_: GoTo was already discouraged when vb6 came out. I learned to avoid it even as far back as Quick Basic. I suggest you browse the keyword listing for vb over at the MSDN, at: https://docs.microsoft.com/en-us/dot...ence/keywords/ - "Continue" for example, is a control flow statement specific to vb.net but probably not useful in your case.

[jmcilhinney]

If you are doing this:

If x Then GoTo Label1
 
    '...
 
Label1:

then you should be doing this instead:

If Not x Then
    '...
End If

This garbage:

If blnNew Then If IsDuplicate() Then GoTo TheEnd

should have been this in the first place:

If blnNew AndAlso IsDuplicate() Then GoTo TheEnd

so you simply negate that expression and put the code the GoTo would skip into the If block.

[_cerberus_]

So debug your code. That's why VS has a debugger. Place a breakpoint at the top of the relevant code and then step through it line by line, examining the state at each step to ensure that everything is what you expect it to be. If it's not then you'll be able to work out exactly what and where.

I did debug my code with breakpoints. In the Immediate window I ran CType(bs_Record.current, datarowview).Item("Room") and dt_Record.Rows.Item(772).item("Room") after the BindingSource.EndEdit and before the DataAdapter.Update - Each of those return the expect non-null values. It's only when the DA.Update happens that values become null. I will take your suggestion and call GetChanges on the DataTable to see what there is.

That's a nonsensical statement. The data adapter is not returning any null values. The data adapter is getting null values from the DataTable. The DataTable is where all the data is stored so the DataTable is what you need to be looking at. One option, for debugging at least, is to call GetChanges on the DataTable, which will return a new DataTable containing only the changed rows. That will make it easier to examine just the rows that have changed and are being saved.

I'm not going to argue semantics with you, but when I know the data table had data in it (from debugging before the update) and then the data adapter errors with null values, it is in a way returning null values. Just a difference of terminology I guess.

[_cerberus_]

If you are doing this:

If x Then GoTo Label1
 
    '...
 
Label1:

then you should be doing this instead:

If Not x Then
    '...
End If

This garbage:

If blnNew Then If IsDuplicate() Then GoTo TheEnd

should have been this in the first place:

If blnNew AndAlso IsDuplicate() Then GoTo TheEnd

so you simply negate that expression and put the code the GoTo would skip into the If block.

Thank you for your suggestions. However, I'm not sure if it fully helps what I was trying to achieve, although I'm sure there is a better way then what I did it.
Only if blnNew=True do I want to run IsDuplicate(), and regardless of whether either of those are True/False, I need to run the code after "TheEnd."
I thought about putting in a Finally statement, but I don't want to run that if the IsComplete() comes back false.
I think I might be stuck with using a label?

Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles BNavSaveItem.Click
    Try
        If Not IsComplete() Then Exit Sub
        If blnNew AndAlso IsDuplicate() Then GoTo TheEnd:
 
        lblTSS.Text = "Saving..."
        lblTSS.BackColor = Color.IndianRed
        Application.DoEvents()
 
        Me.Validate()
        bs_Record.EndEdit()
        da_Record.Update(dt_Record)
 
        If blnNew Then bs_Record.Position = bs_Record.Find("ID_Lab_Asset", ID_New)
 
TheEnd:
        IsDirty = False
        blnEdit = False
        blnNew = False
        DisableControls()
 
        lblTSS.Text = "Done"
        lblTSS.BackColor = Color.LightGreen
 
    Catch ex As Exception
        CustExErrorMsg(Name, Reflection.MethodBase.GetCurrentMethod().Name, GetExceptionInfo(ex))
    End Try
End Sub

[techgnome]

Thank you for your suggestions. However, I'm not sure if it fully helps what I was trying to achieve, although I'm sure there is a better way then what I did it.
Only if blnNew=True do I want to run IsDuplicate(), and regardless of whether either of those are True/False, I need to run the code after "TheEnd."
I thought about putting in a Finally statement, but I don't want to run that if the IsComplete() comes back false.
I think I might be stuck with using a label?

Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles BNavSaveItem.Click
    Try
        If Not IsComplete() Then Exit Sub
        If blnNew AndAlso IsDuplicate() Then GoTo TheEnd:
 
        lblTSS.Text = "Saving..."
        lblTSS.BackColor = Color.IndianRed
        Application.DoEvents()
 
        Me.Validate()
        bs_Record.EndEdit()
        da_Record.Update(dt_Record)
 
        If blnNew Then bs_Record.Position = bs_Record.Find("ID_Lab_Asset", ID_New)
 
TheEnd:
        IsDirty = False
        blnEdit = False
        blnNew = False
        DisableControls()
 
        lblTSS.Text = "Done"
        lblTSS.BackColor = Color.LightGreen
 
    Catch ex As Exception
        CustExErrorMsg(Name, Reflection.MethodBase.GetCurrentMethod().Name, GetExceptionInfo(ex))
    End Try
End Sub

1) No, it doesn't directly help the issue at hand, but it makes it easier for others to help
2) That's why jmc suggested AndAlso ... the first part of hte value will be checked .. if it's true - and only if it's true - it will then run isDuplicate ... if blnNew is false, then it's skipped.
3) IF you want something to always run, then it goes after hte block before it...

If something andAlso somethingElse then
  do this
  do that
End If

Do something to runs no matter hte results of the above block

-tg

[techgnome]

Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles BNavSaveItem.Click
  if IsComplete() Then
    If Not (blnNew andAlso IsDuplicate()) then
        lblTSS.Text = "Saving..."
        lblTSS.BackColor = Color.IndianRed
        Application.DoEvents()
 
        Me.Validate()
        bs_Record.EndEdit()


        ' If you want a Try Catch, this would be the place for it... keep them as small as possible.
        da_Record.Update(dt_Record)

        If blnNew Then bs_Record.Position = bs_Record.Find("ID_Lab_Asset", ID_New)

    end If

    IsDirty = False
    blnEdit = False
    blnNew = False
    DisableControls()

    lblTSS.Text = "Done"
    lblTSS.BackColor = Color.LightGreen

End Sub

-tg

[_cerberus_]

One option, for debugging at least, is to call GetChanges on the DataTable, which will return a new DataTable containing only the changed rows. That will make it easier to examine just the rows that have changed and are being saved.

JM- Thank you for this idea, I was finally able to solve the issue. By using Dim dt_test As DataTable = dt_Record.GetChanges(DataRowState.Added) I was able to determine TWO rows were getting added as new instead of one. The first one was getting added because I had "AddNewItem" on the Binding Navigator set to one of the buttons, but that button I already had set to a click event. This was causing it to double add rows. Once I set "AddNewItem" to "(none)" all the code worked just fine. Wish I would have seen that when I was comparing my two forms side-by-side.