1 Attachment(s)
[RESOLVED] DataAdapter thinks everything is NULL
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:
Attachment 184682
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:
Code:
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:
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:
Code:
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:
Code:
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
Re: DataAdapter thinks everything is NULL
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?
Re: DataAdapter thinks everything is NULL
Quote:
Originally Posted by
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?
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.
Re: DataAdapter thinks everything is NULL
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.
Quote:
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.
Re: DataAdapter thinks everything is NULL
@_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.
Re: DataAdapter thinks everything is NULL
If you are doing this:
vb.net Code:
If x Then GoTo Label1
'...
Label1:
then you should be doing this instead:
vb.net Code:
If Not x Then
'...
End If
This garbage:
vb.net Code:
If blnNew Then If IsDuplicate() Then GoTo TheEnd
should have been this in the first place:
vb.net Code:
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.
Re: DataAdapter thinks everything is NULL
Quote:
Originally Posted by
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 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.
Quote:
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.
Re: DataAdapter thinks everything is NULL
Quote:
Originally Posted by
jmcilhinney
If you are doing this:
vb.net Code:
If x Then GoTo Label1
'...
Label1:
then you should be doing this instead:
vb.net Code:
If Not x Then
'...
End If
This garbage:
vb.net Code:
If blnNew Then If IsDuplicate() Then GoTo TheEnd
should have been this in the first place:
vb.net Code:
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?
vb.net Code:
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
Re: DataAdapter thinks everything is NULL
Quote:
Originally Posted by
_cerberus_
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?
vb.net Code:
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...
Code:
If something andAlso somethingElse then
do this
do that
End If
Do something to runs no matter hte results of the above block
-tg
Re: DataAdapter thinks everything is NULL
Code:
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
Re: DataAdapter thinks everything is NULL
Quote:
Originally Posted by
jmcilhinney
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.
