troubleshooting Question

How to assign a value to a recordset field while creating a new record by using the SQL Max function?

Avatar of zimmer9
zimmer9Flag for United States of America asked on
ASP
4 Comments1 Solution381 ViewsLast Modified:
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@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 & "<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")=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
Web Ninja at large

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros