zimmer9
asked on
How to assign a value to a recordset field while creating a new record by using the SQL Max function?
I am developing an ASP application. In the following code, do you know how I would go about assigning a value to the reqID field. I want to use the SQL "MAX" function to find the highest reqID and add 1 to this value. And then assign this calculated value to the field "reqID". So if the highest reqID is 1000, I would assign 1001 to the reqID when creating a new record. Do you know how I could fix my code to assign the proper value to rst("reqid")?
<%
dim conn
dim ssql, rst, rstAppDetail, rst1
dim fname,id, appid, username, userEml, stmp, reqID1
id = session("id")
if id="" then
Response.Redirect "default.asp?msg=4"
Response.End
else
userEml=session("eml")
username=session("lname")
fname=session("fname")
appid=request("appid")
%>
<!--#include file="dbstringFSNet.asp"-- >
<HTML>
<HEAD>
<TITLE>SOX - new request </TITLE>
<link rel="stylesheet" href="onelook.new.css">
</HEAD>
<BODY leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">
<!--#include file="topusr.asp"-->
<table border=0 cellpadding=5 cellspacing=5>
<%
if usereml="" then
usereml="olga.skobelsky@mo rganstanle y.com"
end if
set rst1=server.CreateObject(" adodb.reco rdset")
ssql="SELECT MAX(reqID) +1 from FSSOX.dbo.tSOXUsrRequest"
rst1.Open ssql, conn, 2,2
reqID1 = rst1("reqID")
set rstappdetail=server.Create Object("ad odb.record set")
ssql="SELECT tAllApplications.*, tEmployees.ldapID AS devid, tEmployees.Manager AS mngrL, tEmployees.Managerfirst AS mngrF, tEmployees.MgrID1 AS mngrid1, tEmployees.Manager1 AS mngrL1, tEmployees_1.Email AS mngrEml, tEmployees_2.Email AS mngr1Eml, tEmployees.Email as devEml FROM tEmployees tEmployees_1 RIGHT OUTER JOIN tEmployees ON tEmployees_1.[Last Name] = tEmployees.Manager LEFT OUTER JOIN tEmployees tEmployees_2 ON tEmployees.MgrID1 = tEmployees_2.EEID RIGHT OUTER JOIN tAllApplications ON tEmployees.NTid = tAllApplications.FSSupport ID where appid='" & appid & "'"
rstAppDetail.Open ssql, conn, 2,2
set rst=server.CreateObject("a dodb.recor dset")
ssql="SELECT * from tSOXUsrRequest"
'ssql="select * from tallapplications where appid=" & appid
rst.Open ssql, conn,2,2
'response.Write appid & "<BR>" & request.Form("reason") & "<BR>" & request.Form("dt") & "<BR>" & request.Form("dtneeded") & "<BR>" & id & "<BR>" & request.Form("req") & "<BR>"
rst.AddNew
rst("reqid") =reqID1 <-------------- this doesn't work
rst("appid") =appid
rst("appname")=rstAppDetai l("applica tion")
rst("userid")=id
rst("username")=username
rst("usernameF")=fname
rst("usereml")=usereml
rst("changerequest")=reque st.Form("r eq")
rst("dtrequested")=request .Form("dt" )
rst("dtneeded")=request.Fo rm("dtneed ed")
rst("reason")=request.Form ("reason")
rst("dev")=rstAppDetail("f ssupportid ")
rst("devid")=rstAppDetail( "devid")
rst("deveml")=rstAppDetail ("deveml")
rst("mngr")=rstAppDetail(" mngrl")
rst("mngreml")=rstAppDetai l("mngrEml ")
rst("mngr1")=rstAppDetail( "mngrl1")
rst("mngr1Eml")=rstAppDeta il("mngr1E ml")
rst.Update
<%
dim conn
dim ssql, rst, rstAppDetail, rst1
dim fname,id, appid, username, userEml, stmp, reqID1
id = session("id")
if id="" then
Response.Redirect "default.asp?msg=4"
Response.End
else
userEml=session("eml")
username=session("lname")
fname=session("fname")
appid=request("appid")
%>
<!--#include file="dbstringFSNet.asp"--
<HTML>
<HEAD>
<TITLE>SOX - new request </TITLE>
<link rel="stylesheet" href="onelook.new.css">
</HEAD>
<BODY leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">
<!--#include file="topusr.asp"-->
<table border=0 cellpadding=5 cellspacing=5>
<%
if usereml="" then
usereml="olga.skobelsky@mo
end if
set rst1=server.CreateObject("
ssql="SELECT MAX(reqID) +1 from FSSOX.dbo.tSOXUsrRequest"
rst1.Open ssql, conn, 2,2
reqID1 = rst1("reqID")
set rstappdetail=server.Create
ssql="SELECT tAllApplications.*, tEmployees.ldapID AS devid, tEmployees.Manager AS mngrL, tEmployees.Managerfirst AS mngrF, tEmployees.MgrID1 AS mngrid1, tEmployees.Manager1 AS mngrL1, tEmployees_1.Email AS mngrEml, tEmployees_2.Email AS mngr1Eml, tEmployees.Email as devEml FROM tEmployees tEmployees_1 RIGHT OUTER JOIN tEmployees ON tEmployees_1.[Last Name] = tEmployees.Manager LEFT OUTER JOIN tEmployees tEmployees_2 ON tEmployees.MgrID1 = tEmployees_2.EEID RIGHT OUTER JOIN tAllApplications ON tEmployees.NTid = tAllApplications.FSSupport
rstAppDetail.Open ssql, conn, 2,2
set rst=server.CreateObject("a
ssql="SELECT * from tSOXUsrRequest"
'ssql="select * from tallapplications where appid=" & appid
rst.Open ssql, conn,2,2
'response.Write appid & "<BR>" & request.Form("reason") & "<BR>" & request.Form("dt") & "<BR>" & request.Form("dtneeded") & "<BR>" & id & "<BR>" & request.Form("req") & "<BR>"
rst.AddNew
rst("reqid") =reqID1 <-------------- this doesn't work
rst("appid") =appid
rst("appname")=rstAppDetai
rst("userid")=id
rst("username")=username
rst("usernameF")=fname
rst("usereml")=usereml
rst("changerequest")=reque
rst("dtrequested")=request
rst("dtneeded")=request.Fo
rst("reason")=request.Form
rst("dev")=rstAppDetail("f
rst("devid")=rstAppDetail(
rst("deveml")=rstAppDetail
rst("mngr")=rstAppDetail("
rst("mngreml")=rstAppDetai
rst("mngr1")=rstAppDetail(
rst("mngr1Eml")=rstAppDeta
rst.Update
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
are you getting an error or is the value not saving correctly?
ASKER
Everything is working fine now. Thanks for your assistance.
ASKER