Problem saving new record in a form. Do I need a binding source?

[PhillD]

I have built a form which is used to input sales expenses. It consists of a few text boxes (for Expense Header information) and the DevExpress DataGrid (for Expense Details entries). The Textboxes are bound to the ExpenseHdr table in the ExpenseTickets Dataset and the grid is bound to a table called ExpenseLines in the ExpenseTickets Dataset.

When I load the form, I am able to pass in an existing ExpenseID parameter to load an existing ticket or I can pass in 0 which will instruct the form to generate a new ExpenseID and create a new Ticket.

When I pass in 0, I generate a new ExpenseID by calling a stored procedure that will return the next available ExpenseID. I then run my select statement with the new ExpenseID against the ExpenseHdr table. This will of course retrieve 0 rows and give me an empty Table inside the Dataset. I then bind the TextBoxes to the ExpenseHdr Table. I then automatically populate some information in the text boxes (like the ExpenseID etc.)

This is where the problem comes in. When I run the save procedure, I do not receive any errors nor does it save the data to the SQL database.

I have written the code myself to perform this function and I think I know what the issue is. When I auto populate the text boxes, I have not instructed the dataset/table to create a new record/row. However I added code to do that and it still did not fix the problem. Does this have anything to do with not using a binding source? The form will only ever have 1 record in it at anyone 1 time so I do not need to navigate multiple records. The grid will have multiple records loaded but it handles it's own binding.

Here is the code relevant to the ExpenseHeader, can anybody help me figure this out?

Thanks

Imports System.Data
Imports System.Data.SqlClient

Public Class ExpenseTickets

    'Connections
    Dim PhantomConn As SqlConnection

    'DataSet
    Dim ExpenseTicketsDataSet As DataSet

    'ExpenseHeader
    Dim ExpenseHdrAdapter As New SqlDataAdapter
    Dim ExpenseHdrCommandBuilder As SqlCommandBuilder

    Private Sub ExpenseTickets_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'set connection string
        PhantomConn = New SqlConnection(My.Settings.PhantomConnectionString)

        If LoadExpenseID = 0 Then
            'we are generating a new ExpenseTicket.  Get a new ExpenseID
            LoadExpenseID = GetExpenseIDKey()

            'check if LoadExpenseID now have a value
            If LoadExpenseID = 0 Then
                MsgBox("Expense ID cannot be 0, the form will now close.  Please report this error to the I.T. Department", MsgBoxStyle.Critical)
                CloseFrmDuringLoad(Me)
                Exit Sub
            End If
        End If

        'load the expense Header DataSet
        LoadExpenseHdr()

        'if we are adding a new record, auto fill some fields.
        If EditMode = "A" Then
            ExpenseTicketsDataSet.Tables("ExpenseHdr").NewRow()

            ExpenseID.Text = LoadExpenseID
            ExpenseUserID.Text = GetExpenseUserID(CurrentUser.EmployeeID)
            ExpenseStatus.Text = "E"
            EntryDate.Value = Date.Today
        End If

    End Sub

    Private Function GetExpenseIDKey() As Integer

        'This will be used to generate the Expense Type Key.

        Dim SQLComm As SqlCommand
        SQLComm = New SqlCommand("GetKeySp", PhantomConn)
        SQLComm.CommandType = CommandType.StoredProcedure

        '4 is the ID for the ExpenseID Key.
        SQLComm.Parameters.Add(New SqlParameter("@KeyID", 4))
        SQLComm.Parameters.Add("@Return", SqlDbType.Int)

        SQLComm.Parameters("@Return").Direction = ParameterDirection.ReturnValue

        Try
            PhantomConn.Open()
            SQLComm.ExecuteNonQuery()

            Return SQLComm.Parameters("@Return").Value

        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical)
            Return SQLComm.Parameters("@Return").Value
        Finally

            PhantomConn.Close()
        End Try

    End Function

    Private Sub LoadExpenseHdr()
        ExpenseHdrAdapter.SelectCommand = New SqlCommand
        ExpenseHdrAdapter.SelectCommand.Connection = PhantomConn

        ExpenseHdrAdapter.SelectCommand.CommandText = "Select * From ExpenseHdr Where ExpenseID = " & LoadExpenseID

        'automatically create insert/delete/update statements
        ExpenseHdrCommandBuilder = New SqlCommandBuilder(ExpenseHdrAdapter)

        ExpenseTicketsDataSet = New DataSet()
        ExpenseHdrAdapter.Fill(ExpenseTicketsDataSet, "ExpenseHdr")

        'bind text boxes to the dataset.
        ExpenseID.DataBindings.Add("Text", ExpenseTicketsDataSet, "ExpenseHdr.ExpenseID")
        ExpenseUserID.DataBindings.Add("Text", ExpenseTicketsDataSet, "ExpenseHdr.ExpenseUserID")
        ExpenseStatus.DataBindings.Add("Text", ExpenseTicketsDataSet, "ExpenseHdr.ExpenseStatus")
        EntryDate.DataBindings.Add("Value", ExpenseTicketsDataSet, "ExpenseHdr.EntryDate")

    End Sub



    Public Function SaveRecord() As Boolean Implements PublicFunctions.IRecordEditor.SaveRecord
        
        If Me.Validate() = True Then
            

            'run the save in a try catch statement
            Try
                'save the Expense Header record
                ExpenseHdrAdapter.Update(ExpenseTicketsDataSet, "ExpenseHdr")


            Catch Ex As Exception
                DataError = True
                MsgBox(Ex.Message, MsgBoxStyle.Critical)
            End Try

            If DataError = False Then
                MsgBox("Save Complete")

                'flag the save function as true
                Return True
            End If
        Else
            MsgBox("Cannot save, please resolve any validation errors before saving.", MsgBoxStyle.Exclamation)
            Return False
        End If
    End Function

End Class

I have also attached a screen shot of the form.

 

[PhillD]

It is also not updating any ExpenseHdr changes on existing records when the save function is called.

I must be doing something wrong but I don't understand what it is. If the form is able to show the correct values when an Existing ExpenseID is supplied, then the data binding must be working. However, when I make any changes to the data in the text box, no changes are being made to the underlying dataset. I know this because when I call

 If ExpenseTickets.HasChanges

It is returning false.

I am really confused and would really appreciate any help I can get.

 

[PhillD]

I have also tried modifying the DataBindings with the following code.

mybindingsource.DataSource = ExpenseTicketsDataSet.Tables("ExpenseHdr")


        'bind text boxes to the dataset.
        ExpenseID.DataBindings.Add("Text", mybindingsource, "ExpenseID")
        ExpenseUserID.DataBindings.Add("Text", mybindingsource, "ExpenseUserID")
        ExpenseStatus.DataBindings.Add("Text", mybindingsource, "ExpenseStatus")
        EntryDate.DataBindings.Add("Text", mybindingsource, "EntryDate")

Again the data still loads correctly, but there are no changes being detected in the dataset (HasChanges still returns false)

 

[cjard]

Always a bit of a headache when people do things in nonstandard ways.

Youre saying that your datatable has 0 rows
Then you bind textboxes (which just stay blank)
Then you populate textboxes (programmatically manipulating the controller is always a bad way of getting data into a model in MVC)
And there is never any data saved?

That's because the data you see in the textboxes didnt go anywhere: there was no row in the model to receive it, if they even were told to end their edit and send the data to the model they don't automatically add a row to a rowless table. Thus, you cannot save it becuse when the adapter comes to scan the table looking for added rows, it contains nothing


You probably wont need a binding source.. Simply adding a row to the underlying table (using a select that you know will select 0 rows is a useless non-op), then populating its values (directly into the row, not poking them in through the textboxes) and telling the currency manager to refresh will get things to a point where the text boxes will show and edit the row data.. Once edited, the row should be available for the adapter to update back to the db, but remember to perform whatever procedure is required to mark the end of the data editing and commit changes to the datarow.. If you were using the prescribed TableAdapter/DataTable/BindingSource route I'd advise validating the form, and endedit()ing the bindingsource but as those components aren't in use here I'm not sure what the corollorary is for your chosen route

 

[PhillD]

I was playing with this over the weekend and pretty much ended up where you suggested.

I kept the binding source and performed a bindingsource.AddNew before filling the text boxes and a bindingsource.EndEdit after they were populated.

When I call my save routine, I am also performing a bindingsource.EndEdit (to capture any changes) and a me.validate to perform control validations.

On the select statement, if you are adding a new "ticket" then the select statement will return 0 records to the dataset, however, if the user is editing an existing ticket, then the select statement will return a populated data table.

 

[cjard]

Yes, but if you know youre adding a new record, don't run the select (as it's a waste of the database's time).. You can't bash the designer wizards (like when I first advocated you use them) claiming it writes overly verbose, inefficient code on the one hand, then write your code with avoidable timewasting operations on the other

If youre working with related data, I'd recommend that you AddNew then EndEdit immediately on the parent, then AddNew and EndEdit immediately on the child.. then let your user edit the records (you dont need to do anything to allow them to edit, just because you called EndEdit - it doesnt prevent them editing)
Reason is, it works round what seemed to be a long standing bug with related bindingsources where the child data would vanish after the parent's EndEdit was called

 

[PhillD]

If I don't run a select statement, how do I create the dataset to store the data?

 

[cjard]

Dim ds as New DataSet 'untyped
Dim ds as New MyDataSet 'name will vary for typed dataset

Or just do the table directly:
Dim dt as New DataTable 'untyped
Dim dt as New MyDataSet.MyDatatable 'typed

dt.AddRow(...values...) 'untyped
dt.AddMyDataTableRow(..values..) 'typed

 

[cjard]

ps; Fill never creates a dataset, it only fills one you already created. TableAdapters have a GetDataBy method that returns a new datatable filled with what you ask, so:

tableadapter.Fill(existingDatatable)
newDatataable = tableAdapter.GetDataBy()

I'm not sure whether DataAdapters have a getdataby, but I know that they dynamically add columns to datatables if the columns arent there.. perhaps this si what youre referring to..

I don't work with untyped datatables, because to me it's analogous to declaring every data container in your app as List(Of Object) and losing all the type safety, compile time checking and coding assistance You can either:

Use the typed dataset designer to create a typed dataset that knows about the columns at design time
or
Have the dataadapter create the columns over and over again every time the datatable is used at runtime

To me, it's a nobrainer to choose the former, and even die hard DataAdapter fans who refuse to use the modern facilities still seem to create datasets in the designer that have all the columns they want to make.. As noted before it's the difference between:

Class Person
  name as String
  age as Integer
  birthday as Date
End Class

vs

Class GenericDataContainer
  data as Dictionary(Of String, Object)
End Class

...
Dim p as Person
p.Name = "John"
p.Age = 26

vs

Dim gdc as GenericDataContainer
gdc("name") = "John"
gdc("age") = 26

Yes, the latter is supremely flexible, but this leads to bad things:

MsgBox("person age is: " & gdc("aeg").ToString())

Compiler would have caught that if it was p.aeg

 

[PhillD]

Thanks for the help here. There are pro's and con's to using the dataset designer....some of which I have experienced.

As a useless piece of information, we recently hired 2 new developers from accross our state. 1 is a Computer Programming college professor and the other is a newly graduated student from a state college. Both have have said not to use the dataset designer...Not had much time to discuss it with them but I guess it's going to be the old school way for us.

 

[cjard]

Yeah, I wouldnt put much stay in the words of a university teacher without additional qualification from them on their viewpoint. I don't find most of the teachers I know to be very progressive; they learn what they want to teach and then teach it, then get stuck in a rut. My lecturers refused to let us use Netbeans for designing Java forms, instead insisting that everyone had to type the properties of every UI element out by hand. A visit back to my university 5 years later shows Netbeans installed as standard and the course on UI and HCI geared around use of that IDE.. (and many faces on the teaching staff replaced with new ones)
Same for a green-behind-the-ears student; he has his opinions because that's what he was taught in college by that professor. Seriously, by the time academia latches onto anything, it's already out of date..

That said, I bet both of them advocate using the forms designer to design forms. They'll probably come round to the sensibility of using the Dataset Designer in a few years time. If you'd got one of the programming leads from Microsoft to come along to your company, do you think he'd say the same?

I look forward to the reasons put forward when you have finished discussing it with them