Im trying to insert a customer via a form and then submit the session CustomerID on to the next page so I can get the info out from the database.

I've tried to use Georges extension but it doesn't work with dreamweaver MX i get a javascript error.

then I got ahold of this code

If (Not MM_abortEdit) Then
' execute the insert
Set MM_editCmd = Server.CreateObject("ADODB.Command"
MM_editCmd.ActiveConnection = MM_editConnection
MM_editCmd.CommandText = "SET NOCOUNT ON;" & MM_editQuery & ";SELECT @" & "@IDENTITY AS Ident"
Set rsLastIdent = MM_editCmd.Execute
if NOT rsLastIdent.EOF then
strLastIdent = rsLastIdent.Fields.Item("Ident".Value
' place the new record ID in a session variable called "CustomerID"
Session("CustomerID" = strLastIdent
end if
MM_editCmd.ActiveConnection.Close

but it doesn't work i get an error even after I use the right connection string.. the error is

Error Type:
Microsoft JET Database Engine (0x80040E14)
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
/InsertCustomer.asp, line 248

how could i get around this?

// Leon

I'm trying to do the same darn thing..and getting the same darn error...I posted you questoin at the macromedia user group as well to see if there is any resonse there...

I also posted the question using an alternative method which gave a different error in this forum...hopefully we can get this resolved..!!

I had the same problem, so I found this example www.udzone.com/ShowDetail.asp?NewsId=195&offset=10

Set MM_editCmd = Server.CreateObject("ADODB.Command"
MM_editCmd.ActiveConnection = MM_editConnection
MM_editCmd.CommandText = MM_editQuery
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close

I replaced the above bit of the code with the following and used the LastIdent session to filter the next page, which shows the last created record.

execute the insert
Set MM_editCmd = Server.CreateObject("ADODB.Command"
MM_editCmd.ActiveConnection = MM_editConnection
MM_editCmd.CommandText = MM_editQuery
MM_editCmd.Execute
MM_editCmd.CommandText = "SELECT @" & "@IDENTITY AS Ident"
Set rsLastIdent = MM_editCmd.Execute
if NOT rsLastIdent.EOF then
Session("LastIdent" = rsLastIdent.Fields.Item("Ident".Value
end if
MM_editCmd.ActiveConnection.Close



Hope that helps


Edited by - xlarge on 05 Sep 2002 17:35:06

I get this error now...

Microsoft VBScript runtime error '800a01b6'

Object doesn't support this property or method: 'rsLastIdent.Fields.Item(...).ValuestIdent'

/scug/Admin/home/insert.asp, line 147

I was able to do the insert with ID without having to have access this way:

<script language="javascript" runat="server">

function GetTimeInMillisec()
{
var Now = new Date()
var TimeStamp = Now.getTime()

return TimeStamp
}

</script>

LR_InsertCustomer=Request.ServerVariables("SCRIPT_NAME"
if Request("issubmitted" = "yes" then
CustomerRS.AddNew
CustomerRS("FirstName"=Request.Form("fmfname"
CustomerRS("LastName"=Request.Form("fmename"
CustomerRS("Phone"=Request.Form("fmtelephone"
CustomerRS("CustomerEmail"=Request.Form("fmemail"
CustomerRS("BillingAddress1"=Request.Form("fmaddress"
CustomerRS("BillingPostalCode"=Request.Form("fmpost"
CustomerRS("BillingCity"=Request.Form("fmcity"
CustomerRS("DeliveryAddress1"=Request.Form("fmladdress"
CustomerRS("ShipPostalCode"=Request.Form("fmlpost"
CustomerRS("ShipCity"=Request.Form("fmlcity"
CustomerRS("Ordernumber"=Request.Form("fmordernumber"
CustomerRS("Ereference"=Request.Form("fmereference"
TimeKey=GetTimeInMillisec()
CustomerRS("TimeKey"=TimeKey
CustomerRS.Update
CustomerRS.Requery
CustomerRS.Filter= "TimeKey='" & TimeKey & "'"
Session("CustomerID"=CustomerRS("CustomerID"
Response.Redirect("CheckOut.asp"

Its simple first you add a column in the database that you call TimeKey
This inserts a number that is the exact time when you inserted the record then you requery the database an filter out so you only get the timekey that matches the one that you inserted then you put that in a session... the code above is raw but I think you understand how it works.

Cheers

FYI... I used 'xlarge' code...but the reason it wasn't working is because the last command was repeated twice. Here is the code that worked for me...

I had the same problem, so I found this example www.udzone.com/ShowDetail.asp?NewsId=195&offset=10

Set MM_editCmd = Server.CreateObject("ADODB.Command"
MM_editCmd.ActiveConnection = MM_editConnection
MM_editCmd.CommandText = MM_editQuery
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close

I replaced the above bit of the code with the following and used the LastIdent session to filter the next page, which shows the last created record.


' execute the insert
Set MM_editCmd = Server.CreateObject("ADODB.Command"
MM_editCmd.ActiveConnection = MM_editConnection
MM_editCmd.CommandText = MM_editQuery
MM_editCmd.Execute
MM_editCmd.CommandText = "SELECT @" & "@IDENTITY AS Ident"
Set rsLastIdent = MM_editCmd.Execute
if NOT rsLastIdent.EOF then
Session("LastIdent" = rsLastIdent.Fields.Item("Ident".Value

Hope that helps