Rene
|
| Posted: 06/19/2002, 4:02 PM |
|
Newbie question...I have an input form which has a key field called Rfq_num. When a user inputs a new record (insert mode?) I'd like the form to automatically insert a default value which is calculated as the highest rfq_num + 1. The data entered into this form gets posted to a SQL Server table. I suspect that the answer has something to do with entering a query to the effect of "SELECT Max(rfq_num) + 1" but I'm not sure where I would put this. Would this be custom code or could I somehow enter it into "Default" for the text box properties. Could somebody point me in the right direction?
|
|
|
 |
Ken Hardwick
|
| Posted: 06/20/2002, 2:22 AM |
|
I used the following with ASP/Oracle...you may need to modify for SQL Server...
Function Next_ID(F,T,W)
'F= Field
'T = Table
'W = criteria if any
kenSQL = "Select max(" & F & ")+1 as next_One from " & T
if W <> "" then
kenSQL = kenSQL & " where " & W
end if
openrs rs2, kenSQL
if rs2.eof = true then
Next_id = 1
else
Next_Id = rs2("Next_one")
end if
rs2.close
set rs2 = nothing
End Function
and then call it in the before insert event..
fldID = Next_ID("ID,"Table","")
|
|
|
|
Rene
|
| Posted: 06/20/2002, 7:04 AM |
|
Thanks for the input Ken! I was also able to get it to work using this code in a Before Show event:
if form.field.Value = 0 then
form.field.Value = ccdlookup("max(field) + 1","table","field<> 0",DBConnection1)
end if
Probably not the most elegant code in the world but it works and for a non-programmer that's major feat!
|
|
|
|
ystyves
|
| Posted: 06/20/2002, 11:29 AM |
|
Why dont you use the seed increment functionality of SQL server?
its way easier
|
|
|
|
James
|
| Posted: 04/09/2003, 7:44 AM |
|
I tried this CCDLookup but can't get it to work. Can you tell me what is wrong?
Any help would be appreciated.
Thanks
global $ship_rate;
global $DBWS103test;
$ship_rate->seq->SetValue(CCDLookup("MAX(seq)+1","ship_rate","division=".
$ship_rate->division->GetValue()."' AND abbr=".
$ship_rate->abbr->GetValue()."' AND country_cd=".
$ship_rate->country_cd->GetValue()."' AND ship_cd=".
$ship_rate->ship_cd->GetValue() ,$DBWS103test));
|
|
|
|
|
