|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
March 23rd, 2000, 08:51 AM
|
|||
|
|||
|
Max SQL
<i><b>Originally posted by : Paul (pwill22@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> <br />
|
|
#2
March 23rd, 2000, 10:49 AM
|
|||
|
|||
|
<i><b>Originally posted by : steve</b></i><br />http://www.aspfree.com/asp/startpage.asp?id=20 is the example i got the code from.<br /><br /><br />------------<br />Paul at 3/23/2000 6:51:39 AM<br /><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> <br />
|
|
| Viewing: ASP Free Forums > Programming > ASP Development > Max SQL |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
