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
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
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)
SELECT *
FROM OPENROWSET('Microsoft.Jet.
'c:\docs\mydatabase.mdb';'
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
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
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.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If I understand you right it's a common relation, that performs best on two tables in the same db.
SELECT Table1.Names,Table2.TeleNu
That would also be possible to do with a linked table from an Access database to your sql database.