Ugh. I'm starting to dislike ADO.Net. ADO seemed so much simpler.

I'm creating a small database application for logging phone calls. The
program is supposed to load a data entry screen where the user can
enter the details of a phone call and then save the record. The
functionality desired means that I don't want the screen to load
existing data but rather open the form in "add mode".

So I've got a DataAdapter (sdaCallsTable) and a generated dataset
(dsCallsTable). I've tied all of the input fields on the form to the
appropriate fields in the dataset. The dataset only holds this one
"Calls" table. That's it. The "Calls" table consists of
CallID (PK), WorkerID (FK), CallType (FK), CallerID (FK),
AnonymousType (FK), StartDate,
StartTime, EndTime, RiskLevel (FK), LengthOfCall, TriggerAlert,
FollowUp, CallerName,
CallerGender, CallerAge, CallDescription

The idea is that the user fills in the data and clicks the "Save"
button when they're done entering the data. There's also a "New"
button so that the user can intiate a "new record mode" that clears all
the input boxes and sets focus to the first input field. The
SetEditMode() just enables/disables the input fields depending on the
boolean passed to it.

Below is the code that I'm trying to use. Shown after is the exception
detail.
'------------------------------------------------------------------
Private Sub frmCallLog_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Try
'Load the comboboxes used on the form
'This ties in the FK's for the table. This works.
LoadLookups()

'Enable editing mode
SetEditMode(True)

'Default to "add mode"
bNewRecord = True

Catch ex As Exception
Dim strM As String, strC As String
strM = "An error occured while loading this screen. The
error detail is:" & vbCrLf
strM += ex.Message & vbCrLf & ex.StackTrace
strC = "Screen Load Error"
MessageBox.Show(strM, strC, MessageBoxButtons.OK,
MessageBoxIcon.Error)
End Try
End Sub
'------------------------------------------------------------------
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnSave.Click
Try
'Execute the save record function
SaveRecord()

'Disable the input fields until the user clicks the "New
Record" button
SetEditMode(False)

Catch ex As Exception
Dim strM As String, strC As String
strM = "An error occured during this operation. The error
detail is:" & vbCrLf
strM += ex.Message & vbCrLf & ex.StackTrace
strC = "Save Button Error"
MessageBox.Show(strM, strC, MessageBoxButtons.OK,
MessageBoxIcon.Error)
End Try
End Sub

Private Sub SaveRecord()
Try
'Check for new record status as form_load
'creates a new record scenario
If bNewRecord Then
'Create the new record in the dataset
Me.BindingContext(Me.DsCallsTable1, "Calls").AddNew()
End If

'Use the BindiningContext class to end the current
'editing so that we can update the server data
Me.BindingContext(Me.DsCallsTable1,
"Calls").EndCurrentEdit()

'Perform the requested task against the dataset
'using the DataAdapter's Update method
Me.sdaCallsTable.Update(Me.DsCallsTable1, "Calls")

'Accept the changes in the Dataset to pass the values back
to the server
Me.DsCallsTable1.AcceptChanges()

Catch ex As Exception
Dim strM As String, strC As String
strM = "An error occured while saving the record. The
error detail is:" & vbCrLf
strM += ex.Message & vbCrLf & ex.StackTrace
strC = "Error During Save Operation"
MessageBox.Show(strM, strC, MessageBoxButtons.OK,
MessageBoxIcon.Error)
End Try
End Sub
'------------------------------------------------------------------


The exception detail:
'------------------------------------------------------------------
An error occured while saving the record. The error detail is:
Column 'CallID' does not allow nulls.
at System.Data.DataView.FinishAddNew(Int32 currentIndex, Boolean
success)
at System.Data.DataRowView.EndEdit()
at System.Windows.Forms.CurrencyManager.EndCurrentEdit()
at System.Windows.Forms.CurrencyManager.ChangeRecordState(Int32
newPosition, Boolean validating, Boolean endCurrentEdit, Boolean
firePositionChange, Boolean pullData)
at System.Windows.Forms.CurrencyManager.UpdateIsBinding(Boolean
force)
at System.Windows.Forms.CurrencyManager.UpdateIsBinding()
at System.Windows.Forms.CurrencyManager.SuspendBinding()
at System.Windows.Forms.CurrencyManager.FindGoodRow()
at System.Windows.Forms.CurrencyManager.CurrencyManager_PushData()
at System.Windows.Forms.CurrencyManager.OnCurrentChanged(EventArgs
e)
at System.Windows.Forms.CurrencyManager.ChangeRecordState(Int32
newPosition, Boolean validating, Boolean endCurrentEdit, Boolean
firePositionChange, Boolean pullData)
at System.Windows.Forms.CurrencyManager.AddNew()
at Crisis_Intervention.frmCallEntry.SaveRecord() in H:\Source
Code\Crisis Intervention\frmCallEntry.vb:line 977
'------------------------------------------------------------------

The CallID field is an autonumbering field. I shouldn't have to pass
anything to it. So I tried removing the "CallID" field from the
DataAdapter's Select statement for the dataset. I still get the same
exception.

What am I doing wrong?

Ugh Part II

In a vain attempt to get away from the autonumber I changed the
database structure so that the CallID field is now a char(20). I
generate a new key value by doing a concatenate using:
Now.ToShortDateString + Now.ToShortTimeString

I bound the input box to the newly altered field in the dataset (yes I
re-generated the dataset).

Since there will only be one user in the system at a time I'm pretty
positive that this will give me unique keys each time. But that is
neither here nor there since I still get the "CallID cannot be NULL"
error.

So now I'm totally lost and brain-fried.

Wow. 7 Hours and no response. I'm bummed. I'm still plugging away at
this stupid thing. The user's want a demo tomorrow morning at 10:00
A.M. Just my luck.

Anyway I've reverted to an auto-key. Trying to follow the steps found
in "Database Programming with Visual Basic.Net and ADO.Net" by F. Scott
Barker. Funny how it works fine in the book's examples but not in real
life. Odd that.

Found a similar approach at
http://www.startvbdotnet.com/ado/Simplebinding.aspx but it too doesn't
work even though the concept behind the table is EXACTLY what I'm
trying to do.

Hmm..... 10.5 hours later I believe I figured a work-around.

I ditched the BindingContext class and databinding completely. Piece
of crap anyway for what I wanted to do.

Instead I manually loaded the Parameters collection for the
InsertCommand. Then:

.InsertCommand.Connection.Open()
.InsertCommand.ExecuteNonQuery()
.InsertCommand.Connection.Close()

Voila! New record added to the database WITH the auto key turned back
on. Think I'll forever give up on using the BindingContext's .AddNew()
as it simply isn't worth a 16 hour day of fighting with it trying to
figure it out.

But if I hadn't been so stubborn about getting it to work then I'd have
finished HOURS ago wouldn't I.

Anyone sense the frustration? Bah. Nobody read this post anyway. :-D

I got this problem too, and im frustrated ... f**k*ng binding

From http://www.developmentnow.com/g/40_2006_5_0_0_749052/Cannot-create-a-new-record.htm

Posted via DevelopmentNow.com Groups

I have had this problem in the past and got around it by using a currency
manager and calling 'endCurrentEdit' on the currency manager before saving.

Another trick I've used (in particular with datagrids) is to move the focus
away from the currentControl before saving, in theory there is a 'endEdit'
that is called when the user moves away from a control which moves the data
in the control to the dataset, so if the user tries to save without moving
away from the control the data is not in the dataset and can be lossed. If
this happens with a new record you'll get that null id because from the
database's point of view you're trying to save an empty record.

Usually once I figure out what is happening - that the data is displayed
correctly in the form but hasn't made it to the dataset yet - I can find
some a way to stuff it into the dataset with the currencyManager, moving the
focus away from the current control, or calling endEdit on dataRowViews
(when bound to a dataview, which is a good practice anyway).

I hope that helps;

DCraig.


[quoted text, click to view]