Visual Basic 2010 and Excel

[nov0798]

I have a small form in which I need to query an Excel sheet with data in it. I have an application where I need to look up vehicles by either tag number, vehicle number, or radio id. Entering any one of the 3 inputs, returns all the other data assigned to that vehicle. So if I type Tag # ABC123, it returns data to all the other fields like vehicle color, make, model, radio id, car #, and its assignment, etc.

I have built the form in visual basic, but for the life of me cannot figure out the code to do this.

Can someone help me?

Thanks

[jmcilhinney]

How many records do you have in total? It may be easiest to simply load the whole sheet into a DataTable and then query that using LINQ.

[nov0798]

I have approximately 400-500 rows of data, and need to display about 8-10 columns of data per vehicle/resource. Ive never heard of LINQ, ill have to look that up.

[KGComputers]

Have you added reference to MS Excel object library? there you can access workbook, worksheet, range and other classes..There you can perform record retrieval/queries based on your
requirements..

[nov0798]

No I have not. I am still new to this, so I am trying to learn as I go.

[abhijit]

How many records do you have in total? It may be easiest to simply load the whole sheet into a DataTable and then query that using LINQ.

I have approximately 400-500 rows of data, and need to display about 8-10 columns of data per vehicle/resource. Ive never heard of LINQ, ill have to look that up.

This is a relatively easy thing to do.

First establish a connection to the Excel File.
Connection string will look something like this.
Next write a query to fetch records from an excel worksheet.

Dim myexcelFile As String = "c:\temp\myfile.xls"
Dim connectStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & myexcelFile & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
Dim dataTable As DataTable = PopulateDataTable(connectStr)
Dim con As New OleDbConnection(connectStr)
Const  qry As String = "SELECT * FROM [Sheet1$]"
Dim odp As New OleDbDataAdapter(qry, con)
odp.Fill(dt)

Now you have your basic data table. You can use LINQ to manipulate the contents of this table every which way you want.

[nov0798]

Thanks for the reply. I am still new to this. So how do I create the query, for data in one table, then have it fill in the rest of the form with the proper data? Can you show me an example?

Thanks again
Brian

[nov0798]

I actually have 428 rows of data, and each row has 11 columns associated with it. I need to be able to type data into any of the forms cells, and have it return all the other data associated with it.

Ive been reading, but am totally lost at this point. Can anyone help me out with this?

Thanks
Brian

[abhijit]

I actually have 428 rows of data, and each row has 11 columns associated with it. I need to be able to type data into any of the forms cells, and have it return all the other data associated with it.

Ive been reading, but am totally lost at this point. Can anyone help me out with this?

Thanks
Brian

I am not sure if your form design is capable of handling multiple results. For instance, if I were to search on year and there are 50 vehicles for the year 1998, how do you propose to display them?

[nov0798]

Oh sorry, the only searchable fields would be only the ones across the top of the form. These are all unique numbers. I was able to connect the DB to the form in VB2010, but I cannot figure out how to get the form to search the DB, and return a result. I have followed the VB2010 book, but cant get it to "search". Any direction would be wonderful. Also, since these vehicles change throughout the year, can I simply just replace the excel sheet when needed, and all the connections and lookup will remain the same?

Thanks

[KGComputers]

If your data is changing by year (dynamic), perhaps you should migrate your data into a database. Excel File corruption due to virus and other reasons can likely occur.

[abhijit]

Oh sorry, the only searchable fields would be only the ones across the top of the form. These are all unique numbers. I was able to connect the DB to the form in VB2010, but I cannot figure out how to get the form to search the DB, and return a result. I have followed the VB2010 book, but cant get it to "search". Any direction would be wonderful. Also, since these vehicles change throughout the year, can I simply just replace the excel sheet when needed, and all the connections and lookup will remain the same?

Thanks

Yes, you can replace your excel sheet every year. The key to remember is you should query the correct sheet and connect to the correct workbook.

As far as search goes, you'll have to figure that one out on your own. Querying a datatable using LINQ is a simple process.
You need to add a reference to System.Data.DataSetExtensions and start writing LINQ queries on your object.

Along the lines of something like this.

Dim vehicleCounts= From c In dt.AsQueryable()
                  New With
                  {
                      c.VehicleId,
                      Key.OrderCount = c.Vehicles.Count()
                  }

[nov0798]

Thanks for the info, but unfortunately, I am still WAY to new to completeley understand this. Can anyone help (talk me through) step by step on how to get info entered in to one text box, have it query the excel sheet, and return all the data associated with that vehicle number? If I can see it done once, I may be able to understand. Even if we use a simple set of data, such as type in BLUE in one box, and have it return YES to the other box. A simple 2 text box form that only quries a limited set of data.

BLUE YES
RED NO
GOLD MAYBE

Sorry, totally lost.

Also my data actually changes monthly, but I will keep the same Excel sheet and workbook name, just add/delete the rows I dont need, and add new ones as needed.

Thanks