Understanding data access through comboboxes

pachjo · Dec 27, 2006

Hi, I am getting a tad confused with the methods used to populate and repopulate of a combobox.

Here is the story:

I have a combobox on a form that is setup as follows:

datasource = MB2007DataSetBindingSource
displaymember = tran_description
valuemember = tran_id

MB2007DataSetBindingSource is setup as follows:

datasource = MB2007DataSet
datamember = transaction_descriptions

And there is also a data adapter named daTransactiondescriptions

When the form loads this line is executed:

Code:

Me.daTransactiondescriptions.Fill(Me.MB2007DataSet.transaction_descriptions)

Now this works a treat and as I would expect. However the problem arises when I write data to the table.

I guess I am missing something here, but this is my code:

Code:

            Try

                ' create a row from the dataset data table

                Dim drDataRow As DataRow = Me.MB2007DataSet.transaction_descriptions.NewRow()

                ' set the transaction description using the users input

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

                ' add the row to the table

                Me.MB2007DataSet.transaction_descriptions.Rows.Add(drDataRow)

                ' commit changes to database

                Me.daTransactiondescriptions.Update(Me.MB2007DataSet.transaction_descriptions)

                ' 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

this code successfully adds the new item to the combo box but I seem to be failing in getting it to commit the change to the database as when I open up the table after closing the apoplication the new entry has not been written?

Any tips please?

Thnx

Denaes · Dec 28, 2006

Okay, using da (data adapter) instead of ta (table adapter), I assume you're using 2003 and not 2005?

You're saving changes to your dataset, but they're not being persisted to the database. If you were using stored procedures, I'd say to put some logic in there to see what's happening. It also might be your SP code. Or your SQL if you're not using SP's.

I'm using 2005 and this is how I successfully perform a persist:

Visual Basic:

            ' Deleting
            taOverrideReason.Update(OverrideReason.Select("", "", DataViewRowState.Deleted))
            ' Modifying
            taOverrideReason.Update(OverrideReason.Select("", "", DataViewRowState.ModifiedCurrent))
            ' Adding
            taOverrideReason.Update(OverrideReason.Select("", "", DataViewRowState.Added))

At least in 2005, we're commiting just the changes. You could also just do a .Update(OverrideReason.GetChanges()), but we found the need to differentiate between Add/Update/Delete for when we have to update many related tables.

I can't recall doing this in 2003, it's been a while and I don't have any sample code handy so I'm not sure if it was handled differently.

pachjo · Dec 28, 2006

Hi, thanks....now I am even more confused!

I am using Visual Studio .NET 2005?

The data adapter rather than table adapter? This appeared by itself and also the coding part of this issue I learned in SAMS Teach Yourself VB 2005 in 24 hours and it used data adapters?

I somewhat lost now?

Denaes · Dec 28, 2006

When you go into your dataset and select the adapter for the table, on the properties window is it referred to as a DataAdapter or TableAdapter?

If it's a SAMS book, it might be 2005 with a TableAdapter and they were just updating it from 2003 with the DataAdapter and kept the old naming convention.

Or better yet, go to the Help Menu and "About Visual Studio" to make sure it's 2005. If it's 2005, the code I have should work. If it doesn't, it might be a problem with the SQL or SP or something else with a relationship causing an error.

I tend to think database side, because you have a try block and that should let you know if there is a problem. The database wouldn't let you know unless you're using SP's and explicitly raise an error of sorts.

pachjo · Dec 28, 2006

Hi,

so are you saying I should be using tableadpaters instead of dataadapters? I am definitely using 2005 and am a tad knocked back that a book I bought to get upto speed on 2005 from 2003 has lead me a merry dance

pachjo · Dec 28, 2006

On the properties for my dataset I have this:

daTransactiondescriptions Butters.MB2007DataSetTableAdapters.transaction_descriptionsTableAdapter

So I guess it is my naming it with da that has caused the confusion?

Denaes · Dec 28, 2006

Just made me question how much I could help if you were using DataAdapters (and VS 2003). No biggie.

Did you check to see if the code I suggested was any help?

pachjo · Dec 28, 2006

I have tried the following to try to persist the data but no joy?

Code:

'causes no errors or persist
Me.taTransactiondescriptions.Update(Me.MB2007DataSet.transaction_descriptions)

' causes no errors or persist
Me.taTransactiondescriptions.Update(Me.MB2007DataSet)

' overridereason causes error as it is not found?
Me.taTransactiondescriptions.Update(overridereason.select())

Denaes · Dec 28, 2006

Try this:

Me.taTransactiondescriptions.Update(Me.MB2007DataSet.transaction_descriptions.GetChanges())

This is telling it to update the changes as they're found.

What code do you have for your insert property on the table adapter? Is it hooked up to a Stored Procedure or SQL?

pachjo · Dec 28, 2006

Hi,

I get an error saying no overload resolution failed because no accessible update can be called with these arguments.

The way I am trying to add the new row is like this:

Code:

 Try

                ' create a row from the dataset data table

                Dim drDataRow As DataRow = Me.MB2007DataSet.transaction_descriptions.NewRow()

                ' set the transaction description using the users input

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

                ' add the row to the table

                Me.MB2007DataSet.transaction_descriptions.Rows.Add(drDataRow)

                ' commit changes to database

                Me.taTransactiondescriptions.Update(Me.MB2007DataSet.transaction_descriptions.GetChanges())

                ' 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

Denaes · Dec 28, 2006

Okay, it sounds like it found a row to insert and wasn't able to insert because the command was there in the TableAdapter.

Look on the TableAdapter, under the Property called "Insert" with a plus next to it. Click the plus to expand the options. What values do you have for "CommandText" and "CommandType"?

pachjo · Dec 28, 2006

HI,

well if you mean look at the taTransactionDescriptions in form design all I get for its properties there are:

ApplicationSettings
Name
ClearBeforeFill
GenerateMember
Modifiers

I had previoulsy manually created a command object to insert the new row and that sussessfully updated the database but would not update the combobox no matter what I tried?

This writes to the database but does not update combo?

Code:

        ' create an SQL command object to write changes to the database

            Dim cmdTransactionDescription As New System.Data.SqlClient.SqlCommand

            Dim strSQLString As String

            ' create the SQL statement using user input from detailed monthly tabpage

            strSQLString = "INSERT INTO transaction_descriptions (tran_description) VALUES ('" & Me.txtNewTransactionDescription.Text & "')"

            Try

                ' link the command to the database connection

                cmdTransactionDescription.Connection = glb_cnMB2007

                ' set the commands commandtext using the SQL statement

                cmdTransactionDescription.CommandText = strSQLString

                ' if the record was appended successfully then update the transaction display

                If cmdTransactionDescription.ExecuteNonQuery() = 1 Then

                    Me.btnSaveTransaction.Enabled = False

                    Me.btnClearTransaction.Enabled = False

                    'try to fill the data adapter with the transaction description table data

                    'Me.MB2007DataSet.Clear()

                    'Me.taTransactiondescriptions.Fill(Me.MB2007DataSet.transaction_descriptions)

                End If

                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)

            Finally

                cmdTransactionDescription.Dispose()

            End Try

pachjo · Dec 28, 2006

How my problem started out was that I had successfully updated the database using a command object but atr the combobox is linked to a databinding object to a table adapter to a dataset I was trying to get the dataset - tableadapter - databinding to refresh so the combobox would update but no joy?

I think I am getting my wires crossed on which method uses what etc etc??

Denaes · Dec 28, 2006

You can't use the Update method of a TableAdaptor without an appropriate command assigned to it.

If you use a typed dataset, this is all handled for you in the TableAdapter already as an InsertCommand, SelectCommand, DeleteCommand and UpdateCommand. You assign a SQL Statement or Stored Procedure to each of these to perform the appropriate action when you select Update on your TableAdapter.

The code you provided didn't refresh the combo because you didn't requery the database after your insert.

You have the correct code commented out: 'Me.taTransactiondescriptions.Fill(Me.MB2007DataSet.transaction_descriptions)

Without creating an object on your own as you just did, the TableAdapter doesn't know how to do anything. When you provided it with the changes, it found a row to insert, but no command to tell it how to actually perform the Insert.

Try your code with the command object with the Table Fill uncommented out. That should work.

pachjo · Dec 28, 2006

Hi, sorry that was an oversight on my part not uncommenting line you mention. It does not work. You will see I have the .clear line above which I also tried to see if that would work.

When this code runs the row is added to the database and the combobox blinks as is it has been accessed by the code but the new line does not appear. It only appears if I close the program and run it again!

So it does look like from what you were saying that I was on the right path which pleases me, but still have a situation where either the database gets updated and the combobox does not or the database does not get updated but the combobox does?

Yoinks!

Denaes · Dec 28, 2006

If you put a breakpoint on the Fill method, it's definately getting run?

Prior to Fill being run, check the Table "transaction_descriptions.rows.count" and then check it after the fill to see if it actually added the extra row.

You can use Debug.Print or a messagebox to easily check the # of rows before and after.

pachjo · Dec 28, 2006

Yes it does run and I have done the step through with a messagebox to show the count, but here are the results:

Before execution of If cmdTransactionDescription.ExecuteNonQuery() = 1 Then
there are 8 rows in the transaction_descriptions table in the dataset

The line Me.taTransactiondescriptions.Fill(Me.MB2007DataSet.transaction_descriptions)
runs deep into VS generated code that I do not understand and returns without any error.

After that the row count is still 8

After I close the program and look at the database there are 9 rows.

So I know that the database using the command method works but the dataset, tableadapter bit is not doing anything????

Denaes · Dec 28, 2006

Okay, so the Fill Method isn't working properly. It's running as you can see a refresh in the combobox and it passes that line of code, but it's not selecting all the rows.

Whats your Select SQL for that Table Adapter?

pachjo · Dec 28, 2006

Denaes said:
You can't use the Update method of a TableAdaptor without an appropriate command assigned to it.
If you use a typed dataset, this is all handled for you in the TableAdapter already as an InsertCommand, SelectCommand, DeleteCommand and UpdateCommand. You assign a SQL Statement or Stored Procedure to each of these to perform the appropriate action when you select Update on your TableAdapter.

Yes out of curiosity the dataset, databinding and table adapter were created by VS in desgin view.

So how do I set the insert command for example? When I try to access it in code I get the option .insert but it does not give me the option to assign a value to it?

pachjo · Dec 28, 2006

Denaes said:
Okay, so the Fill Method isn't working properly. It's running as you can see a refresh in the combobox and it passes that line of code, but it's not selecting all the rows.

Whats your Select SQL for that Table Adapter?

The sql for the select command is:

SELECT tran_description, tran_id FROM dbo.transaction_descriptions ORDER BY tran_description

Understanding data access through comboboxes

Newcomer

Senior Contributor

Newcomer

Senior Contributor

Newcomer

Newcomer

Senior Contributor

Newcomer

Senior Contributor

Newcomer

Senior Contributor

Newcomer

Newcomer

Senior Contributor

Newcomer

Senior Contributor

Newcomer

Senior Contributor

Newcomer

Newcomer