[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 405
  • Last Modified:

CurrencyManager.Current, AddNew and inputing values

Hi Guys,

I'm having an odd problem using a CurrencyManager and a bound ComboBox.

This is what I'm using to bond the combobox....

        ComboBox2.DataSource = dtbl
        ComboBox2.DisplayMember = "MyDate"
        ComboBox2.ValueMember = "Date"
        ComboBox2.DataBindings.Add("Text", dtbl, "Date")
        bm = ComboBox2.BindingContext(dtbl)

All is good so far. I can navigate to records via the combobox, as well as back/forward buttons. The problem arises when I try to add a new record to the underlying datatable....

        Dim NewDate As String = InputBox("Enter new date in dd/mm/yyyy format.")
        cm.AddNew()
        CType(cm.Current, DataRowView).Row("Date") = Date.ParseExact(NewDate, "dd/MM/yyyy", Nothing)

I thought this would work perfect, but it doesn't. The [Date] field is left empty in the newly added record.

So, how do I go about inputting a value into the newly created record?

Cheers,

Wayne

0
Wayne Taylor (webtubbs)
Asked:
Wayne Taylor (webtubbs)
  • 10
  • 9
1 Solution
 
SanclerCommented:
Wayne

The docs for CurrencyManager.AddNew Method say

>>
You typically use this property only if you are creating your own control that incorporates the CurrencyManager. Otherwise, to add items if the data source is a DataView, use the DataView.AddNew method of the DataView class. If the data source is a DataTable, use the NewRow method and add the row to the DataRowCollection.
<<

I'd need to experiment a bit to give a full explanation for that advice but, for instance, try this

        cm.AddNew()
        Dim dr As DataRow = CType(cm.Current, DataRowView).Row
        Debug.WriteLine(dr.RowState)
        cm.EndCurrentEdit()
        Debug.WriteLine(dr.RowState)

You will find that the row is Detached in the first instance, and only becomes Added after the EndCurrentEdit.

That's a general comment.  Applying it - although without testing - to this situation, and acknowledging that the code you show may not be the full picture, my guess is that the currencymanager is not liking the record when it comes (automatically - e.g. on moving to another record) to EndCurrentEdit.  It is bound three ways - valuemember, displaymember, and text.  In what you show, whilst you may be creating a new record so far as the valuemember is concerned, another record's values are still occupying the displaymember and text spots.  That could lead to conflicts.  And, in my experience, currencymanagers will often - without reporting any errors - simply refuse to do their stuff when such conflicts occur and revert to the last (as they see it) fully valid situation.

That's all hypothesis.  I could devise a testing regime to check it out.  But my first recommendation would be to use

      Dim dr As DataRow = myTable.NewRow
      dr("Date") = Date.ParseExact(NewDate, "dd/MM/yyyy", Nothing)
      'and for the other fields
      MyTable.Rows.Add(dr)

and let the binding then bring the new valid record into the combobox.

Roger
0
 
Wayne Taylor (webtubbs)Author Commented:
Thanks Roger.

I'd tried dealing with the datatable, but still no joy. I think the underlying problem may be the table itself....

        da = New OleDbDataAdapter("SELECT *, Format([Date],""dd-MMM-yyyy"") AS MyDate FROM tblMain ORDER BY [Date]", cn)
        dtbl = New DataTable
        da.Fill(dtbl)

I did this in order to format the combobox correctly (it was showing the time as well). When I just had all the fields from the table, without MyDate, this works....

        cm.AddNew()
        Dim dr As DataRow = CType(cm.Current, DataRowView).Row
        bm.EndCurrentEdit()
        dr("Date") = Date.ParseExact(NewDate, "dd/MM/yyyy", Nothing)

...although when saving back to the database, this works the first time around....

        bm.EndCurrentEdit()
        Dim cmd As New OleDbCommandBuilder(da)
        cmd.QuotePrefix = "["
        cmd.QuoteSuffix = "]"
        da.UpdateCommand = cmd.GetUpdateCommand

        Try
            da.Update(dtbl)
        Catch ex As OleDbException
            MsgBox(ex.Message)
        End Try

...but trying to save again results in a "Duplicate Record" error.
0
 
SanclerCommented:
>>
...but trying to save again results in a "Duplicate Record" error.
<<

Was there an .AcceptChanges after the first save and before the second?

Roger
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
Wayne Taylor (webtubbs)Author Commented:
No, nothing. All there is is the AddNew code, enter a few fields manually (in Textboxes), then the Update code.
0
 
SanclerCommented:
If you don't put .AcceptChanges after the first update, the row will still be marked as .Added.  So, on the second update, it will try to insert it again.  It will be a duplicate of an already existing record.

Try

            da.Update(dtbl)
            dtbl.AcceptChanges

in place of

            da.Update(dtbl)

Roger
0
 
Wayne Taylor (webtubbs)Author Commented:
OK, that's got that little problem fixed. Which brings me back to why this was happening in the first place.

I was originally using this to fill the datatable....

    da = New OleDbDataAdapter("SELECT * FROM tblMain ORDER BY [Date]", cn)

...but that was causing the dates in the combobox to show the full date/time. So, to "fix" that, I used this....

    da = New OleDbDataAdapter("SELECT *, Format([Date],""dd-MMM-yyyy"") AS MyDate FROM tblMain ORDER BY [Date]", cn)

...but then I start having problems when adding a new record (editing existing records works fine).

Any ideas?

Cheers,

Wayne
0
 
SanclerCommented:
What, precisely, is the structure of your database table?  What columns/fields does it have apart from Date, and what is the primary key?

In trying a test with a stripped down database table - just Date, datatype Date/Time, set as the Primary Key - I'm getting an error you haven't yet reported and, examining the da InsertCommand commandtext, I see why

INSERT INTO [tblMain] ([Date], [Date]) VALUES (?, ?)

I've not come across that before, but I've never, so far as I can recall, used a Select statement that brought over the same field/column twice - once raw and once formatted.

I've not tested furtehr because my thoughts were, in any event, moving towards bringing over the date unformatted and then adding a column to the datatable in the app, putting the formatted string value that you want in that, and using that as the DisplayMember.  Does that idea appeal?  Would it work in your setup?

Roger
0
 
Wayne Taylor (webtubbs)Author Commented:
There are a total of 33 fields, but most follow the same theme....

Date, Hoisting_Actual, Hoisting_Daily_Target, Hoisting_Monthly_Target, Hoisting_Unit

Date is the primary key.

>>moving towards bringing over the date unformatted and then adding a column to the datatable in the app, putting the formatted string value that you want in that, and using that as the DisplayMember

This would work, but because there is about 3000 records, it might slow things down a little too much. I'll give it a try in the morning and report back.

Thanks for your help.

Wayne
0
 
SanclerCommented:
Wayne

This is working for me.  One form, one combo - cbo - one button, this code

Imports System.Data.OleDb

Public Class Form1

    Private conString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test\test.mdb"
    Private con As New OleDbConnection(conString)
    Private da As OleDbDataAdapter
    Private dt As New DataTable("TestTable")

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        da = New OleDbDataAdapter("SELECT Format([Date],""dd-MMM-yyyy"") AS MyDate FROM tblMain ORDER BY [Date]", con)

        Dim cmd As New OleDbCommandBuilder(da)
        cmd.QuotePrefix = "["
        cmd.QuoteSuffix = "]"

        da.Fill(dt)
        cbo.DataSource = dt
        cbo.DisplayMember = "MyDate"

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim NewDate As String = InputBox("Enter new date in dd/mm/yyyy format.")
        Dim dr As DataRow = dt.NewRow
        dr("MyDate") = Format(CDate(NewDate), "dd-MMM-yyyy")
        dt.Rows.Add(dr)
        da.Update(dt)
        dt.AcceptChanges()

    End Sub

End Class

The set up is much simplified.  tblMain in Test.mdb is only one field - Date, datatype Date/Time, set as Primary Key.  But the difference, which I think is crucial, is that the [Date] field/column is not brought over in its raw state as well as its formatted state.  Everything is done just with the formatted value.  And that's enough to send the date back to the database as a Date.

Roger
0
 
Wayne Taylor (webtubbs)Author Commented:
Roger,

There's a problem with that though. The database I'm pulling the data from is somewhat dynamic. At the moment there are 8 "areas". Each area has 4 fields....

_Actual
_Daily_Target
_Monthly_Target
_Unit

...with the area name as the prefix. This is done so it's possible to add another area if needed fairly easily, by adding another 4 fields to the database.

I'm going to try bringing in the formatted data seperately, then copying accross to the main table, overwriting the dat/time values in the original [Date] column.

Wayne
0
 
Wayne Taylor (webtubbs)Author Commented:
Jeez, this is starting to bug me. I'm now able to add a new record to the datatable, but now I'm getting a Concurrency Violation when I try to update the database.

I'll continue playing....
0
 
SanclerCommented:
>>
There's a problem with that though. The database I'm pulling the data from is somewhat dynamic.
<<

So you need to use "*"?

If so, try this

    Private Function CreateSelectStatement(ByVal tn As String) As String

        Dim s As String = "SELECT * FROM " & tn
        Dim columns As String

        Dim cmd As New OleDbCommand(s, con)
        con.Open()
        Dim dr As OleDbDataReader = cmd.ExecuteReader(System.Data.CommandBehavior.KeyInfo)
        Dim dt As DataTable = dr.GetSchemaTable
        con.Close()

        columns = dt.Rows(0)("ColumnName")
        For i As Integer = 1 To dt.Rows.Count - 1
            columns &= ", " & dt.Rows(i)("ColumnName")
        Next
        Return s.Replace("*", columns)

    End Function

That fills out "*" with the actual field/column names.  Within what that returns, you could then replace Date with Format([Date],""dd-MMM-yyyy"" (and add your ORDER BY clause) BEFORE you actually used the Select command, and got the CommandBuilder to generate update statements from it.

Roger
0
 
Wayne Taylor (webtubbs)Author Commented:
Thanks Roger.

Using your function, I obtained this SELECT statement....

SELECT Format([Date],"dd-MMM-yyyy") AS MyDate, [Hoisting_Actual], [Hoisting_Daily_Target], [Hoisting_Monthly_Target], [Hoisting_Unit], [Development_Percy_Actual], [Development_Percy_Daily_Target], [Development_Percy_Monthly_Target], [Development_Percy_Unit], [Development_Occ_Actual], [Development_Occ_Daily_Target], [Development_Occ_Monthly_Target], [Development_Occ_Unit], [Development_Peak_Actual], [Development_Peak_Daily_Target], [Development_Peak_Monthly_Target], [Development_Peak_Unit], [Production_Drilling_Actual], [Production_Drilling_Daily_Target], [Production_Drilling_Monthly_Target], [Production_Drilling_Unit], [Stope_Backfill_Actual], [Stope_Backfill_Daily_Target], [Stope_Backfill_Monthly_Target], [Stope_Backfill_Unit], [Trucking_Actual], [Trucking_Daily_Target], [Trucking_Monthly_Target], [Trucking_Unit], [Bogged_Actual], [Bogged_Daily_Target], [Bogged_Monthly_Target], [Bogged_Unit] FROM tblMain ORDER BY [Date] ASC

...but when I try to enter a value for MyDate with this code....

        bm.EndCurrentEdit()
        Dim dr As DataRow = CType(bm.Current, DataRowView).Row
        dr("MyDate") = Date.ParseExact(NewDate, "dd/MM/yyyy", Nothing).ToString("dd-MMM-yy")
        bm.EndCurrentEdit()

...I get an error saying column 'MyDate' is read-only. I am able to add a new row like this though....

        Dim dr As DataRow = dtbl.NewRow
        dr("MyDate") = Date.ParseExact(NewDate, "dd/MM/yyyy", Nothing).ToString("dd-MMM-yy")
        dtbl.Rows.Add(dr)

..but then when I go to transfer, I get a "Index or Primary connot contain a Null value" error.

And using the CommandBuilder I got this INSERT statement....

INSERT INTO [tblMain]( [Date] , [Hoisting_Actual] , [Hoisting_Daily_Target] , [Hoisting_Monthly_Target] , [Hoisting_Unit] , [Development_Percy_Actual] , [Development_Percy_Daily_Target] , [Development_Percy_Monthly_Target] , [Development_Percy_Unit] , [Development_Occ_Actual] , [Development_Occ_Daily_Target] , [Development_Occ_Monthly_Target] , [Development_Occ_Unit] , [Development_Peak_Actual] , [Development_Peak_Daily_Target] , [Development_Peak_Monthly_Target] , [Development_Peak_Unit] , [Production_Drilling_Actual] , [Production_Drilling_Daily_Target] , [Production_Drilling_Monthly_Target] , [Production_Drilling_Unit] , [Stope_Backfill_Actual] , [Stope_Backfill_Daily_Target] , [Stope_Backfill_Monthly_Target] , [Stope_Backfill_Unit] , [Trucking_Actual] , [Trucking_Daily_Target] , [Trucking_Monthly_Target] , [Trucking_Unit] , [Bogged_Actual] , [Bogged_Daily_Target] , [Bogged_Monthly_Target] , [Bogged_Unit] ) VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? )

However, I still get the Concurrency error when I go to update the database with a new record.

I'm starting to think an entire rethink of the process may be on the cards....

Wayne
0
 
SanclerCommented:
>>
I'm starting to think an entire rethink of the process may be on the cards....
<<

I'm tending to agree.  Quite apart from the specific problems here, there's a couple of issues that gave me pause for thought.  One was a combobox with 8,000 entries in the form dd-MMM-yyyy in date order.  I wasn't sure how user friendly that would be.  The other was using the Date as a Primary Key.

But I've just extended my test - adding a "Detail" field to the table in Access - and this code is performing OK for me on all my tests.  One form, one combo, one textbox, three buttons.

Imports System.Data.OleDb

Public Class Form1

    Private conString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test\test.mdb"
    Private WithEvents con As New OleDbConnection(conString)
    Private da As OleDbDataAdapter
    Private dtbl As New DataTable("Main")

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim s As String = CreateSelectStatement("tblMain")
        Dim sql = s.Replace("Date", "Format([Date],""dd-MMM-yyyy"") AS MyDate") & " ORDER BY [Date]"

        Debug.WriteLine(sql)

        da = New OleDbDataAdapter(sql, con)
        da.Fill(dtbl)
        Dim cb As New OleDbCommandBuilder(da)
        cb.QuotePrefix = "["
        cb.QuoteSuffix = "]"

        Debug.WriteLine(cb.GetDeleteCommand.CommandText)
        Debug.WriteLine(cb.GetInsertCommand.CommandText)
        Debug.WriteLine(cb.GetUpdateCommand.CommandText)

        ComboBox1.DataSource = dtbl
        ComboBox1.DisplayMember = "MyDate"
        TextBox1.DataBindings.Add("Text", dtbl, "Detail")

    End Sub

    Private Function CreateSelectStatement(ByVal tn As String) As String

        Dim s As String = "SELECT * FROM " & tn
        Dim columns As String

        Dim cmd As New OleDbCommand(s, con)
        con.Open()
        Dim dr As OleDbDataReader = cmd.ExecuteReader(System.Data.CommandBehavior.KeyInfo)
        Dim dt As DataTable = dr.GetSchemaTable
        con.Close()

        columns = dt.Rows(0)("ColumnName")
        For i As Integer = 1 To dt.Rows.Count - 1
            columns &= ", " & dt.Rows(i)("ColumnName")
        Next
        Return s.Replace("*", columns)

    End Function

    Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim NewDate As String = InputBox("Enter new date in dd/mm/yyyy format.")
        Dim dr As DataRow = dtbl.NewRow
        dr("MyDate") = Date.ParseExact(NewDate, "dd/MM/yyyy", Nothing).ToString("dd-MMM-yy")
        dr("Detail") = "New detail"
        dtbl.Rows.Add(dr)
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        da.Update(dtbl)
        dtbl.AcceptChanges()
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        dtbl.Rows(0)("MyDate") = "01-Jan-2007"
    End Sub
End Class

Here's the debug.output from it

SELECT Format([Date],"dd-MMM-yyyy") AS MyDate, Detail FROM tblMain ORDER BY [Date]
DELETE FROM [tblMain] WHERE (([Date] = ?) AND ((? = 1 AND [Detail] IS NULL) OR ([Detail] = ?)))
INSERT INTO [tblMain] ([Date], [Detail]) VALUES (?, ?)
UPDATE [tblMain] SET [Date] = ?, [Detail] = ? WHERE (([Date] = ?) AND ((? = 1 AND [Detail] IS NULL) OR ([Detail] = ?)))

Roger
0
 
Wayne Taylor (webtubbs)Author Commented:
Roger,

When I bind the combobox like this....

     ComboBox1.DataSource = dtbl
     ComboBox1.DisplayMember = "MyDate"

...all the combobox displays is a series of "System.Data.DataRowView".

>>One was a combobox with 8,000 entries in the form dd-MMM-yyyy in date order

There aren't 8000 records yet (not yet filled), but eventually I will only be getting the top 100 records.

Wayne
0
 
SanclerCommented:
>>
When I bind the combobox like this....

     ComboBox1.DataSource = dtbl
     ComboBox1.DisplayMember = "MyDate"

...all the combobox displays is a series of "System.Data.DataRowView".
<<

Is that _all_ the binding you are using?  Or have you still included the

        ComboBox2.ValueMember = "Date"
        ComboBox2.DataBindings.Add("Text", dtbl, "Date")
 
from your first post [though, with the new names, it would be ComboBox1 rather than 2]?  If so, that would explain the problem because there is now no field in the datatable called "Date".

Roger
0
 
Wayne Taylor (webtubbs)Author Commented:
>>Is that _all_ the binding you are using?

Yes, only those 2 lines. Nothing else.

Wayne
0
 
SanclerCommented:
I'm foxed.  It's always difficult dealing with this sort of thing at a distance.  Have you tried - just as a temporary see what happens thing - for instance putting some other field from the table as .DisplayMember?

Another thing.  Strictly, it's better if the order of the lines is reversed

     ComboBox1.DisplayMember = "MyDate"
     ComboBox1.DataSource = dtbl

Another thought.  Do you have any code in any event connected with the combo?

Roger
0
 
Wayne Taylor (webtubbs)Author Commented:
Roger,

Never mind the "System.Data.DataRowView" problem. Turns out it was just a stupid spelling mistake I made.

Other than that, it works! I did have to put a cm.EndCurrentEdit() before updating the database, but it otherwise works fantastic.

Many thanks for your help.

Wayne
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 10
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now