SQL query question

[colinod]

I am inserting a record into an access database but at the same time i want to know what the personal id number of the new record is at the same time? Is this possible??

[colinod]

i have found ou how to do this but i am having a problem retrieving the information.

I am sending the id of the information written to the database in a link via email with the following

<%
Response.Expires=0
Set mailConnection=Server.CreateObject("ADODB.Connection")
DatabaseDetails="DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.Mappath("../../private/database.mdb") & ";"
mailConnection.Open DatabaseDetails
mailQuery="INSERT INTO mailouttext (mailtext) VALUES ('"&textnew&"')"
set rs = mailConnection.execute("select MAX(ID) from mailouttext") 
Set mailRecordset=mailConnection.execute(mailQuery)
%>
<%
 
'Sends an email
 
Dim usrmail
Dim usrcart
Dim usrtext
usrcart = Request.Form("link") & "&id=" & rs(0)
Response.Write(usrcart)
Set usrmail=Server.CreateObject("CDO.Message")
usrmail.To=Request.Form("To")
usrmail.From="[e-mail]"
usrmail.Bcc=Request.Form("Bcc1")
usrmail.Subject=Request.Form("Subject")
usrmail.CreateMHTMLBody "http://www.website.co.uk/mailout/viewshortlist3.asp" & usrcart
usrmail.Send()
Response.Write("")
'Destroy the mail object!
Set usrmail = nothing
%>

the problem is that when the email is recieved the page it displays has the information that was typed in for the previous email??

[GazMathias]

Hi,

I know you've written a lot already, but you might want to look into the ADO Record Set object's AddNew method.

rs.AddNew ' Add a new row to the recordset (not the table)
rs("SomeField") = "SomeValue" ' Insert some data
' Add more fields
rs.Update  'Save your changes
rs.MoveLast 'Go to the record you just created
 
Response.Write rs("ID")  'This should let you grab the ID (assuming it is auto incremented by the DB)
 

Gaz

[colinod]

sorry Gaz i can see how to get this into my code at the moment??? not that good with ASP but am learning all the time i have tried the following

<%'Read in the value of the TEXTAREA
Dim textnew 
textnew = Request.Form("body2") 
'Replace all vbCrLf with <BR>s 
 
textnew = Replace(textnew, vbCrLf, "<BR>") 
 
'Replace all spaces with &nbsp; 
 
textnew = Replace(textnew, " ", "&nbsp;") 
%>
 
<%
Response.Expires=0
Set mailConnection=Server.CreateObject("ADODB.Connection")
DatabaseDetails="DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.Mappath("../../private/mailouts.mdb") & ";"
mailConnection.Open DatabaseDetails
mailQuery="SELECT * from mailouttext"
Set mailRecordset=mailConnection.execute(mailQuery)
mailRecordset.AddNew ' Add a new row to the recordset (not the table) 
mailRecordset("mailtext") = "textnew" ' Insert some data 
' Add more fields 
mailRecordset.Update  'Save your changes 
mailRecordset.MoveLast 'Go to the record you just created 
 
Response.Write mailRecordset("ID")  'This should let you grab the ID (assuming it is auto incremented by the DB) 
 
%>

but it comes back with the following

ADODB.Recordset error '800a0cb3'

Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.

/mailout/mailoutemail.asp, line 20

line 20 is the addnew line

[GazMathias]

Hi,

You need to provide a LockType, either optimistic (lock only when updating) or pessimistic (record by record).

The general form for this type of operation is explained pretty well here http://www.w3schools.com/ADO/prop_rs_locktype.asp, but there are loads of other examples out there. A simple "rs.update" search on Google yields plenty.

Gaz

[colinod]

thanks for your help, i have actually changed the way im going to do this and just encoded the url and the replaced the characters on the page that views the text