Wayne Taylor (webtubbs)
asked on
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(d tbl)
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
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
bm = ComboBox2.BindingContext(d
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
ASKER
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-yyy y"") 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.
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-yyy
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.
>>
...but trying to save again results in a "Duplicate Record" error.
<<
Was there an .AcceptChanges after the first save and before the second?
Roger
...but trying to save again results in a "Duplicate Record" error.
<<
Was there an .AcceptChanges after the first save and before the second?
Roger
ASKER
No, nothing. All there is is the AddNew code, enter a few fields manually (in Textboxes), then the Update code.
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
Try
da.Update(dtbl)
dtbl.AcceptChanges
in place of
da.Update(dtbl)
Roger
ASKER
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-yyy y"") 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
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-yyy
...but then I start having problems when adding a new record (editing existing records works fine).
Any ideas?
Cheers,
Wayne
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
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
ASKER
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
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
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.OL EDB.4.0;Da ta 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-yyy y"") 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
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.OL
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-yyy
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
ASKER
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
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
ASKER
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....
I'll continue playing....
>>
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(ByVa l 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.D ata.Comman dBehavior. 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-yyy y"" (and add your ORDER BY clause) BEFORE you actually used the Select command, and got the CommandBuilder to generate update statements from it.
Roger
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(ByVa
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.D
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-yyy
Roger
ASKER
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_T arget], [Development_Percy_Monthly _Target], [Development_Percy_Unit], [Development_Occ_Actual], [Development_Occ_Daily_Tar get], [Development_Occ_Monthly_T arget], [Development_Occ_Unit], [Development_Peak_Actual], [Development_Peak_Daily_Ta rget], [Development_Peak_Monthly_ Target], [Development_Peak_Unit], [Production_Drilling_Actua l], [Production_Drilling_Daily _Target], [Production_Drilling_Month ly_Target] , [Production_Drilling_Unit] , [Stope_Backfill_Actual], [Stope_Backfill_Daily_Targ et], [Stope_Backfill_Monthly_Ta rget], [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_T arget] , [Development_Percy_Monthly _Target] , [Development_Percy_Unit] , [Development_Occ_Actual] , [Development_Occ_Daily_Tar get] , [Development_Occ_Monthly_T arget] , [Development_Occ_Unit] , [Development_Peak_Actual] , [Development_Peak_Daily_Ta rget] , [Development_Peak_Monthly_ Target] , [Development_Peak_Unit] , [Production_Drilling_Actua l] , [Production_Drilling_Daily _Target] , [Production_Drilling_Month ly_Target] , [Production_Drilling_Unit] , [Stope_Backfill_Actual] , [Stope_Backfill_Daily_Targ et] , [Stope_Backfill_Monthly_Ta rget] , [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
Using your function, I obtained this SELECT statement....
SELECT Format([Date],"dd-MMM-yyyy
...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-
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-
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]
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
>>
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
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
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
ASKER
>>Is that _all_ the binding you are using?
Yes, only those 2 lines. Nothing else.
Wayne
Yes, only those 2 lines. Nothing else.
Wayne
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
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
ASKER
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
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
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.RowStat
cm.EndCurrentEdit()
Debug.WriteLine(dr.RowStat
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