Advertisement

01.26.2008 at 10:52PM PST, ID: 23114216
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

Database query using dataset datatable or array

Tags: vb.net
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
Start your free trial to view this solution
Question Stats
Zone: Programming
Question Asked By: MichMat
Solution Provided By: vb_jonas
Participating Experts: 1
Solution Grade: A
Views: 260
Translate:
Loading Advertisement...
01.26.2008 at 11:05PM PST, ID: 20752844

Rank: Guru

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
01.26.2008 at 11:11PM PST, ID: 20752865

Rank: Guru

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
01.26.2008 at 11:41PM PST, ID: 20752963

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
01.27.2008 at 01:19AM PST, ID: 20753188

Rank: Guru

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
01.27.2008 at 01:19AM PST, ID: 20753190

Rank: Guru

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
01.27.2008 at 02:40AM PST, ID: 20753317

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
01.27.2008 at 09:39PM PST, ID: 20757041

Rank: Guru

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
Loading Advertisement...
Microsoft
  • Internet Protocols
  • Applications
  • Development
  • OS
  • Hardware
  • Windows Security
Apple
  • Operating Systems
  • Hardware
  • Programming
  • Networking
  • Software
Internet
  • Search Engines
  • File Sharing
  • WebTrends / Stats
  • Spy / Ad Blockers
  • Web Browsers
  • New Net Users
  • Web Development
  • Chat / IM
  • Anti Spam
  • Web Servers
  • Anti-Virus
  • Email Clients
Gamers
  • Tips
  • Online / MMORPG
  • Puzzle
  • Emulators
  • Action / Adventure
  • Role Playing
  • Consoles
  • Game Programming
  • Strategy
  • Sports
  • Misc
  • Computer Games
Digital Living
  • Hardware
  • New Net Users
  • New Users
  • Software
  • Digital Music
  • Gaming World
  • Home Security
  • Apple
  • Networking Hardware
Virus & Spyware
  • Vulnerabilities
  • IDS
  • Encryption
  • Anti-Virus
  • Operating Systems Security
  • Software Firewalls
  • WebApplications
  • Cell Phones
  • Operating Systems
  • Internet
  • Hardware Firewalls
Hardware
  • Handhelds / PDAs
  • Displays / Monitors
  • Components
  • Networking Hardware
  • Peripherals
  • Laptops/Notebooks
  • Storage
  • Servers
  • Desktops
  • New Users
  • Misc
  • Apple
Software
  • System Utilities
  • Industry Specific
  • Network Management
  • Photos / Graphics
  • Page Layout
  • VMWare
  • Misc
  • Web Development
  • OS
  • CYGWIN
  • Voice Recognition
  • Message Queue
  • Quality Assurance
  • Security
  • Firewalls
  • MultiMedia Applications
  • Development
  • Database
  • Office / Productivity
  • Business Management
  • OS/2 Apps
  • Server Software
  • Internet / Email
ITPro
  • OS
  • Storage
  • Encryption
  • Operating Systems Security
  • Apple Hardware
  • Laptops & Notebooks
  • Servers
  • Networking Hardware
  • Peripherals
  • Devices
  • Displays / Monitors
  • WebTrends / Stats
  • Search Engines
  • Firewalls
  • WebApplications
  • IDS
  • Vulnerabilities
  • Email Clients
  • File Sharing
  • Spy / Ad Blockers
  • Web Browsers
  • Web Servers
  • Networking
  • Anti-Virus
  • Chat / IM
  • Anti Spam
Developer
  • Web Servers
  • Web Browsers
  • Game Programming
  • Dev Tools
  • Industry Specific
  • Office / Productivity
  • Database
  • CYGWIN
  • Web Development
  • Search Engines
  • File Sharing
  • WebTrends / Stats
  • Programming
  • Content Management
  • Application Servers
  • Protocols
Storage
  • Removable Backup Media
  • Storage Technology
  • Servers
  • Grid
  • Remote Access
  • Backup / Restore
  • Misc
  • Hard Drives
OS
  • Miscellaneous
  • Security
  • Development
  • Linux
  • VMWare
  • MainFrame OS
  • Unix
  • Apple
  • OS / 2
  • AS / 400
  • BeOS
  • Microsoft
  • VMS / OpenVMS
Database
  • Oracle
  • Miscellaneous
  • MySQL
  • Software
  • Sybase
  • Contact Management
  • PostgreSQL
  • Data Manipulation
  • Clarion
  • InterSystems Cache
  • Siebel
  • MUMPS
  • OLAP
  • SQLBase
  • SAS
  • GIS & GPS
  • 4GL
  • Berkeley DB
  • DB2
  • Informix
  • Interbase / Firebird
  • FoxPro
  • Reporting
  • LDAP
  • Filemaker Pro
  • MS SQL Server
  • dBase
  • MS Access
Security
  • Misc
  • Web Browsers
  • Software Firewalls
  • Operating Systems Security
  • File Sharing
  • Spy / Ad Blockers
  • Vulnerabilities
  • WebApplications
  • IDS
  • Anti-Virus
  • Encryption
  • Anti Spam
  • Email Clients
  • VPN
  • Chat / IM
Programming
  • Editors IDEs
  • Installation
  • Handhelds / PDAs
  • Multimedia Programming
  • System / Kernel
  • Algorithms
  • Game
  • Signal Processing
  • Project Management
  • Open Source
  • Database
  • Misc
  • Languages
  • Processor Platforms
  • Theory
Web Development
  • Scripting
  • Blogs
  • Web Servers
  • Software
  • Search Engines
  • Web Graphics
  • Images
  • Internet Marketing
  • Images and Photos
  • Components
  • Document Imaging
  • Web Languages/Standards
  • Illustration
  • WebApplications
  • Fonts
  • WebTrends / Stats
  • Authoring
  • Digital Camera Software
  • Miscellaneous
Networking
  • Protocols
  • Apple Networking
  • Network Management
  • Message Queue
  • Application Servers
  • Content Management
  • File Servers
  • Email Servers
  • Misc
  • Java Editors & IDEs
  • Wireless
  • Networking Hardware
  • Backup / Restore
  • System Utilities
  • ISPs & Hosting
  • Web Servers
  • Storage Technology
  • Removable Backup Media
  • Servers
  • Broadband
  • Grid
  • OS / 2
  • Novell Netware
  • Unix Networking
  • Windows Networking
  • Security
  • Telecommunications
  • Operating Systems
  • Linux Networking
Other
  • Community Advisor
  • Lounge
  • Community Support
  • New Net Users
  • Philosophy / Religion
  • Math / Science
  • Miscellaneous
  • URLs
  • Expert Lounge
  • Politics
  • Puzzles / Riddles
Community Support
  • Suggestions
  • New to EE
  • New Topics
  • Community Advisor
  • CleanUp
  • Announcements
  • General
  • Feedback
  • Input
  • EE Bugs
 
01.26.2008 at 11:05PM PST, ID: 20752844

Rank: Guru

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.
 
01.26.2008 at 11:11PM PST, ID: 20752865

Rank: Guru

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)
 
01.26.2008 at 11:41PM PST, ID: 20752963
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
 
01.27.2008 at 01:19AM PST, ID: 20753188

Rank: Guru

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

 
01.27.2008 at 01:19AM PST, ID: 20753190

Rank: Guru

.. some of your code.
 
01.27.2008 at 02:40AM PST, ID: 20753317
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
 
01.27.2008 at 09:39PM PST, ID: 20757041

Rank: Guru

Hi,
So you have a dataset.datatable with approx  700 names and details from a Visual Fox pro database. And use a datafile with 800000 names, and you would like to extract a table with those names who exists in both the 700 (+details) and the 800000, right?

When you did it with 2 dbf's, did you still work with a loop? The fastest method is running sql instructions at the same db. If you are comfortable with FoxPro then run your query there. Otherwise - run it from within Access. Since I'm used to Access here's my idea there:

Open up an Access db, and create a table with the 800000 names (you could do that, no?), call it for examle "MasterTable"
Create a linked table to your dbf - in Access tables create a new linked table, select the Fox Pro provider and your dbf, and the table within it. "DetailsTable"
Create a new table that should contain the results, "ResultsTable" - create the columns you need.

Create a new insert query something like this:

INSERT INTO ResultsTable (Name, Details)
SELECT MasterTable.Names,DetailsTable.TeleNumbers FROM MasterTable INNER JOIN DetailsTable ON MasterTable.Names=DetailsTable.Names

That query will insert all names and details into resultstable, where the names exists in both mastertable & detailstable.
Accepted Solution
 
 
20080236-EE-VQP-29 / EE_QW_2_20070628