Semi-Colons in Conn.Execute Query 1

[MartinSmithhh]

I came across some pretty useful sample code to insert a new record and retrieve the identity but I am not sure how it works!

Dim new_identity
'Doing an insert
Query = "INSERT INTO ComDirMOTD " & _
    "(title, sender_text, message_body, Hyperlink, body_backcol,  " & _
    "ConfirmationValue, StartDate, EndDate, login_of_requestor) " & _
"VALUES ('" & MessageTitle & "','" & SenderName & "','" & MessageBody & "','" & Hyperlink & "','" & BodyColor & "','" & MOTDConfirm & "','" & StartDate & "','" & EndDate & "','" & LogInOfRequestor & "')"
Dim RS
Set RS = Server.CreateObject("ADODB.Recordset")
Set RS = Conn.Execute(Query & ";" & "select @@identity").NextRecordset
'Retrieve the motd_id for the newly inserted record
new_identity = RS(0)
Set RS = Nothing

I would be grateful if anyone has any info and or links which explain this further.

specifically
1. what is the significance of the '0' in RS(0)?
2. Is this more efficient than 2 separate queries?
3. Can this technique be used for anything else apart from retrieving identities?

Thanks,

Martin

 

[rac2]

Hello Martin,

rs(0) refers to the first item in the current record. In this case the recordset only has one record with only one item in it, the value of @@identity.

I think this would be more efficient than executing two commands, this involves a single exchange with the server. However, a more efficient method would be to create a stored procedure and call the stored procedure, passing the values to be inserted as parameters. The most time-consuming part of this is generating the actual database server code to carry out the SQL statements. This would be done one time with a stored procedure instead of everytime for the code you are looking at.

I would not use this "technique."

By the way, your post would be more suitable for the ASP forum.

Richard

 

[MartinSmithhh]

Thanks very much for your explanation and advice.