Database Development FAQs & Tutorials [updated May 13 2011]

[si_the_geek]

Database Development FAQ

The following is a list of frequently asked questions for working with databases, including how to use databases from VB, and creating/designing/managing databases.

Sections in this Page

For all languages/databases:

General
Database Design
SQL (not SQL Server, but the query language for all database systems!)
Installation

Programming language specific:

VB.Net (VB2002 and later) (includes "VB Express")
Classic VB (VB6 and earlier) - ADO
Classic VB (VB6 and earlier) - Data-bound controls (eg: Data Control/ADODC/Data Environment)

Database system specific:

SQL Server
MS Access
Oracle
MySQL

Note that if you have a question which isn't answered here, it may be in another of our FAQ threads, such as the Classic VB FAQs, or the Office Development FAQs

If you can think of a "frequent" question (and preferably an answer to it as well) that you think should be added to these FAQs, please see this thread.
____________________________________________________________
Recent Additions

• May 13 2011 : [General] How can I run an ADODB based program that was compiled on Windows 7 SP1?
• Mar 8 2011 : [General] How to connect to a database on the internet
• Mar 29 2010 : [General] Why dont AutoNumber/Identity/etc re-use deleted numbers?

____________________________________________________________


General

• What does this error mean, and how do I fix it?
  
  
• How can I run an ADODB based program that was compiled on Windows 7 SP1?
  see: http://support.microsoft.com/kb/2517589
  
  
• How can I use a database in my program?
  For Classic VB (VB6 and earlier) see the Classic VB - ADO section.
  For VB.Net (VB2002 or later, and Express) see VB.Net section.
  
  
• How can I get the auto-generated AutoNumber/Identity value for the record just added?
  
  
• Why don't AutoNumber/Identity/etc re-use deleted numbers?
  
  
• Which database system should I use?
  
  
• How to connect to a database on the internet
  
  
• What is an Index, and how does it help with searching/sorting?
  See the replies in this thread (especially posts #2 and #4).
  
  
• Where can I find a tool to query a database?
  See Sybase database edit tool in the UtilityBank forum.

up to Section list

Database design

• What is Database Normalization?
  Why multiple tables are a good thing, and how to set them up
  
  
• What names should I NOT use for tables/fields/views/stored procedures/...?
  (includes listings of Reserved Words)
  
  
• Why is it not recommended to store Date or Time values in a Text/Char based data type?
  (note that there are some exceptions, but read it before making that decision)
  

up to Section list

SQL

• Where can I find a tutorial/reference guide for SQL?
  
  • General (includes explanations and examples of several types of SQL statements)
  • multi-table queries
  • complex Updates
  • Reference for SQL with Access: Tutorial / Reference / more info (also see this article for how to read data from other databases)
  • Reference for SQL with SQL Server: Reference
  • Reference for SQL with MySQL: Reference
    
  
  
• Why should I use Parameters in my program instead of putting values directly into my SQL string?
  (aka "how do I protect against SQL Injection attacks")
  
  
• How do I use values (numbers, strings, dates) in SQL statements?
  
  
• How do I put the ' character into an SQL string?
  
  
• How can I put the value of a variable/control into my SQL statement?
  
  
• How can I find out why my SQL statement isn't working?
  
  
• Stored procedures: How can I add optional parameters to a Where clause, without building the statement in a string?
  
  
• How can I work with parent-child data, where a child can also be a parent? (SQL Server example)

up to Section list

Installation

• Where can I get MDAC, or Drivers/Providers for Sql Server/Access/Excel?
  
  
• Where can I get a Driver/Provider for MySql?
  See apt section of the MySQL web site, here.
  
  
• How can I Re-install MDAC 2.8 RTM (Window XP SP2)?

up to Section list

[si_the_geek]

VB.NET (VB2002 and later)

• An Introduction to ADO.NET ( Tutorial )
  Also see Retrieving and Saving Data in Databases from our CodeBank forum
  
  
• What is the difference between ADO (which was for older versions of VB) and ADO.Net?
  See the MS article ADO.NET for the ADO Programmer
  
  
• How can I fill a DataTable using a DataReader?
  See Filling a DataTable using a DataReader in our CodeBank forum
  
  
• When running in VB rather than compiled, why do changes to my data not get saved?
  See How to: Manage Local Data Files in Your Project at MSDN.
  
  
• How can I call Stored Procedures from ADO.Net?
  See this article at CodeGuru.
  
  
• How can I use Transactions in ADO.Net?
  See this article at CodeGuru.
  
  
• How can I read & write BLOB (picture/file/..) data using ADO.Net?
  See Saving Images in Databases from our CodeBank, and/or this article at MSDN
  
  
• How can I create an Access database using code?
  See this article at CodeGuru.
  
  
• ADO.NET Entity Framework Tutorial
  ADO.NET Entity Framework maps business entities in your database to logical objects in your code allowing you to work across them in an easy, efficient manner.
  
  
• Where can I find the ADO.Net documentation?
  It can be found online at MSDN here
  

up to Section list

Classic VB (VB6 and earlier) - ADO

• ADO Tutorial (this is an updated version of Beacon's ADO Tutorial, and includes various corrections)
  for help with extending the example in the tutorial, see ADO Beginners Tutorial, Some Further Steps.
  
  
• What do the parameters of the recordset.Open method mean? (includes advice on which CursorType and LockType to use)
  
  
• Why do I get the error "Invalid use of Null" when using field values?
  
  
• Why does Recordcount sometimes equal -1?
  
  
• How can I fill a ComboBox/ListBox with values from a database?
  
  
• How can I fill a ListView with values from a database?
  
  
• How can I add a record to a database?
  (includes using a Recordset, connection.Execute, a Command object, and a Stored Procedure - and discusses which is best for different situations)
  
  
• How can I work with dates correctly? (or "Why are my dates not working properly?")
  
  
• How do I use a Command object? (for why it is a good idea, see "Why should I use Parameters..." in the SQL section above)
  
  
• How do I get just a range of records, as you do in search results? (Paging)
  
  
• How can I store images (or other files) in a database?
  
  
• How can I use the Shape command?
  See This article at Microsoft.com
  
  
• Where can I find the ADO documentation?
  It can be found online at MSDN here, and includes many useful things such as: Recordset object, Connection object, Classic VB code examples, and ADOX
  
  
• ...If you have problems with code that isn't database specific, you may find the answer in our Classic VB FAQs

up to Section list

Classic VB (VB6 and earlier) - Data-bound controls

• Why is using bound controls a bad thing?
  
  
• Why doesn't my Data Control reload data when I set the RecordSource?

up to Section list

[si_the_geek]

SQL Server

• SQL Server 2005 useful info
  (contains several links in Post #4, including the Free version of SQL Server 2005, tools for it, and the help files [Books Online])
  
  
• How can I configure SQL Server 2005 to allow remote connections?
  See this MS KB article
  
  
• How can I use Windows Authentication with a Pocket PC?
  
  
• How can I import/export data from other database systems/files?
  See this thread and this thread from our Office Development FAQ's, which use Excel files as an example (the same methods can be used for other sources/targets).
  
  
• How can I write a Stored Procedure?
  See this tutorial
  
  
• How can I debug a Stored Procedure?
  See this tutorial
  
  
• How can I enable connections from other computers?
  See this MS KB article
  
  
• How can I create/use a CLR (.Net code) function?
  See Creating A CLR Function (SQL SERVER 2005) in our Tutorials forum.
  
  
• ...For help with SQL statements, see the articles in the SQL section of this page.

up to Section list

MS Access

• If my program uses an Access database, do my users need Access installed?
  
  
• How do I use an Access database from multiple computers (in a network)?
  
  
• How can I use an MS Access database which is stored on a Web server?
  See How to access remote MS Access database in the CodeBank forum.
  
  
• ...For help with SQL statements, see the articles in the SQL section of this page.
  
  
• ...More articles about Access (including use from VB code) can be found in the Access section of our Office Development FAQ's
  (includes items such as 'How do I perform a Compact & Repair on my database?')

up to Section list

Oracle

• Returning a recordset from an Oracle SP, and Testing a SP from SQL*Plus
  
  
• ...For help with SQL statements, see the articles in the SQL section of this page.

up to Section list

MySQL

• Where can I get the .Net provider for MySQL?
  See .Net connector on the MySQL web site, documentation can be found here.
  
  
• ...For help with SQL statements, see the articles in the SQL section of this page.

up to Section list