Refreshing Binding navigator / source

[ecaf]

Hoping someone can help on this, I have searched without success so far.

I have a form with databound text boxes and combo boxes using a Bindsource and Binding Navigator.
I use the Add new button (of the Binding Navigator) to clear the text boxes for a new record, but then have a Update button on the form where I do an Insert query to SQL, which works because if I refresh the table in SQL the new row is there.

However when I then use the Binding Navigator 'move previous' button I get an error saying "Column 'AllowOverbook' does not allow nulls. " - now this is a column that is defined in the database to have 'Allow Nulls' = False, but I do not display this field on my form because in this case it is not required.
Instead I have it included on my Insert statement and just set it to '0'
Also when you look at the table every entry in the AllowOverbook column is '0' and there are no NULLS.

I think I need to refresh the Binding Navigator or Binding Source or the table adapter, but I have tried a few things, which I put in straight after executing the Insert statement, such as:

BindingSource1.ResetBindings(False) - I also tried TRUE
BindingNavigator1.Update
TableAdapter.GetData()

I have also tried putting in a text box for this field and defaulting it to 0, used as a hidden field - it is bound but has no effect

Nothing seems to stop this error when I try to navigate other rows straight after an Insert. But if I close the form and re-open it, I can immediately navigate through all the records and can see the most recently added record.

The reason I have the Update button is because I want to validate the fields before updating. Any help on this is appreciated.

 

[TyB]

Just a thought

I mostly use SQL Data Bases in my projects, but thinking back to VB6 days. You might want to try doing a move record next after you update so the DB saves the record.

It has been awhile so this may not even help and I have never had to do this with a SQL DB.

Ty

 

[ecaf]

Hi Ty,
I tried what you said I put in BindingSource1.MoveNext after executing the Insert query. I just got the same error message about column does not allow nulls, except I got it now after clicking update instead of the usual clicking update and then clicking on the record navigator.

I'm not sure what your point is about the SQL database? I am using a SQL database... I have text boxes bound to a datasource, but am manually using an "Insert INTO..." query to add the new record.

 

[TyB]

Sorry if I'm miss understanding

Are you getting the Null error when you do the command.ExecuteNonQuery()?

If not then we know the Db is accepting the data without issue, but when you navigate next it seems like the pointer before your INSERT is already set to EOF.

Maybe if the Navigator is at the last record and you add a new one maybe try MoveLastItem

Let me admit right up front that I have never used navigator or binding source and most of my VB.net is on aspx web pages and many years of VB6 which are generally not helpful in .NET.

Ty

 

[ecaf]

Are you getting the Null error when you do the command.ExecuteNonQuery()?

If not then we know the Db is accepting the data without issue, but when you navigate next it seems like the pointer before your INSERT is already set to EOF.

No, it is after this, as I said its when I try to do a record navigation - whereas the data has already been written to the table.

Maybe if the Navigator is at the last record and you add a new one maybe try MoveLastItem

Ok so I have the following code in the Update button (for add new record)

myCommand.ExecuteNonQuery()
BindingSource1.MoveLast()

Which is fine, the new record gets added to the database, and when you look at the record count on the navigator it shows record 16 of 16
So then I click the 'Previous record' button on the binding navigator, this is when the message pops up again saying the column cannot accept nulls.

Maybe I misunderstood where you want me to put the .MoveLast?
I almost have this working correctly - I really hope I can resolve this, it would be common for a user to enter a new record and then scroll back through the records, its the 1st thing I tried during testing so I assume they will too.
(thanks for the help so far, I understand you might not use these functions, but maybe there are some others that do that might?)

 

[TyB]

How about...

How about......

myCommand.ExecuteNonQuery()
BindingSource1.MoveFirst()

The the recordset is moved to the first record. Does it matter if they have to hit back to go to other records or forward? Is the user most likely going to want to see the previous record?

Hope any of this is helping.

Ty

 

[ecaf]

No, it didn't work...
I tried the .MoveFirst - which just resulted in a message box popping up with the same error, because it was caught in the catch section.

I also tried:
myCommand.ExecuteNonQuery()
BindingSource1.ResetBindings(False)
BindingSource1.MoveLast()

Which I thought might work because I had no error... but then clicked on the record navigation button again, and got the about:blank page again with the same error.

I'm starting to think maybe I should remove the navigator and just add my own buttons or something? I should be still able to bind the text & combo boxes with the navigator hidden, shouldn't I?

 

[TyB]

Try this link

This might help if you deside to do your own navigation.

VB.NET-BindingSource

 

[ecaf]

Thanks, I tried one or two but no success. Going to have to come back to that when I get a chance next week.