AboutJeff Laatsch Expertise I can answer programming questions related to: C, C++, PHP, PERL, VB, SQL, ASP, JAVA, JS, XHTML, XML, CSS, PL/SQL, AWK, SED, VI Editor and if I don`t know the answer I have 20+ years systems experience as my resource.
I can handle general questions in most areas of computer science including systems integration, API, networking related issues.
Experience An accomplished WEB Master/Developer, Programmer Analyst, Systems Administrator with 12+ years of industry background in full system management and technical hardware/software support expertise including SUN, HP-UX, AIX, LINUX, and Windows NT,Win2000, 2003 with Active Directory: Well rounded in software engineering principles, requirements gathering, analyst, definition, system architectural design, OOA, OOD, UML, SEI-CMM and SDLC: Knowledgeable in Data Warehouse, Data Mart technologies, and experienced working with ORACLE databases utilizing ERP, SQL, PHP, PERL, C, C++, PL/SQL,SQL, JAVA, VB.NET, SOAP and other Dynamic WEB technologies: Analytical thinker and problem solver: Goal oriented professional who has a Masters in Computer Systems with emphasis in computer programming (C++ and Application Development).
Education/Credentials City University, WA 12/03
Master of Science in Computer Systems
with Emphasis in Computer programming (C++/App Development)
Chapman University, WA 6/01
Bachelor of Science in Computer Information Systems
Emphasis in Computer networks and security
20+ years of experience in the Computer Science Industry
Awards and Honors 7/96,9/96,10/96,1/97,2/98,6/99,10/01,7/02,01/03,
Outstanding Achievement & Special Recognition
I have three tables, say X,Y,Z. Each of the three tables
is linked by a unique ID field.How can create a recordset using SQL in
VB6 so that the corrseponding records in each of the tables
can modified or deleted at once??
Multiple Recordsets - the ability to stuff multiple SQL query results into one Recordset - is an ADO feature that's few developers
When developing ASP pages, there are times when our ASP programming logic requires us to retrieve multiple recordsets that originate from multiple tables that have no direct relationship between them. (Retrieving multiple recordsets means that we use more than one SQL SELECT statement. ) But that is not too difficult a task for an intermediate or experienced ASP programmer. Usually the common solution would be to:
Get the first recordset...
Doing some processing with it...
Close it and get the second recordset...
Process it, close it, get the third, ... and so on...
First, create an instance of ADO Recordset and populate the first Recordset with the appropriate data from the xxxxx table, and do some processing with that Recordset:
example:
'adjust this to your own
sConnectString = "DRIVER={sql server};SERVER=localhost;" & _
"DATABASE=northwind;UID=sa;PWD="
'Populate the Recordset
set oRS = Server.CreateObject("ADODB.Recordset")
oRS.Open "SELECT categoryId, categoryName FROM Categories " & _
"WHERE categoryId > 3", sConnectString
'Now we've got the recordset, and do some processing with its records
Do While Not oRs.EOF
'......some processing here
oRs.MoveNext
Loop
oRS.Close
Once we've finished processing the Categories table Recordset, we're ready to populate the second Recordset from Region table, and also do some processing with it.
oRS.Open "SELECT RegionId, RegionDescription FROM Region", sConnectString
'Now we've got the recordset, and do some processing with its records
Do While Not oRs.EOF
'......some processing here
oRs.MoveNext
Loop
oRS.Close
Okay, at this point, we're 100% done with our Recordset objects, so we need to do clean up.
Set oRS = Nothing
Yes, we've accomplished what we want
In order to make our code more readable and easy to maintain, it's better we declare our SQL SELECT statements like the one below (don't forget to put semicolon between SQL SELECT statements).
sSQL = "SELECT categoryId, categoryName FROM Categories WHERE categoryId > 3"
sSQL = sSQL & ";SELECT RegionId , RegionDescription FROM Region"
sSQL = sSQL & ";SELECT ShipperID, CompanyName, Phone FROM Shippers"
set oRS = Server.CreateObject("ADODB.Recordset")
oRs.Open sSQL, sConnectString
If you like to use SQL stored procedures, you can utilize multiple Recordsets by creating a stored procedure that returns multiple SQL queries, like so:
Create Procedure MyResults
AS
SELECT categoryId, categoryName FROM Categories WHERE categoryId > 3
SELECT RegionId, RegionDescription FROM Region
SELECT ShipperID, CompanyName, Phone FROM Shippers
And then create the multiple Recordset as follows:
'Retrieve the multiple Recordsets
set oRS = Server.CreateObject("ADODB.Recordset")
oRs.Open SQL, sConnectString
'Work with the first Recordset (the Categories table)
Do While Not oRs.EOF
'......some processing here
oRs.MoveNext
Loop
'Move to the next Recordset
Set oRS = oRS.NextRecordset()
'Work with the second Recordset (the Region table)
Do While Not oRs.EOF
'......some processing here
oRs.MoveNext
Loop
'Clean up...
oRS.Close
Set oRS = Nothing
There are some things to be wary of when using Multiple Recordsets. Unfortunately, you can't use the NextRecordset method with an ADO disconnected Recordset. Also not all data providers support multiple Recordsets
