Refreshing a combobox does not work

[pachjo]

Hi, I have a form that has a combobox bound to a bindingsource which correctly populates on startup.

I then have a text box which is used to add new entries to the underlying database which should then reflect in the combobox, but does not?

When I save the value from the text box the entry successfully writes to the database and when I .fill the table via the tableadapter the combobox flicks to show it has been refreshed.

However when looking at the combobox the new item does not appear. I have stepped through the code and the row count remains the same before and after the .fill command so for some reason the refresh/reload of the underlying table is not happening and I cannot find out why?

I used the exact same code that loads the data at the beginning which works on first opening the form so can't see why it wont work again??

I vaguely remeber way back in early VS.NET that there were times I had to move all the desiger genreated code for a dataset to the end of the desginer generated block to overcome a similar problem, but have tried that and that does not work either.

Any ideas out there?

I have attached the project, with .exe files removed should anyone want to see the problem first hand?

The password to access the detailed monthly tab is set to 123.

Look at the combobox for transaction descriptions to see the entries. Then add one in the text box below and you will see the combobox flick but no new entry? Then look at the database and see the new entry was written???

Thanks in hope....

 

[pachjo]

Not sure why I should have to do it this way, but if it works dont knock it?

I removed all the guff from the design view and coded the lot and it works?

I created a tableadapter, commandbuilder and datatable and linked the combobox to this.

Now when I do the update it all works a treat

 

[gpoch]

Pachjo,

I've dug through some of your code and I think your creating alot of extra work for yourself.

I rarely work with datasets but I would suggest reading some articles on the possibilities of datasets. For example why not make your changes to the dataset and let the dataset do the work for you. All inserts, edits, deletes, and fills can be done by a dataset and a data adapter. They can even handle stored procedures.

[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] newTdRow [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] MB2007DataSet.transaction_descriptionsRow[/SIZE]
[SIZE=2]newTdRow = MB2007DataSet.transaction_descriptions.Newtransaction_descriptionsRow[/SIZE]
[SIZE=2]newTdRow.tran_description = [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].txtNewTransactionDescription.Text[/SIZE]
[SIZE=2]MB2007DataSet.transaction_descriptions.Rows.Add(newTdRow)[/SIZE]

Now you don't have to create the SQL code to update the database and re-fill the Dataset when you do changes, plus any binding controls will update automatically.

One more thing. Avoid inserting literal values into your SQL statements. Better yet check out this link...
http://www.vbdotnetforums.com/showthread.php?t=15955

There is alot of info on MSDN and other sites that will give you walkthroughs on datasets.

BTW you can get your ConnectionString by using

My.Settings[SIZE=2].MB2007ConnectionString[/SIZE]

 

[pachjo]

Hi, thanks for your comments. I agree with what you say, I did not like the idea of using an SQL command as I had. But from my last post you will se that I had finally overcome the problem, this code fits the bill:

            Try

                ' create a row from the dataset data table

                Dim drDataRow As DataRow = glb_dtTransactionDesc.NewRow

                ' set the transaction description using the users input

                drDataRow("tran_description") = Me.txtNewTransactionDescription.Text

                ' add the row to the table

                glb_dtTransactionDesc.Rows.Add(drDataRow)

                ' commit changes to database

                glb_taTransactionDesc.Update(glb_dtTransactionDesc)

                ' clear the user input field and set focus

                Me.txtNewTransactionDescription.Clear()

                Me.txtNewTransactionDescription.Select()

            Catch objError As Exception

                ' tell user no connection made

                MessageBox.Show("Failed to save transaction description to the database" & vbCrLf & vbCrLf & objError.Message, "Save Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

            End Try

I can get this to work with the use of a tableadapter, datatable and datarow without reference to a dataset.

I am puzzled that I can get it all to work fine by using code, yet adding a dataset and bindingsorce in design mode sets things all bonkers??

Another thing I find is that if I use an SQL statement that references more than one table then I am unable to use the .UPDATE method as it complains about multiple tables? In such cases I have to resort to manually creating the SQL to do the updating.

Oh and thanks for the tip regarding glb_cnMB2007.ConnectionString.....EXCELLENT

 

[pachjo]

Pachjo,
BTW you can get your ConnectionString by using

My.Settings[SIZE=2].MB2007ConnectionString[/SIZE]

Mmm not sure why but when I use this method I am unable to write back to the database? Has been driving me bonkers as even previously working code stopped working!

I thought back to what changes I had made and it was the one above. I restored it back to the literal string I used previously and all works fine.

So can anyone explain why the above appears to make the database connection read-only and why when the .update was pefromed it returned true with no error but without writing to the database?

 

[gpoch]

Check the settings tab in the project properties and compare the connection string to your connection string.

Also, take a peek at this.
http://blogs.msdn.com/sqlexpress/archive/2006/07/17/668971.aspx

 

[pachjo]

HI,

well the only difference is that the .settings one uses |DataDirectory| entry, everything else is the same, Integrated Security=True, User Instance=True?

Ohh edit.... Of course the literal string points to the actual live copy I would expect to use, the latter points to the debug copy that gets overwritten! Well done that man for bringing this to light!

Also, very uselful link thanks. I am suprised at this explanation though about how the file gets over written and that to get around it you have to custom code it?

Interesting....