Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Working with Data in Visual Basic 2010

Posted on 2010-11-19
Medium Priority
Last Modified: 2012-05-10
I'm strugging trying to get my head around working with data in Visual BASIC 2010. The specific problems I have at the moment are:

I have a form that reads data from an access table that now looks like this - and it works is as much as I can see the data, navaguie through it etc but how can I

1. Use another text box to allow me to enter a location and display just the data that matches the location

2. How can I select a whole record and add it to another table (by pressing a button)

Any help much appreciated - I can bost the code I have so far (which is not much), if it helps
Question by:KCTS
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
LVL 52

Expert Comment

by:Carl Tawn
ID: 34175599
Can you post a few more details of how you constructed your form and how you are currently getting your data? You should be able apply a filter to the data you are retrieving, either once it is bound or at the source; but the specifics will depend on how you currently have your form working.
LVL 70

Author Comment

ID: 34195770
OK i've attached the project as it stands so far as a zip file - there is not much in it presently

Essentially there are two tables tbl_master and tbl_allocated which appear side by side on a form

I want to be able to do two things

1. be able to filter the left most table by location - ir I want to put a textbox/combo box on the form, enter a location and then have the dataset on the left update to show only matching records.

2. I want to be able to click the > button and copy the record from the table on the left to the table on the right
(it would be nice to have the option to COPY or MOVE the record)

If I can provide any further help please let me know

( I have had to add .txt to some file extentions as the zip file would not upload to the EE site with the default extentions present - please just remove any .txt extentions)
LVL 70

Author Comment

ID: 34195779
Here is the main form if it helps
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.


Expert Comment

ID: 34216492
I have explained the way of programatic selecting, filtering and inserting data from/to tables in your post/question "Copying rows from one table to another in Visual Basic 2010".

Now I see more clear what you need. It seems more easy that I thought.

As I see you can do the "select" by using DataSet.Tables(0).Select("ID = " + textID.Text). So, you enter desired row ID in textID. When you execute select statement you get array of Rows with exactly one row (array length will be one). To present it in datagrid on the left side you should "link" datagrid with filtering results.
I do this on following way:
1. Create DataView object using DataTable (of Dataset) and row filter as:
Dim dvr as DataView
dvr = New DataView(ds.Tables(0), "ID = "+desiredID, "ID", DataViewRowState.CurrentRows)
2. Bind your datagrid to the DataView as : yourDataGrid.DataSource = dvr
3. You should take care about presented columns in datagrid

Now, when you enter the ID in above textID box, and refresh datagrid (for example press enter or press the button), the left grid will show filtered row from table1.

when you press button to copy from left grid to right grid you need to do following:
1. put all desired data (shown in left grid) in some variables.
2. create insert sql command to the table2 (which is presented by right grid)
3. execute insert statement into the table2
4. refresh right grid on the same way as you did it with left grid when you defined desired ID (you have all necessary data for doing this).
if you want to do MOVE from table1 to table2, yo will just need to do first copy data to table2 (as described) and then do DELETE from table1 where table1.ID = desiredID, and at the end to refresh left grid (since you deleted slected row). When refreshing left grid, you should take decision what to do: clear grid or position to previous or next ID from table1.

LVL 70

Author Comment

ID: 34216595
I'm finding this quite difficult to follow - is it possible for tou tp provide an example ?

Accepted Solution

pnedic earned 2000 total points
ID: 34216627
Haha, I was afreid of this.

Well that what you want to do is not small peace of code (who ever coded it). On the other hand, this is not as complicated as long my answer is.
In the your second question, I wrote some parts of code, but I will try to write complete code and post it (but now I'm little bit bussy).
Just try one more to read my previous answer, till I post some code.

LVL 83

Expert Comment

ID: 34218492
You can do this

dTable.DefaultView.RowFilter = "Type=1"
Dim dTemp as DataTable = dTable.DefaultView.ToTable()
LVL 70

Author Comment

ID: 34218652
Where do I put that code and how can it be used to define the filter
LVL 83

Expert Comment

ID: 34218681
That depends on you app. Are you using datatables?

>1. be able to filter the left most table by location - ir I want to put a textbox/combo box on the form, enter a location and then have the dataset on the left update to show only matching records.

Then you would put the first line in the button click for filter. Or if you use combobox then you can put the first line in combobox_selectedindexchange event.

You can put the second line in > button click and assign the dTemp as datasource of second grid.

Assisted Solution

pnedic earned 2000 total points
ID: 34229538
Lets your text box for entering ID name txtRwID. Your ">" button name as btnCopy.

First what you need is to filter data using entered desired ID (in txtRwID) and present filtered data in left grid.
Second you need to make code for Copy or Move, as CodeCruiser said, in _Click event of btnCopy.

I see you are using BindingNavigator object for navigating data of table. In order to link your BindingNavigator with dataGrid and present data, you should read help about BindingNavigator class of MS documentation (you can see it at Here can find explanation how to link grid with navigator, and how to select current record. So this is your first set of actions.

If you don't want to use bindingNavigator and manually filter rows then you need to use one of approaches that are counted in previous answers (Select method on DataTable or RowFilter on DataView)

Now you create onClick event for btnCopy as:
Private Sub btnCopy_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCopy.Click

'here you will:
'1. pick up selected data in left grid: either by taking data from grid or by select sql statement.
'2. create insert sql to insert data into table2
'3. Execute insert sql
'4. Refresh right grid (position it to the newly inserted record).
'5. Optionally, delete record from table1 using delete sql statement. And refresh left grid.
End Sub

That is how it works.

LVL 70

Author Comment

ID: 34256531
Sorry for the delay in getting back to you - have been away from work due to SNOW!

I will try the suggestions shortly

Featured Post

CHALLENGE LAB: Troubleshooting Connectivity Issues

Goal: Fix the connectivity issue in the lab's AWS environment so that you can SSH into the provided EC2 instance.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What my article will show is if you ever had to do processing to a listbox without being able to just select all the items in it. My software Visual Studio 2008 crystal report v11 My issue was I wanted to add crystal report to a form and show…
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA.…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA:…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question