Replies Back to Article

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?

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.

There is a workaround

http://www.newmanzone.com/recipes/recipe.asp?LinkID=2&Link=index2.htm

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.

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)

How do I install this extension

on DW CS5.

Message that it needs ultradev.