Link to home
Start Free TrialLog in
Avatar of MichMat
MichMat

asked on

Database query using dataset datatable or array

Hi,

A question regarding an Database query, using VB.net.
I would like to query a database to get the names and telephone numbers from the database using as a reference either an array or a datatable using sql.
like this
Select Names, Telnumbers from My database if ( the name im looking for  is in a datatable or array ) (I can create either)
at the moment Im using a loop to search for them but this is so slow I would like to get all the info out at once.

Is this possible? Or is it only possible if the names are in a nother database? And if so how can I for instance access the said data from a Access database when the names are coming from a dbf?

I have done this with 2 dbf's in the same folder quite sucessfully cutting down the search time using loops from 30 minutes to about 60 seconds and would like to do so again in this different scenario. What would be the best way to go?

Thank you

Michal
Avatar of vb_jonas
vb_jonas
Flag of Sweden image

Hi!
If I understand you right it's a common relation, that performs best on two tables in the same db.

SELECT Table1.Names,Table2.TeleNumbers FROM Table1 INNER JOIN Table2 ON Table1.Names=Table2.Names

That would also be possible to do with a linked table from an Access database to your sql database.
To use a Accesstable from within SQL, you can use OpenRowSet:
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
   'c:\docs\mydatabase.mdb';'admin';'', TableName)
Avatar of MichMat
MichMat

ASKER

Not sure that I have explained my self right.

Idealy I would like to query a database using the names that I already have in a datatable.
There might be for instance 700 names that I have extracted into a datatable from a dbf free file.
Now I want to match the names to a master database containing 800,000 names and create a NEW dataset datatable that contains details from the master database about a person if the name in the master database matches that of the datatable name. All at the same time.

I am new to all of this and basicaly know only very simple sql statments.

Michal
Is the "master database" in access or sql server? And are you using a dbf as a file, or in a sql server attached db?

You could use a dataadapter to fill data to a new datatable, using the SQL statement above. But the little tricky bit might be to link the access datatable into the dbf.

For a better explanation / working code, please post

.. some of your code.
Avatar of MichMat

ASKER

Hi
That is the trouble,
I havent got anything as yet. Currently the dbf is the only thing that is not changable, the rest I need to make up. There is no sql database rather sql statments. The master database can be anything I want im using excel file at the moment because its what I know. I access the dbf using vfpoledb and return a dataset with all the names and details. Then I loop throug the dataset and 'find' the names in the master excel file. As you can imagine it takes a long time.  I was hoping to use that dataset to construct a query fro the  master database because I have to use it for other purposes so Im trying not to duplicate.

Basicaly Im trying to find a faster method.

Michal
ASKER CERTIFIED SOLUTION
Avatar of vb_jonas
vb_jonas
Flag of Sweden image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial