Variable from SQL 2

[jeellison]

Good day everyone.

Why I am having to learn ASP is beyond me but would this be the best way to list the amount of open jobs in a db?

Anyway can somebody tell me why this isnt working.

<%
DIM count_open_jobs
Set count_open_jobs = "SELECT COUNT(*) FROM tynansdb.tynan_jobs WHERE job_status = ("open") "
%>

<p>There are currently <%= (count_open_jobs)%> Open Jobs

Im no programmer!!

Thanks
James

 

[gmmastros]

There are a couple problems with this.

First, it appears as though you are trying to embed quotes within your SQL query. Most databases use single-quotes to delimit strings.

Second problem, you are using an aggregate Count(*), so you should also use an Alias for that column.

Third, you are not actually executing the query. You need to have a database object where you execute the query.

Lastly, you need to use the recordset object when displaying the value on the page.

<% 
DIM count_open_jobs
Set count_open_jobs = [blue]DB[/blue][!].Execute[/!] "SELECT COUNT(*) [!]As OpenJobCount[/!] FROM tynansdb.tynan_jobs WHERE job_status = [!]'[/!]open[!]'[/!]"
%>

<p>There are currently <%=count_open_jobs[!]("OpenJobCount")[/!]%> Open Jobs

That part in blue represents an ADODB.Connection object. You probably already have one of these on this page (or another page). The name (DB) may be different. Everything else can probably be copy/pasted as is.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jeellison]

Yes I think it does George thanks

So you can't just run a query and store the result as a variable? You need to use a alias ie OpenJobCount?

I take it the ADODB connection objest is in here :
<%
Dim Recordset1
Dim Recordset1_cmd
Dim Recordset1_numRows

Set Recordset1_cmd = Server.CreateObject ("ADODB.Command")
Recordset1_cmd.ActiveConnection = MM_tynans_mysql_STRING
Recordset1_cmd.CommandText = "SELECT * FROM tynansdb.tynan_jobs"
Recordset1_cmd.Prepared = true

Set Recordset1 = Recordset1_cmd.Execute
Recordset1_numRows = 0
%>

and it is recordset1 or recordset1_cmd?

Ive tried both and still getting the following error :

Microsoft VBScript compilation error '800a0401'

Expected end of statement

/summary.asp, line 19

Set count_open_jobs = Recordset1.Execute "SELECT COUNT(*) As OpenJobCount FROM tynansdb.tynan_jobs WHERE job_status = 'open'"
-----------------------------------------^

Also ive tried it without the .Execute

Thanks
James

 

[gmmastros]

Well.... not exactly. Your method uses a command object, and I was talking about a connection object. They're similar, but different.

You could probably do this....

<%
Dim Recordset1
Dim Recordset1_cmd
Dim Recordset1_numRows
Dim OpenJobCount

[green]' Create a command object[/green]
Set Recordset1_cmd = Server.CreateObject ("ADODB.Command")

[green]' Set the connection string[/green]
Recordset1_cmd.ActiveConnection = MM_tynans_mysql_STRING

[green]' Set the query[/green]
Recordset1_cmd.CommandText = "SELECT COUNT(*) As OpenJobCount FROM tynansdb.tynan_jobs WHERE job_status = 'open'" 
Recordset1_cmd.Prepared = true

[green]' Execute the query, storing the results in Recordset1[/green]
Set Recordset1 = Recordset1_cmd.Execute

[green]' Set the local OpenJobCount variable to the value
' stored in the recordset[/green]
OpenJobCount = Recordset1("OpenJobCount")

Recordset1.Close
Set Recordset1 = Nothing
Set Recordset1_cmd = Nothing

%>

<p>There are currently <%=OpenJobCount%> Open Jobs

Notice that I added comments to the code to help you understand what's going on. Also notice that I added another variable to represent the OpenJobCount. I also changed the code where you display this on the page (so it uses the local variable instead of the recordset object.

I did this because I like to have the clean-up code immediately follow the code where you use it. In this case, you are using 2 objects, a command object, and a recordset object. Notice how the objects are used and destroyed as quickly as possible. This helps with memory usage on your server as well as (slight) performance improvements. Your query will return a single value, but it's being stored in a recordset, (which can accommodate multiple rows and multiple columns). Since this is a single value, you can set it to a local variable and use it later.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jeellison]

George,

THANK YOU.

I think I understand, but neverless it is now working.

So if I was to create a variable for the amount of closed jobs I take it I do exactly the same (in the same page) but with a variable called closedjobcount.

Thanks again.

James

 

[jeellison]

IE is it ok to do this (and it works)

<%
Dim Recordset1
Dim Recordset1_cmd
Dim Recordset1_numRows

Set Recordset1_cmd = Server.CreateObject ("ADODB.Command")
Recordset1_cmd.ActiveConnection = MM_tynans_mysql_STRING
Recordset1_cmd.CommandText = "SELECT COUNT(*) As OpenJobCount FROM tynansdb.tynan_jobs WHERE job_status = 'open'"
Recordset1_cmd.Prepared = true

Set Recordset1 = Recordset1_cmd.Execute
Recordset1_numRows = 0
OpenJobCount = Recordset1("OpenJobCount")

Recordset1.Close
Set Recordset1 = Nothing
Set Recordset1_cmd = Nothing
%>

<%
Dim Recordset2
Dim Recordset2_cmd
Dim Recordset2_numRows

Set Recordset2_cmd = Server.CreateObject ("ADODB.Command")
Recordset2_cmd.ActiveConnection = MM_tynans_mysql_STRING
Recordset2_cmd.CommandText = "SELECT COUNT(*) As ClosedJobCount FROM tynansdb.tynan_jobs WHERE engineer_name = '" & session("varuser") & "' "
Recordset2_cmd.Prepared = true

Set Recordset2 = Recordset2_cmd.Execute
Recordset2_numRows = 0
ClosedJobCount = Recordset2("ClosedJobCount")

Recordset2.Close
Set Recordset2 = Nothing
Set Recordset2_cmd = Nothing
%>

 

[gmmastros]

is it ok to do this (and it works)

If it works.... who am I to argue.

However, if you would have asked if this was the best way... I would have to say, "It's pretty good, but can be better". You see, every time you 'go to the database', there is going to be a penalty for it. That penalty is performance. Now, understand that I'm NOT suggesting it's going to take a long time. And it's entirely possible that what I about to suggest will actually be slower, but it's something to consider as you move forward with the development.

First, let's take a look at the 2 queries you have.
[tt]
SELECT COUNT(*) As OpenJobCount
FROM tynansdb.tynan_jobs
WHERE job_status = 'open'

SELECT COUNT(*) As ClosedJobCount
FROM tynansdb.tynan_jobs
WHERE engineer_name = 'Joe'
[/tt]
Notice how the 2 queries are very similar. In fact, the only difference is in the where clause. As such, the 2 queries can be combined in to one query instead. Like this...

Select Sum(Case When Job_Status = 'open' Then 1 Else 0 End) as OpenJobCount,
       Sum(Case When engineer_name = 'Joe' Then 1 Else 0 End) As ClosedJobCount
FROM   tynansdb.tynan_jobs

Summing 1 is the same as count, so you can get multiple 'Counts' from the same query.

I said earlier that this method may actually be slower. And it's true. You'll have to test the performance to know. You see, when you have a WHERE clause, the SQL engine can make effective use of indexes, which would make the query faster. Since each count has a different filter condition, the separate queries could each use their own index. Combined, there is no where clause, and therefore, no indexes would be used. Of course, if you don't already have indexes on the Engineer_Name column or the job_status column, then the individual queries wouldn't be using indexes either, so the combined query may be faster.

Of course, there are other ways that you can combine the query (using sql) so that indexes would be used.

Select (
       SELECT COUNT(*)
       FROM   tynansdb.tynan_jobs 
       WHERE  job_status = 'open'
       ) As OpenJobCount,
       (
       SELECT COUNT(*)
       FROM   tynansdb.tynan_jobs 
       WHERE  engineer_name = 'Joe'
       ) As ClosedJobCount

This query uses sub-queries to calculate the values. Since each query is left 'as-is', each sub query would be able to use indexes to return the values faster.

Please understand that the difference in execution time may be VERY minimal. In my opinion, this is no excuse for inaction. You see, as tables get larger, the performance becomes more important. Also, as the number of users increases, it's more important to have faster performance. All of this leads to scalability. I encourage you to make the effort to understand the techniques I describe here. This particular page you are working on may not benefit from these techniques. It's better for you to understand them now, so that you can apply these techniques to other queries/pages you may need to work on later.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom