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")

%>


<HTML>
 <HEAD>
 <TITLE>SOX - new request </TITLE>
 <link rel="stylesheet" href="onelook.new.css">
 </HEAD>
 <BODY leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">


<table border=0 cellpadding=5 cellspacing=5>
<%

 if usereml="" then
 usereml="olga.skobelsky@morganstanley.com"
 end if


 set rst1=server.CreateObject("adodb.recordset")
 ssql="SELECT MAX(reqID) +1 from FSSOX.dbo.tSOXUsrRequest"
rst1.Open ssql, conn, 2,2
reqID1 = rst1("reqID")


 set rstappdetail=server.CreateObject("adodb.recordset")
 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.FSSupportID where appid='" & appid & "'"
 rstAppDetail.Open ssql, conn, 2,2

 set rst=server.CreateObject("adodb.recordset")
 ssql="SELECT * from tSOXUsrRequest"
 'ssql="select * from tallapplications where appid=" & appid
 rst.Open ssql, conn,2,2
 'response.Write appid & " " & request.Form("reason") & " " & request.Form("dt") & " " & request.Form("dtneeded") & " " & id & " " & request.Form("req") & " "

 rst.AddNew
 rst("reqid") =reqID1 <-------------- this doesn't work
 rst("appid") =appid
 rst("appname")=rstAppDetail("application")
 rst("userid")=id
 rst("username")=username
 rst("usernameF")=fname
 rst("usereml")=usereml
 rst("changerequest")=request.Form("req")
 rst("dtrequested")=request.Form("dt")
 rst("dtneeded")=request.Form("dtneeded")
 rst("reason")=request.Form("reason")
 rst("dev")=rstAppDetail("fssupportid")
 rst("devid")=rstAppDetail("devid")
 rst("deveml")=rstAppDetail("deveml")
 rst("mngr")=rstAppDetail("mngrl")
 rst("mngreml")=rstAppDetail("mngrEml")
 rst("mngr1")=rstAppDetail("mngrl1")
 rst("mngr1Eml")=rstAppDetail("mngr1Eml")
 rst.Update

ASKER CERTIFIED SOLUTION

Big Monty

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

zimmer9

ASKER

I will take your suggestion but how would you answer the question I posed if you couldn't use aut-increment?

Big Monty

are you getting an error or is the value not saving correctly?

zimmer9

ASKER

Everything is working fine now. Thanks for your assistance.