| Author |
 Topic  |
|
|
Intrepidone
Average Member
Canada
515 Posts |
 Posted - 22 May 2002 : 21:52:31
|
Hi, I am stuck here and cannot figure this out. I am trying to create a recordcount from one of my tables on a db I am working on.
I am trying to pull the count out of a column named 'UnitEquipID' from a table named 'Laptops'. It works so far with the code below if I hard code in what I am trying to count, ie:Tecra 8100, I have 3 Tecra's in my db, 7 E500's, etc... and since I have it hard coded here it shows 3 as the count.
<%
set rsUnitEquipID=server.createobject("adodb.recordset")
rsUnitEquipID.open "select * from [Laptops] where UnitEquipID='Tecra 8100'",myDsn ,3,3
EquipID = rsUnitEquipID.recordcount
set rsUnitEquipID = nothing
strTotalEquipID = EquipID
%>
<b><%= strTotalEquipID %></b>
<%
Now, what I want to accomplish is this. In the UnitEquipID column under the Laptops table, I may have many descriptions, like the example above, Tecra 8100, or Tecra 8000, Compaq E500, etc... now how can I have it so when I click on the link that pulls this information up, say I click on the link for the E500 it will do a count of how many E500's I have, or Tecra 8100's, etc.... without hard coding it in.
I hope I didn't confuse anyone with what I tryed to say cause I am confused for all of us 
Intrepidone
Edited by - Intrepidone on 22 May 2002 21:53:35 |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 22 May 2002 : 22:02:12
|
I'd change the SQL:
<%
set rsUnitEquipID=server.createobject("adodb.recordset")
rsUnitEquipID.open "select Count(UnitEquipID) As eTotal from [Laptops] where UnitEquipID='Tecra 8100'",myDsn ,3,3
EquipID = rsUnitEquipID("eTotal")
rsUnitEquipID.Close
set rsUnitEquipID = nothing
strTotalEquipID = EquipID%> <-- This is not really necessary because you can Response.Write EquipID
<b><%= strTotalEquipID %></b>
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs
Edited by - ruirib on 22 May 2002 22:39:48 |
 |
|
|
Intrepidone
Average Member
Canada
515 Posts |
Posted - 23 May 2002 : 06:28:14
|
Thanks ruirib, I changed the SQL and it works to, but.... I would still like to pull the total count of Tecra 8100's, or Compaq E500's without hard coding it in. How can this be done?
On the front page, you can click on an item in our inventory and a pop up window opens with all the information about this product, as in the pic below ...don't mind the smudges, had to protect some info.
As you can see in the top right corner the Make/Model is a Compaq E500, this info is pulled from the db.
Now if you look at the bottom right corner it says Qty in DB is 3, but that is wrong...since I hard coded Tecra 8100 in the code, it shows the total for Tecra's. Now, since there is only 1 E500 in the db, what would I have to do to the code above to count only the E500's, or which ever make or model that is clicked on that is in our inventory. The Make/Model of all the laptops are in the same column UnitEquipID in the Laptops table....
Thanks 
Intrepidone
Edited by - Intrepidone on 23 May 2002 06:44:35 |
|
|
|
Nikkol
Forum Moderator
USA
6907 Posts |
Posted - 23 May 2002 : 07:04:30
|
How does that pop up screen know to get the info for the Compaq (as opposed to the Tecca) when you click on the link on the main page? You say that info is pulled from the db. How are you pulling it from the db?
If your link on the main page were something like:
<a href="mypage.asp?model=Compaq%20E500">
then you would make your sql string:
"select Count(UnitEquipID) As eTotal from [Laptops] where UnitEquipID='" & Request.QueryString(model) & "'",myDsn ,3,3
Nikkol |
|
|
|
Intrepidone
Average Member
Canada
515 Posts |
Posted - 23 May 2002 : 15:46:47
|
Thanks for the info Nikkol
I did manage to accomplish what I wanted to do by changing the code to this
rsUnitEquipID.open "select * from [Laptops] where UnitEquipID='" & rs("UnitEquipID") & "'",myDsn ,3,3
I had tried this before but never put all the " 's in
Another thing I do want to do, but do not know if I can is this. For all of our equipment, we assign numbers to them, tag them and that number is also used as the computer name on the intranet. I would like to create another table with a column of #'s from 001 to 999. When we add a piece of kit to the db, it pulls a # from that table. If that number is already used in the ie: Laptops table, it pulls another # until it finds one not in the Laptops table, just so we do not have to search the db and see what was the last number used.
These are also "No Duplicates" so we do not generate a machine with the same name/#
Any heads up on how to accomplish this?? I'll sit down tonight and try see what I can figure out.
Intrepidone |
|
|
|
Nikkol
Forum Moderator
USA
6907 Posts |
Posted - 23 May 2002 : 18:05:04
|
You could accomplish that by using the AutoNumber field type. But it sounds like you have existing numbers already. Depending on how many existing numbers you have, you could add those first and then for every new item, it will just increment by one.
Nikkol |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 23 May 2002 : 19:10:08
|
If you don't want to use an AutoNumber, so that you do not get gaps between successive numbers, you could create maybe an Integer column named EquiPNumber and then get each successive number like this:
<%
set rsUnitEquipNumber=server.createobject("adodb.recordset")
rsUnitEquipNumber.open "select MAX(EquipNumber)+1 AS NextNum from [Laptops]",myDsn ,3,3
If Not IsNull(rsUnitEquipNUMBER("NextNum"))) Then '<--Testing for Null just because of the first record
EquipNumber = rsUnitEquipNUMBER("NextNum")
Else
EquipNumber = 1
End If
rsUnitEquipNumber.close
rsUnitEquipNumber= nothing
%>
You should also create a Unique index for this EquiNumber column in order to avoid repeated numbers.
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs |
|
|
| |
Topic |
|
|
|
Topic Locked
