To read the article online, visit http://www.4GuysFromRolla.com/webtech/011900-1.shtml

A Component to List the System DSNs on a Web Server

By Mike Shaffer


At various times during the development cycle for a project, I often need to examine the structure of databases that are sitting on my servers. I wanted to have a tool that would allow me to quickly and easily choose from a list of registered DSN's and then show me a list of all tables within that database, as well as the layout of each table.

This article is about a VB ActiveX component that returns a list of system DSN's. This may be a useful tool for some of you. For example, in the article Creating a SQL Test Page, Geno Timlin showed how a user could select a particular database through a list box of DSNs. However, these selectable DSNs had to be hard coded into the ASP page. This component would assist with Geno's application, automatically populating the list box with all of the DSNs available on the Web server.

To obtain a list of registered DSN's on the web server (either PWS or IIS), I created a VB5 component (this code would also work with VB6). You will have to download the source code, compile it into an ActiveX DLL, and register it on the web server using regsvr32. Or you can simply download the pre-built DLL and register it on the web server using regsvr32. (To learn more about using regsvr32 to register components on the web server, be sure to check out this article.)

The component contains a single function, GetDSNs() which returns a list of the System DSNs on the web server in a delimited string. The delimiter used is the pipe. An example output from this function would be:

MQIS (SQL Server)|4Guys (SQL Server)|forum (SQL Server)|classified (SQL Server)|

This delimited string can then be parsed to create a select box! There is an example of this on 4Guys for you to check out before downloading the source and DLL. The source code for the application can be viewed at the bottom of the article, or downloaded as a VB project!

Happy Programming!


Attachments:

  • See the component in action
  • Download the DSNList2.dll

    Source Code:

  • Download the DSNList.cls Class
  • Download the DSNList.vbp Project


    Following is the source code for the VB component.

    '::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: '::: ::: '::: TITLE: DSNLister (ActiveX DLL) ::: '::: AUTHOR: Mike Shaffer ::: '::: DATE: 21-Dec-99 ::: '::: PURPOSE: Returns a list of DSN's with a vertical bar as ::: '::: a delimiter and a tab character separating the ::: '::: DSN name and the driver spec. ::: '::: ::: '::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: Option Explicit Private Const SQL_SUCCESS As Long = 0 ' ODBC Success Private Const SQL_ERROR As Long = -1 ' ODBC Error Private Const SQL_FETCH_NEXT As Long = 1 ' ODBC Move Next Private Declare Function SQLDataSources Lib "ODBC32.DLL" _ (ByVal hEnv As Long, ByVal fDirection _ As Integer, ByVal szDSN As String, _ ByVal cbDSNMax As Integer, pcbDSN As Integer, _ ByVal szDescription As String, ByVal cbDescriptionMax _ As Integer, pcbDescription As Integer) As Integer Private Declare Function SQLAllocEnv Lib "ODBC32.DLL" _ (env As Long) As Integer ' Public Function GetDSNs() As String ':::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: '::: ::: '::: This routine does the actual work ::: '::: ::: ':::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: ' Dim intRetCode As Integer ' the return code Dim strDSNItem As String ' the dsn name Dim strDRVItem As String ' the driver name Dim strDSN As String ' the formatted dsn name Dim intDSNLen As Integer ' the length of the dsn name Dim intDRVLen As Integer ' the length of the driver name Dim hEnv As Long ' handle to the environment Dim strTemp As String ' Tempspace Dim strDSNTemp As String ' Tempspace On Error Resume Next If (SQLAllocEnv(hEnv) <> SQL_ERROR) Then Do strDSNItem = Space$(1024) strDRVItem = Space$(1024) intRetCode = SQLDataSources(hEnv, SQL_FETCH_NEXT, strDSNItem, _ Len(strDSNItem), intDSNLen, strDRVItem, _ Len(strDRVItem), intDRVLen) strDSN = Left$(strDSNItem, intDSNLen) If (Len(strDSN) > 0) And (strDSN <> Space$(intDSNLen)) Then strDSNTemp = strDSN & vbTab & "(" & _ Left$(strDRVItem, intDRVLen) & ")|" ' Check for dupes... If InStr(strTemp, strDSNTemp) = 0 Then strTemp = strTemp & strDSNTemp End If End If Loop Until intRetCode <> SQL_SUCCESS End If GetDSNs = strTemp End Function


  • Article Information
    Article Title: A Component to List the System DSNs on a Web Server
    Article Author: Mike Shaffer
    Published Date: Wednesday, January 19, 2000
    Article URL: http://www.4GuysFromRolla.com/webtech/011900-1.shtml


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers