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


<!--#include file="dbstringFSNet.asp"-->

            <TITLE>SOX - new request </TITLE>
            <link rel="stylesheet" href="">
      <BODY leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">
<!--#include file="topusr.asp"-->

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

      if usereml="" then
      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("reqid") =reqID1         <-------------- this doesn't work
      rst("appid") =appid
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