March 23rd, 2000, 08:46 AM
|
|
Contributing User
|
|
Join Date: Dec 2002
Posts: 14,578
Time spent in forums: < 1 sec
Reputation Power: 22
|
|
|
MAX SQL
<i><b>Originally posted by : Paul Wilson (pwil22@hotmail.com)</b></i><br />I am trying to use a MAX SQL method of returning an access <br />autonumber (instead of timestamping - thanks Mike anyway)<br />My page updates my database but does not print the ID#. <br />Can anyone see where I have gone wrong.<br /><br />Thanks <br /><br />Paul<br /><br />P.S. how can i forward this ID# as a variable to another .asp page<br /> to use as a FK in a different table.<br /><br />Heres my attempt...<br /><br /><title></title> <br /></head> <br /><br /><body> <br /><% <br />'Declare variables needed <br />Dim strInsert <br />Dim strValues <br />Dim CmdText <br />Dim blnCriticalError <br />Dim maxstrsql <br />Dim thisdelegateid <br />Dim objrs <br /><br /><br />'Set required variables <br />adCmdText = 1 <br /><br />If Request.Form("Action") = "Add" Then <br /><br />'Start building the sql statement <br />strInsert = "Insert into Delegate (Title, [Family Name], [First Names], " & <br />_ <br />"Address1, [Post Code]" <br /><br />strValues = "Values('" & Cstr(Request.Form("txtTitle")) & _ <br />"','" & Cstr(Request.Form("txtFamily")) & _ <br />"','" & Cstr(Request.Form("txtFirst")) & _ <br />"','" & Cstr(Request.Form("txtAddress1")) & _ <br />"','" & Cstr(Request.Form("txtPostCode")) & "'" <br /><br /><br />'Add Position if present <br />If Len(Request.Form("txtPosition")) >0 Then <br />strInsert = strInsert & ",Position" <br />strValues = strValues & ",'" & _ <br />Cstr(Request.Form("txtPosition")) & "'" <br />End If <br /><br />'Add Hospital if present <br />If Len(Request.Form("txtHospital")) >0 Then <br />strInsert = strInsert & ",Hospital" <br />strValues = strValues & ",'" & _ <br />Cstr(Request.Form("txtHospital")) & "'" <br />End If <br /><br />'Add Address2 if present <br />If Len(Request.Form("txtAddress2")) >0 Then <br />strInsert = strInsert & ",Address2" <br />strValues = strValues & ",'" & _ <br />Cstr(Request.Form("txtAddress2")) & "'" <br />End If <br /><br />'Add Position if present <br />If Len(Request.Form("txtTel")) >0 Then <br />strInsert = strInsert & ",Telephone" <br />strValues = strValues & ",'" & _ <br />Cstr(Request.Form("txtTel")) & "'" <br />End If <br /><br />'Add Position if present <br />If Len(Request.Form("txtFax")) >0 Then <br />strInsert = strInsert & ",Fax" <br />strValues = strValues & ",'" & _ <br />Cstr(Request.Form("txtFax")) & "'" <br />End If <br /><br />'Add Position if present <br />If Len(Request.Form("txtEmail")) >0 Then <br />strInsert = strInsert & ",[E-mail]" <br />strValues = strValues & ",'" & _ <br />Cstr(Request.Form("txtEmail")) & "'" <br />End If <br /><br /><br />'Add Home Addreess if checked <br />If Request.Form("chkHome") = 1 Then <br />'Add the column name <br />strInsert = strInsert & ",[Home Address]" <br />'Add True to the valu string <br />strValues = strValues & ",True" <br />End If <br /><br /><br /><br />'Create and open the database object <br />Set objConn = Server.CreateObject("ADODB.Connection") <br />objConn.Open "DSN=CasilTest" <br /><br />'Create the command object <br />Set objCmd = Server.CreateObject("ADODB.Command") <br /><br />Set objrs = Server.CreateObject("ADODB.Recordset") <br /><br />'SQL to get the maximum delegate id <br />maxstrrsql = "select Max([Delegate ID]) from Delegate" <br /><br /><br /><br />'Set the command object properties <br />Set objCmd.ActiveConnection = objConn <br />objCmd.CommandText = strInsert & ") " & strValues & ")" <br />objCmd.CommandType = adCmdText <br /><br />'Execute the command <br />objCmd.Execute <br /><br />objrs.open maxstrsql, con <br /><br />'set as a variable <br />set thisdelegateid = objrs(0) <br />response.write "Please make note that your Delegate ID is " & <br />(thisdelegateid) <br /><br />End If <br /><br />'Close and dereference database objects <br />Set objCmd = Nothing <br />objConn.Close <br />Set objConn = Nothing <br />%> <br /></body> <br /></html>
|
ASP Free Forums Sponsor:
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
