robn
Posts: 70
|
| Posted: 05/11/2006, 6:56 AM |
|
Hi all
I've used CCDlookup many time to set field values on Before show, but I come across an issue I can't seem to resolve using it.
Basically I want to retrieve the last data entry (projno) from the database for the current logged on user where the status is set to 2. I written a statement is SQL the appears to work fine
SELECT projno
FROM tblProject
WHERE clientc = session("userid") and [id] =
(select max([id]) from tblProject where Status = 2 AND clientc=session("userid"))
obvisiously replacing session("userid") with the clientc number.
But I can't seem to find a way to get this statement working with CCDLookup.
If anyone knows of a resolve or another way to retrieve this value and place it in a field your help would be greatly appreciated.
many thanks in advance
Rob
|
 |
 |
aegregory
Posts: 7
|
| Posted: 05/12/2006, 3:13 PM |
|
Add this to the custom code on the beforeshow event of your page or form.
Dim myconnection
SET myconnection = New clsDBConnection1
myconnection.open
Label.Value = CCDLookup("projno", "tblProject", "id=max(id) and status = 2 and clientc= " & CCGetUserId(), myconnection)
I tested out the following code in one of my applications and it works. My example populates a label with the most recent record id where the status = 1 and the record belongs to the current user.
Dim myconnection
SET myconnection = New clsDBDisputes
myconnection.open
Label1.Value=CCDLookup("max(dispute_id)", "dispute_data", "dispute_status_id = 1 and ae_id= " & CCGetUserLogin(), myconnection)
|
|
|
|
robn
Posts: 70
|
| Posted: 05/15/2006, 3:10 AM |
|
aegregory many thanks for you response it certainly pointed me in the right direction.
To get the code working I used your code but had to tweak the SQL slightly to get it to run. So the final working code in the before show looks like the following
Dim myconnection
SET myconnection = New clsDBConnection1
myconnection.open
tblProject .projno.Value = CCDLookup("projno", "tblProject", "[id]=(Select max([id]) from tblProject where status = 2 and clientc=" & session("userid")&")", myconnection)
This works a treat, many thanks for your help
Rob
|
|
|
|
marcwolf
Posts: 361
|
| Posted: 05/15/2006, 5:53 PM |
|
Hi Robn
With ccdlookup the table or where parts are optional
so
ccdlookup("fieldname","table","crit",connect )
is the same as
ccdlookup( "fieldname from table where crit","","",conn)
CCS automatically puts the select in for you and then checks that table name and where are present.
It also helps if you are doing specific database processes lile Encrypt and Soundex where all you need is the Select
ccdlookup("soundex('dave')","","",conn)
Hope this helps.
I often find its a good idea to look throught common.asp and classes.asp to find out what is already provided for use :>, and also how it works
Take care
Dave
_________________
' Coding Coding Coding
Keep Those Keyboards Coding.
Raw Code!!!!!!!
|
|
|
|
|
