Replies Back to Article
Insert Record With Identity ASP
So far not so good - Usual hassles you get with the insert behaviour (error 503 don't try this request at home), but this time there is an error in the SQL at this point:
MM_editCmd.CommandText = "SET NOCOUNT ON;" & MM_editQuery & ";SELECT@" & "@IDENTITY AS Ident"
Set rsLastIdent = MM_editCmd.Execute
Got to admit I don't know what is happening - the insert statement I can follow the rest passes me by.
I'm using MySQL- the latest(ish) release does this explain it?
I am assuming that I can pass the id to another page and use this in data operations - for instance - I insert a record in a master table, say ORDER, then I use the key, say OrderID (auto-increment integer) when I come to insert a record in a detail table, say ORDER_LINE - The PK for order line consists of OrderID & LineID (auto-increment) together -
a) how do I pass the id or ORDER, and
b) will it return OrderID, LineID following the insert in ORDER_LINE?
There should be the same construction for MySQL, but I couldn't found - maybe you can help me with this. Then I will build it into the extension.
Well you have a variable LastIdent, it is set in the script. You just have to switch off the redirect and put your own code that uses this variable just after the the insert script.
Maybe it will be better if I make a Session variable from this one in the next version of this extension ...
mr. petrov,
i know this is not on your extension i was just hoping you could help. i was wondering how could i modify the insert record extension so that when a user didn't put anything on 1 field it would display an error message stating that the specific field has no entry?
thanks.
sandy
Ok, if I have filled out the behaviour variables, and leave blank, "after inserting go to," (not put in a redirect page per online instructions), then how do I get to the next page so I can pull down that variable?
Also, does the form need to be submitted to itself? And what does the cursor type need to be?
And how can I find out more info on session variables? Thanks.
After inserting this server behaviour, filling out all the fields (correctly?) I get the following message:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
/beta/TMPc79d296c76.asp, line 91
Help!
There is a problem giving parameters to the "after inserting go to" field. You can specify a file name, but you can't send parameters to it. Use session variables to send parameters from one page to other.
Pd: Cool tool for reply ... congratulations for it ... ;)
I have noticed several people have been receiving this error as well as myself. I'm using an Access 2000 DB & I've tried it on SQL 7.0.
I receive this error on BOTH types of DBs:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
Any resolution to this so far?
Can you send me the SQL statement that gets generated? To get it add after the line:
MM_editCmd.CommandText = "...."
Add:
Response.Write MM_editCmd.CommandText
Response.End
This will output the SQL and end processing. Execute the page, copy the SQL and send it to me (via private mail please), then remove the lines from your code.
Also the problem is only with Access.
SQL Server does accept the SQL statement - whole UDzone is full with this stuff.
Identity works with Access 2000 ONLY if you use the "Jet OLEDB Provider 4.0"!
So define your ODBC connection not with "Microsoft Access Driver" but with "Jet OLEDB Provider 4.0". So your connection string should look like:
"Provider=Microsoft.Jet.OLEDB.4.0;Data source="C":\Backup\OBS\Development\Db\obs2k.mdb;Persist Security info="False""
If you use a DSN-Less connection. Otherwise its in the ODBC Settings.
Read more about this in the following ASPWATCH article
I get this error from ultradev when I try to apply the behavior:
____________________________________
Server Behavior was not applied. Could not find a valid XML file that refers to the current server behavior and the current server model.
____________________________________
I'm assuming that I didn't install the behavior correctly? All I did was double-click the download, and it seem to go through properly.
The Jet 4.0 driver was unacceptable on our project so I updated it to make it work regardless of the driver.
Here is the original piece of code:
--------
' 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
LastIdent = rsLastIdent.Fields.Item("Ident").Value
end if
MM_editCmd.ActiveConnection.Close
--------
And here is the new piece
--------
' 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
LastIdent = rsLastIdent.Fields.Item("Ident").Value
end if
MM_editCmd.ActiveConnection.Close
-------
YES!!! That works really great. Thank you. But one thing, 'cause I see some people asking about this. The session variable is not set. Go to this line:
if NOT rsLastIdent.EOF then
LastIdent = rsLastIdent.Fields.Item("Ident").Value
end if
And change:
LastIdent = rsLastIdent.Fields.Item("Ident").Value
to
Session("LastIdent") = rsLastIdent.Fields.Item("Ident").Value
Thanks again!!!
I receive the following error (below) when I try applying the insert record with identity behavior. I'm running SQL 2000 and UD4 using Javascript as my server language. Any ideas?
--"server behavior was not applied. Could not find a valid XML file that
refers to the current server behavior and the current server model."--
Can somebody please translate me the code into JavaScript. Thanks!
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
Hi All,
Many Users has ask me to this little Code Snippet for George Petrov's Extension. This work with MySQL in DSN or DSN less Connection.
..................................................................................
If (Not MM_abortEdit) Then
' 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 LAST_INSERT_ID() 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
...................................................................................
Good Luck !
Best Regards from Germany
Jörg
ultradevextensions.de
Thanks! I was looking for this one! Now I need one for Oracle too.
Also maybe a way how to detect what database is being used now ...
Any chance of updating the extension so by default it does not requite jet4.0?
This extension is just what I'm looking for (I think) but I could do with a bit of help if anyone has time. First a couple of questions
1. The description of this behaviour says that it is a "Modification" of Insert Record Server Behaviour. Does this mean that it has overwritten the original Behaviour?
2. I'm using this behaviour on a ecommerce site where the users dont register. I'm planning on generating a "CustomerID" session variable first. I dont understand how this Server Behaviour will let me do this though
Rich
What is causing the following error?
Server behavior was not applied. Could not find a valid XML file that
refers to the current server behavior and the current server model.
I've noted that others have received this error as well but I am unable to find any responses from Tech Support, or others at UDZone, as to what the cause may be and how to work around it.
I tried to use this extension in Dreamweaverr MX, but it
can't be used.. I got the following error message:
Javascript error while loading InsertRecordWithIdent ..
Unable to open scriptfile "/EditOperations.js"[error2]
Gert-Jan
Yes as you can see from the properties info - there is no MX support yet.
I hope to make it very soon.
Hi George
When will you add MX support for this extension?
/Magnus
George,
Do you have any plans to update this extenion so it will be MX compatible?
Or got anyone send me the code, so i can make it work myself, like a snippet??
Thanx in advance
Matthijs
Open the "InsertRecordWithIdent.htm" file for editing. (Use the Windows "search" tool to find the actual location of the file. Make sure to edit the file that is in the folder for the server scripting you are using.)
Change the line referencing the "EditOperations.js" file location (should be line 12 in the code) to <SCRIPT SCR="../../ServerBehaviors/Shared/EditOperations.js"></SCRIPT>.
This should correct the file reference issue.
how can I get the Ident value?
When I use this extention to insert record. How can I get the ident value in the next page?
Thank you for your help.
/Vincent
Is this extension compatible with PureAspUpload ? I've tried with some other extension s to retrieve the autonumber but no chance.
Best regards
Domi
George are you going to make this behavior work with DWMX? I used to use it all the time with UltraDev 4 but it keeps throwing errors in MX.
Thanks.
After installing the new extension and then restartin MX, it failed to open. Furthermore I received a Java2 error?
Check out the FAQ's link above. There is a fix in there for your problem.
What is causing the following error?
Server behavior was not applied. Could not find a valid XML file that
refers to the current server behavior and the current server model.
I've noted that others have received this error as well but I am unable to find any responses from Tech Support, or others at UDZone, as to what the cause may be and how to work around it.
Hi George,
Quick Question, when i add the retrieve ident on insert then add the make the ident into a session variable, is described in FAQ for the extension it always makes my dreamweaver lock up. If I add the Session code in notepad and it will work great, but I cannot open that page up in Dreamweaver ever again.
Any idea. Basically I just need the unique Id so that I can make a command on the next page.
Jim Hanifen
This really bites ***. I tried using this extension as it is exactly what I need, but when I tried it came back with the dreaded Javascript error. I followed the instructions that someone had wrote about the problem, still having problems.
Now I need to know how to let a user register ( insert a record ) and then be provided with there details on another page where they can update any settings, apart from there username.
I know how to use the MM_Username session when the user log's in but when I try to create a session for when the user register's It doesn't work due to a recordset not being attached to it.
Any one...
Thank You In Advance...
Gavin Roberts
Evenyoucando.co.uk
Hi,
when I launch this Behavior, dreamweaver write this error:
---------------------------
Macromedia Dreamweaver MX 2004
---------------------------
JavaScript error while loading InsertRecordWithIdent.htm:
Unable to open script file "C:\Programmi\Macromedia\Dreamweaver MX 2004\Configuration\ServerBehaviors\EditOperations.js" (error 2).
---------------------------
OK
---------------------------
Why? Help me if is possible...
Simone
I am getting the following error message:
Microsoft JET Database Engineerror '80040e14'
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
/admin/addprogram.asp, line 90
Any ideas as to the problem.
Thanks
When inserting to a SQL tables where insert trigger(s) exist, the value returned by @@identity (MSSQL) will contain the most recently inserted table identity value. If you need a specific table identity value, such as when your trigger inserts to multiple tables, use something like the following:
SELECT ident_current('InsertTableNameHere') AS Ident
Sorry but this is a poorly written extension. Not only did I initially get the Javascript error (which I fixed after copying and pasting a shared file to the right folder) but I then got the SQL error which I couldn't fix at all.
This extension needs an update real bad so it can work properly in MX and the latest versions of Windows server technology. Right now, it's basically useless.
Hi, I already used this extension on my old computer using Win2K_PRO + DW_MX, but now, I'm trying to use on my new computer using WinXP_PRO + DW_MX, and it-s failing. DW shows the following msg:
JavaScript error while loading IRWI: Unable to open script file "../EditOperations.js" (error 2)
What do I have to do? Thank you.
PS: DMXZone could lower the price of the extensions to brazilians...US$ is too high to us...
As facilidades que esta ferramenta pode dar ao administrados de DB são claramente visiveis, pois aumenta o controle sobre a ação direta de cada inserção em cada tela de navegação, diminui o trabalho de multiplas consultas ao DB para verificar se o registro foi realmente inserido e qual o seu valor de chave, mas precisa ser extendido para suportar MySql ou tera seu uso muito limitado
Hi,
I have tried this code snippet, but now have server errors...
I simply replaced the code from George's insert with the code below and I get nothing now :-(
http://www.sitetester.co.uk/mngt/idinsert.asp
Anyone got any clue as to how to get this thing working - its a major stumbling block for me!
Thanks,
Chris.
OOPS!
Got it working now - brillaint code - thanks!
To retreive the ID of a new record, use the Insert Record With Identity extension then locate the following lines of code:
' 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
LastIdent = rsLastIdent.Fields.Item("Ident").Value
end if
MM_editCmd.ActiveConnection.Close
and replace them with:
' 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
This code is NOT dependant on driver (as the original code works on MS SQL Server/Access 2000 with "Jet OLEDB Provider 4.0" only)