In the company I am working in right now, they are still using a very old database server which is MS SQL Server 6.5. We have a program that query the database for customer information. The program was designed in-house 10 years ago by the person I am replacing. There are some issues with the design of the program and we can not make any changes to it as there are no source codes to be found. The only chance is to write a new program that connects to the existing database.

Visual Studio 2008 is the plateform I use to write my programs. However, it supports native connection to MS SQL Server 7.0 or newer only. To connect to SQL Server 6.5, I had to use a different method, similar to the MySQL connection I have talked about earlier.

Setting Up ODBC Connection

To connect to the database in question, I need to setup an ODBC connection in the operating system first. To do that in Windows XP, follow the steps below:

1. Start > Control Panel > Administrative Tools > Data Sources (ODBC)
2. Under the System DNS tab, click Add
   
3. Select SQL Server and click Finish
   
4. Enter a name for the connection and select the server that is hosting the SQL database, click Next
   
5. Select the second option to use SQL Server Authentication and enter the user ID and password, click Next
   
6. On the next page, leave the settings as-is and click Next
   
7. Click Finish
   
8. Click Test Data Source. If the settings are correct, it should tell you the test is successful.
9. Click OK until all screens are closed.

Visual Basic to ODBC

After you have setup the ODBC connection in your operating system, you can use the following codes to establish the connection in Visual Basic. You need to replace the variables in the connection string and the SQL query in the odbcCommand.

Dim conn As New System.Data.Odbc.OdbcConnection
conn.ConnectionString = "Dsn=VB_ODBC;uid=user;pwd=password"
conn.Open()
Dim dbCommand As OdbcCommand
Dim dbAdapter As OdbcDataAdapter
Dim dbTable As Data.DataTable
dbCommand = New OdbcCommand("SELECT * FROM customer", conn)
dbAdapter = New OdbcDataAdapter(dbCommand)
dbTable = New Data.DataTable
dbAdapter.Fill(dbTable)
dbAdapter = Nothing
dbCommand = Nothing


Display Results in Datagridview

With the codes above, you have now loaded the results of the query “SELECT * FROM customer” into a data table in the memory. You can then display the data table containing the query results in your program such as a datagridview.

DataGridView1.DataSource = dbTable

At the end, remember to close the database connection with the following code.

conn.Close()

You should find that the codes above are very similar to the codes to setup with MySQL. You just need to replace the MySQL naming to ODBC.