Category Archives: Database Programming

May 01

Encapsulator of XML Features in SQL Server 2000

Posted on by

This class acts as a re-useable object for calling Stored Procedures and SQL Queries that return XML from SQL Server 2000.
With a few lines of code, you can return XML from SQL Server to your application. You can pass multiple input parameters to the stored procedure as well as output parameters for returning singular data. It also lets you easily access @RETURN_VALUE parameters from your Stored Procedures so that business rules can be applied within the stored procedure. You can call multiple stored procedures and Queries with while sharing one connection. This class does all these things and hides the details from the programmer.
I have included a Visual Basic Project with this class, the project can be used to return straight XML to the client application. The project can retrieve XML from SQL Server via a Stored Procedure or an SQL statement with the ‘FOR XML [AUTO|RAW|EXPLICIT]‘ statement at the end.
I have also included an ASP page (CallSP.asp) and an XSL file (Products.xsl) to show an example of using the class from within ASP. The ASP page calls a stored procedure that has an input parameter and an output parameter. After the XML is returned to the page, we apply an XSL style sheet to transform it into straight HTML on the server using the MSXML2.DOMDocument object.
The examples use the Northwind Database that ships with SQL Server 2000. I also sent along a short sql script (SQL_Setup.sql) to create several stored procedure examples. The proper permissions must be set in SQL to view or execute the stored procedures from the Visual Basic Project as well as from an ASP Page.

Download Project

May 01

Display Recordset Data in a ListView Control

Posted on by

A Quick And Easy way to pass a Recordset to the ListView Control. Nice interface and behind the scenes actions for formatting any item in ListView.
Note: The application uses the Northwind database that ships with VB6. If you don’t have this database at C:\Program Files\Microsoft Visual Studio\VB98\NWIND.MDB, you need to change the connection string in the GetEmployeesData routine in the EmployeesData class.

Download Project

May 01

Data Shaping and Data Object Information Using ADODB and ADOX

Posted on by

This class allows you to obtain information about a database using ADOX, Microsoft’s new extensions to ADO that allow you to set and read information about database objects such as tables, fields, and indexes. It also includes a function that returns a shaped recordset (a recordset that includes a child recordset as a column) without you having to write the complex SQL syntax for Shaping.
According to Microsoft’s documentation, the Access JetOLEDB provider is the only one that fully supports ADOX. However, most of the the functions in the class have been tested with SQL Server 7.0, where they work fine. They should work with other providers (current and future) to the degree that they support the ADOX functionality included in this class.
Reviewing and using this class is an excellent way to familiarize yourself with Shaping and ADOX, which will undoubtedly soon become widely adopted technologies.

Download Project

May 01

Sample DMO, RDO, DAO and ADO Demonstration

Posted on by

The program demonstrates major VB capabilities of database accessing. It demonstrates usage of DMO, RDO, ADO and DAO data access to local database and SQL database, too. It also shows a few abilities that are extremely suitable for usage in DB application, but they are not entirely documented in the documentations dedicated to VB databases programming as well as in the MSDN.
To enable all features of this demo, you must copy the provided access database to a SQL Server database and create a DSN for the latter. Follow the instructions in the readme.htm file.

Download Project

Download File

May 01

Customers Inventory System

Posted on by

This application is suitable for a car shop. Whenever the mechanic finish a job on a car the only thing he has to do is to use the service wizard and select the parts he used from a list and the number of the parts. The application substract the number of parts used from the inventory and gives the subtotal and total price.

Download Project

Download Project