Multiple Recordsets - the ability to stuff multiple SQL query results into one Recordset - is an ADO feature that's few developers are aware of. In this article, we are going to take a look on what a Mutliple Recordset is, exactly, and how it can make your development life easier (and your code more efficient). But before we start examining Multiple Recordsets, let's look at a common task in ASP and how it is handled without using Multiple Recordsets.

Write a Review   Add Favorite   Refer it to Friend   Report Broken Link  
Date Added: Oct 10, 2006 Hits: 4 Rating: 0.00 Votes: 0

In this step by step tutorial I will guide you to build the fastest method to display records from the database using plain ASP. The technique we are going to use is based on a simple fact that you don't have to hit database every time you want to display records. Take for example a list box which displays names of countries from the database. How many times you are going to change the countries in the database? I bet not often. So if you have to show those countries from the database then it will be better to cache these records in the memory and display them from memory every time you want to display that country list to the user.

Write a Review   Add Favorite   Refer it to Friend   Report Broken Link  
Date Added: Oct 10, 2006 Hits: 3 Rating: 0.00 Votes: 0

This article looks at a neat and not often discussed feature of ADO - disconnected Recordsets. A disconnected Recordset, as its name implies, is a Recordset that lacks a connection. This may seem a bit confusing at first - how can a Recordset lack a connection to a data store? - but will hopefully become more clear as the article progresses

Write a Review   Add Favorite   Refer it to Friend   Report Broken Link  
Date Added: Oct 10, 2006 Hits: 5 Rating: 0.00 Votes: 0

Recently I was asked to develop a web based report that would collect data from a database, format it in an HTML page and then print a certain number of rows on each page with a column header. The problem with coming up with a solution such as requested, is to find out how to do a page break after a certain number of rows

Write a Review   Add Favorite   Refer it to Friend   Report Broken Link  
Date Added: Oct 10, 2006 Hits: 1 Rating: 0.00 Votes: 0

This one's like a double whammie! Welcome to the world of hard core SQL Server stored procedures and to the NextRecordset method of ADO's recordset object... Before we go on, this requires SQL Server. WON'T WORK WITH ACCESS.

That said, the goal for this example was easy... Get a list of all current tables in the database created by your's truly, get a list of dependent stored procedures for each table and do it all in a stored procedure! Easy...

There are basically 2 parts to this example. The calling application (a VBScript class called SQLServerTools) and the SQL Server stored procedure: sp_Admin_ExampleTablesDependencies (included with the class). The VBScript class is easy and contains only 1 method. That method is a great example of how to use the NextRecordset method of ADO to capture a second (or 50th) recordset returned by a procedure. For example, did you know this was valid SQL:

"SELECT * FROM table1;SELECT * FROM table2;"

Well it is, SQL Server knows that this statement should produce two separate and distinct recordsets however if you execute that and don't use NextRecordset, you will only get that first RS back. The stored procedure I wrote to do all the work is the second part of the example. That procedure returns (2 * num of tables) recordsets which is variable and based on the number of tables found in sysobjects for your particular db. Bottom line, that procedure always returns more than 1 recordset.

Right now, as you read this, I estimate that the procedure returned as many as 30 recordsets that were looped through with NextRecordset... But that's not the real magic. The class only exists to call the procedure and work with it's results. The stored procedure itself does all the work.

I've commented both the app and the stored procedure to the max but I'll give you an overview of the stored procedure... The stored procedure uses a cursor to loop through the records of a recordset returned by the system table: sysobjects. The process used is almost the same as using ADO to manipulate a recordset but right in the db itself... this means huge speed bonus and, by writing a stored procedure, all that logic is forever contained within and isn't spilled out into the class where it doesn't belong.

So anyways, while the procedure is looping through each table returned by sysobjects, it's simultaneously calling the system stored procedure: sp_depends. sp_depends is a handy function that returns a recordset of names representing every stored procedure that references the entered table! sp_depends can be used on any object, not just tables, but that is another discussion for another day...

Write a Review   Add Favorite   Refer it to Friend   Report Broken Link  
Date Added: Oct 10, 2006 Hits: 4 Rating: 0.00 Votes: 0

This article shows how to use the refresh method of the ADO Parameters collection to interrogate a stored procedure. You may already have experience using ADOX or SQL-DMO to accomplish this, but please keep in mind that the method displayed here is meant to be simple enough that anyone with ADO experience can use it. (ADOX and SQL-DMO will be addressed later.)

Write a Review   Add Favorite   Refer it to Friend   Report Broken Link  
Date Added: Oct 10, 2006 Hits: 3 Rating: 0.00 Votes: 0

Building an SQL string from optionally posted criteria is tricky. You need to check if the user posted a value since only then is the criteria included in the query. Numeric entries are different from character entries and multiple-selection fields require additional processing to produce a valid SQL query. Fields that might contain single quotes need special attention to prevent syntax errors.

Write a Review   Add Favorite   Refer it to Friend   Report Broken Link  
Date Added: Oct 10, 2006 Hits: 2 Rating: 0.00 Votes: 0

This article covers two advanced ADO topics: creating hierarchical Recordsets (via data shaping) and creating custom Recordsets. Specifically, this article looks at how to create custom hierarchical Recordsets. Therefore, it is essential that the reader have a fluid understanding of both data shaping and custom Recordsets, topics that this article doesn't aim at teaching. To learn about these topics, be sure to read the following articles

Write a Review   Add Favorite   Refer it to Friend   Report Broken Link  
Date Added: Oct 10, 2006 Hits: 1 Rating: 0.00 Votes: 0

The SQL Distributed Management objects (SQL-DMO) extend the functionality of SQL Server by providing developers a way to execute common tasks using programming and scripting languages. The first article in this series entitled "Using the SQL Distributed Management Objects", provided an overview of SQL-DMO. This article expands upon those concepts by showing you how to use SQL-DMO to create an Active Server Page application to backup and verify a SQL Server database.

Write a Review   Add Favorite   Refer it to Friend   Report Broken Link  
Date Added: Oct 10, 2006 Hits: 1 Rating: 0.00 Votes: 0

When I originally wrote my online database administration pages I went for the simple approach. As a consequence, I just displayed the values of the fields as they appeared in the tables. Here's an example of how that looked:

Write a Review   Add Favorite   Refer it to Friend   Report Broken Link  
Date Added: Oct 10, 2006 Hits: 2 Rating: 0.00 Votes: 0