Controls bound to a DataView: Problems inserting new line/record (Question 2)

I have a form in my VB.net application (built using Visual Studio 2003) that has a listbox control (its DataSource set to MyDataView and it’s DisplayMember set to a MyDataView column). Several textfields also exist and all bound to the same MyDataView.

How do I successfully insert a new record (datarow) into the DataView with the data from the respective textboxs and then list the respective column in the ListBox with the rest, ready to update the datasource (SQL 2000 table) when the adapter’s update method is called. The adapter, dataset, SQLConnection and dataView were generated using VS 2003 wizards. I can’t get it to do the insert.

Using the following code, it just updates (over writes an existing datarow), and not insert (total number of rows +1). If I hard code the update, the record gets added twice then throws a constraint exception (duplication of the PK). If I just call the update method when the save button is clicked, nothing happens or things start to go horribly wrong. The other select, delete, updates methods all work ok.

This is how I am currently trying to do it:

'btnAddNew_Click...
  lstList.ClearSelected()
  txtBox1.Text=""
  txtBox2.Text=""
'end

'User fills out form

'btnInsert_Click...
  Me.BindingContext(MyDataView).EndCurrentEdit()
  da1.Update(MyDataSet)
'end

If my listbox originally contained 5 items, this code just overwrites one of the existing ones and so still contains 5 when it should be 6.

IvanHowarthAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SanclerCommented:
You will need to use MyDataView.AddNew to create a new record and set the binding manager's position to it with

   Me.BindingContext(MyDataView).Position = MyDataView.Count - 1

If you do that, the fields to which the controls are bound should clear automatically.  Otherwise, as you've discovered, your code is just clearing, and the user is then making changes to, the fields in an existing record.

Roger
0
IvanHowarthAuthor Commented:
Please see line marked ***

'btnAddNew_Click...
    MyDataView.AddNew()
    BindingContext(MyDataView).Position = MyDataView.Count - 1
        '***How do explicitly assign a value? I have a column that won't allow nulls that is not assigned to a control (just used in selecting a range of data from the source table)
'end

'User fills out form

'btnInsert_Click...
   Me.BindingContext(MyDataView).EndCurrentEdit()
   da1.Update(MyDataSet)
'end


0
SanclerCommented:
Try this

        MyDataView.Item(MyDataView.Count - 1).Item(MyColumnName) = MyColumnValue

Roger

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IvanHowarthAuthor Commented:
Roger - you're the man!

All my data access functions now work as desired. Thanks

Ivan
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.