One connection - Multiple Queries

[parallon]

Hello all. I am just trying to make my code a little shorter and more efficient, and one of the things I am trying to do is run multiple queries from one connection. Using the following sample, how would I manage to perform multiple queries without having to create unique variables for each one? I am sure this is probably pretty basic, but when I get something that works, I tend to move to the next thing without improving on what I already know. :)

Dim oConn, oRS, sQry
Dim sConn

set oConn = Server.CreateObject("ADODB.Connection")

sConn = MM_timesheet_STRING
oConn.Open sConn
Set oRS = Server.CreateObject("ADODB.Recordset")
Set oRS.ActiveConnection = oConn

'SQL RecordSet
sQry = "********INSERT SQL STATEMENT HERE*******"
oRS.Open sQry

Thanks,

Mike

[Old Pedant]

Do you mean that the various recordsets will be open at the same time?

Or will you close one before opening another?

And, by the by, if you think you *NEED* multiple recordsets open at the same time, this *MIGHT* be an indication of poor program design. Not always so, but more often than not.

Anyway...

Only IF you need more than one recordset open at the same time do you *NEED* to use more than one set of variables.

There is nothing wrong with doing:

set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open MM_timesheet_STRING

sQry = "...first query ..."
' try to use this form, instead of using CreateObject and Open
' it's much more efficient:
Set oRS = oConn.Execute( sQry )
... do stuff with the recordset ...
oRS.Close

sQry = "...second query ..."
Set oRS = oConn.Execute( sQry )
... do stuff with the recordset ...
oRS.Close

...

sQry = "...37th query ..."
Set oRS = oConn.Execute( sQry )
... do stuff with the recordset ...
oRS.Close

...


Of course, if you do need more than one recordset open at the same time, then you will need separate names.

set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open MM_timesheet_STRING

sQry = "...first query ..."
Set RS1 = oConn.Execute( sQry )

sQry = "...second query ..." ' no reason not to use the same string variable!
Set RS2 = oConn.Execute( sQry )

... do stuff with the recordsets ...

RS2.Close
RS1.Close

...

[parallon]

Thank you for the quick reply. I tried your first example with the following code:
set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open MM_timesheet_STRING

sQry = "SELECT Count(*) AS vCount FROM tblBBS WHERE v1_1 = 'AR' AND ObsDate Between #" & vStartDate & "# And #" & vEndDate & "#"
Set oRS = oConn.Execute( sQry )

vCount_2 = oRS("vCount").value
Response.Write(vCount_2 & "  ")
vCategory = "1.1 Hearing"

oRS.Close


sQry = "INSERT INTO tblBBS_Temp (vQryNo, vCategory, vLocQty) VALUES ('9', '" & vCategory & "', '" & vcount_2 & "')"
Set oRS = oConn.Execute( sQry )

oRS.Close

... and got the following error:
ADODB.Recordset error '800a0e78'

Operation is not allowed when the object is closed.

/bbs/rpts/inc/_9_qryTotals_AR.inc, line 24


... with Line 24 being "oRS.Close"

I even tried it with your second method and got the same error.

Thanks,

Mike

[Old Pedant]

*ONLY* SELECT operations produce a recordset.

INSERT, DELETE, and UPDATE operations do *NOT* produce a recordset, so you should never assign the result of the Execute to them.

Indeed, since they don't produce a recordset, you can't close it!

What they *DO* produce, that might be interesting, is a COUNT of the number of records affected (inserted, updated, or deleted, as appropriate).

...
sQry = "INSERT INTO tblBBS_Temp (vQryNo, vCategory, vLocQty) VALUES ('9', '" & vCategory & "', '" & vcount_2 & "')"
howMany = -1
oConn.Execute sQry, howMany
Response.Write "Executed a query the affected " & howMany & " records.<br/>"
...

Obviously, in the case of a standard INSERT, you would always expect a value of 1, only. But there are other kinds of INSERT that can create many new records. And of course UPDATE and DELETE could easily affect zero to any number of records.

You don't have to use the "howMany" trick, and surely not worth bothering for a simple one-record INSERT, but put it on you list of things worth remembering.

[parallon]

Wow, thank you so very much. I never knew ANY of that. If you only knew how many lines of code you are going to save me. I really appreciate all the help that you have provided for my many topics over the past few weeks.

Thanks again,

Mike

[Old Pedant]

Here's another change you could make to that code:

set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open MM_timesheet_STRING

sQry = "INSERT INTO tblBBS_Temp (vQryNo, vCategory, vLocQty) " _
& "SELECT 9, '1.1 Hearing', COUNT(*) " _
& "FROM tblBBS WHERE v1_1 = 'AR' " _
& "AND ObsDate Between #" & vStartDate & "# And #" & vEndDate & "#"
howMany = -1 ' the value doesn't really matter...but -1 is impossible from the Execute
oConn.Execute sQry, howMany
If howMany <> 1 Then
Response.Write "Error: INSERT added " & howMany & " records (should have been 1)"
Response.End
End If

...

If you don't *NEED* to display the COUNT(*) on the page--if the only purpose of getting it is to update the DB, then why do two steps when one, as above, will do?

[parallon]

That is interesting. I will look into that.

Thanks again,

Mike

[ghell]

You can also execute multiple statements at once using semicolons.

"SELECT * FROM foo; SELECT * FROM bar"

You can mix and match SELECT, INSERT, etc.


You can then navigate through the results. I don't remember exactly how though. Possibly using NextRecordset. See the docs for ADO Recordset (http://msdn.microsoft.com/en-us/library/ms681510%28VS.85%29.aspx)

[parallon]

That might actually help a lot since I have some code with up to 60 queries. I will definately look into this.

I also heard of the following method for counting number of instances in each of 20 columns for each of 3 possible values, which I haven't gotten to work yet. I am currently running one query for each value on each column. :( Ugly, but it works for now.
SELECT DCount("row","test","col1 = 'AR'")+DCount("row","test","col2 = 'AR'")+DCount("row","test","col3 = 'AR'")+DCount("row","test","col4 = 'AR'") AS AR,
DCount("row","test","col1 = 'no'")+DCount("row","test","col2 = 'no'")+DCount("row","test","col3 = 'no'")+DCount("row","test","col4 = 'no'") AS [NO],
DCount("row","test","col1 = 'safe'")+DCount("row","test","col2 = 'safe'")+DCount("row","test","col3 = 'safe'")+DCount("row","test","col4 = 'safe'") AS Safe
FROM test
GROUP BY DCount("row","test","col1 = 'AR'")+DCount("row","test","col2 = 'AR'")+DCount("row","test","col3 = 'AR'")+DCount("row","test","col4 = 'AR'"),
DCount("row","test","col1 = 'no'")+DCount("row","test","col2 = 'no'")+DCount("row","test","col3 = 'no'")+DCount("row","test","col4 = 'no'"),
DCount("row","test","col1 = 'safe'")+DCount("row","test","col2 = 'safe'")+DCount("row","test","col3 = 'safe'")+DCount("row","test","col4 = 'safe'");

Thanks,

Mike

[ghell]

I'm not entirely sure what you are trying to achieve but it may be best to try using a view in SQL and then just treat that like a normal table in ASP.

This is also better than using temporary tables (which I assume you are using judging by "INSERT INTO tblBBS_Temp ...")

If it is only a single number you are trying to get back, you would use a scalar function rather than a view.


It is typically easier to create directly in SQL because you can see it all, just hit F5 to test it, etc and generally everything is faster if it is done directly in SQL as well because DBMS are highly optimised and also communication between the DBMS and the application is reduced.

[Old Pedant]

Have to wonder about a DB design where you *have* to do something like that. I think it almost surely should be re-designed.

Almost surely you should have a many-to-one table with one record per current column per main record. So 20 records in the many-to-one table instead of 20 columns in the main table.

Then it would be nearly trivial to do all in one SQL statement.

Simple example showing 3 instead of 20:

Table main (wrong design):
id, other, stuff, col1, col2, col3

1, 'joe', 'peanuts', 'AR', 'AK', 'MT'

******

Right way:

Table main:
id, other, stuff
1, 'joe', 'peanuts'

Table aux:
id, colType, value
1, 1, 'AR'
1, 2, 'AK'
1, 3, 'MT'


Now, to get your counts by id, by colType, by value:

SELECT id, colType, value, COUNT(*) as howMany
FROM aux
GROUP BY id, colType, value


Is that what you are now doing the hard way with the temp table and all?

[parallon]

Well, the way that it all works is that I have a user complete a checklist with 20 different categories. Each category has 3 choices (AR, Safe, NO), along with a text box next to each category for comments. There are other things on the form including Name, Date, Location, etc. When they submit the form, I am populating tblBBS with one row for each form submitted. The columns contain each of the inputs from the form, which include the 20 categories and their selected status. So, I know that when you query a table it checks row by row, and the only way I could think to get the qantities of each status was to search for a specific status one column at a time, unless I queried one row, assigned the values of each category to separate variables, and populated an array using a Case or If/Then statement.

All these queries are to populate various reports based on tblBBS. The only reason I am using tblBBS_Temp is because all this info comes out of the DB in scattered order, and since I am not too familiar with BubbleSorts or other sorting of Arrays, I just found it easier to populate a table and call it back in the order that I needed to pupulate an array. In all actuality, I really don't even need an Array in some cases because I am just looping through the array and outputting the results to XML for the charts that I am using. Not sure if that makes sense or not.

You have both been a great help, and as a rookie, I am just looking to improve on what I currently have and stash some ideas for future work. Everything is currently working good, but I know it could run faster and be easier to modify if necessary, that is why I am seeking alternative methods.

Old Pedant, the info you gave me the other day on use of recordsets and connections reduced some of my code by up to 200 lines. For that I am very thankful.

Mike

[Old Pedant]

Yeah, sorry to be bearer of bad news, but it's past time for a DB redesign.


Table main:
id, other, stuff
1, 'joe', 'peanuts'
2, 'max', 'zamboni'

Table aux:
id, colType, value, comment
1, 1, 'AR', 'be sure to finagle this first'
1, 2, 'Safe', 'as safe as it can be
1, 3, 'AR', ''
2, 1, 'No'
... etc. ...

And then, as I said, a query such as

SELECT colType, value, COUNT(*) as howMany
FROM aux
GROUP BY colType, value
ORDER BY colType, value

would get you all your counts in one go. (You'd get 3 records per column, but of course then it's trivial to convert that to a single row in ASP code...or you could use an outer query to consolidate it.)

There are many other reasons to go with a design like this. One obvious reason: If you add a new checkbox, you don't have to change your DB, at all!