How to force execution of SQL?

[rastkocvetkovic]

SHORT DESCRIPTION:

new_news.asp

1. INSERT new news (id generated by autonumber)
2. RETRIEVE ID from the new news (select max (id) from news)
3. UPDATE new news with it's own ID

PROBLEM: the retrieved id is not from currently inserted news, but from one's already stored in database.

SOLUTION: how could I FORCE INSERT to perform it's task before retrieval?

--------------------------------------------------------

LONG DESCRIPTION:

I'm inserting a new news on the same page and then retrieve it's ID to update one of it's fields with the same ID (that is necessary indeed because of the programs structure). No, the problem is that the retrieval of the new ID always retrieves the previous MAX id, not from the currently inserted news, although the insert is executed before the retrieval of the new ID. Is there any "Flush" functions that would make that done?

--------------------------------------------------------

Please help ASAP! Thanks in advance.

 

[AWithers]

You dont say what db you are using if it is sql server then there is a function @@identity which retruns the id of the latest inserted row. If you assign this to a variable and then use in your select statement you should get what you want

see example below extracted from books on line

Sorry if this is no good againt your db

Andy

INSERT INTO jobs (job_desc,min_lvl,max_lvl)
VALUES ('Accountant',12,125)
SELECT @@IDENTITY AS 'Identity'

 

[rastkocvetkovic]

Hi!

Yes, my mistake, i forgot to say which DB system I'm using. That's MS Access using ADO. The procedure goes like this:

set newConn = Server.CreateObject("ADODB.Command&quot
newConn.ActiveConnection = "dsn=news_database;"

SQL = "INSERT INTO News (date, news) VALUES ('" & date & "', '" & content & "')"
newConn.CommandText = SQL
newConn.Execute()

Dim lastNews
Set lastNews = Server.CreateObject("ADODB.Recordset&quot
lastNews.ActiveConnection = "dsn=news_database;"
lastNews.Source = "SELECT id FROM News WHERE id = (SELECT MAX(id) FROM News)"
lastNews.Open()
Dim lastID
lastID = lastNews.Fields.Item("id&quot.Value
lastNews.Close()
Set lastNews = Nothing

----------------------------

retrieved lastID isn't really the lastID I wanted

But I'll try the method you suggested. I hope it'll help. I'll let you know.

 

[Geee]

Im not sure but I think your SQL query wants to be...

lastNews.Source = "SELECT Max(id) FROM News"

I use this method frequently with ADO/Access and it works for me. Post if you still have problems. -GTM Consult, Home of USITE-
-=

http://www.gtmconsult.co.uk=-

 

[codestorm]

i recently read on another thread about an 'undocumented' (stupid) feature of access - it caches tables for about 5 seconds before refreshing to display updates.

Not sure if this problem applies using ADO thru ASP, but it seems to throw things out of whack using ADO and VB apps.

The solution posted involved (from memory) using the JRO.JetEngine object - more specifically the RefreshCache method. Haven't needed to try it myself tho .. codestorm
Fire bad. Tree pretty. - Buffy
select * from population where talent > 'average'
You're not a complete programmer unless you know how to guess.
I hope I never consider myself an 'expert'.
<insert witticism here>

 

[Tarwn]

There is also a flag you can apply to your execute statement that occasionally solves this problem, basically it is only to inform the object that no records are expected back, and in my mind it shouldn't actually affect the outcome, but in some cases does.

newConn.Execute,,adExecuteNoRecords

if the enum doesn't work use the numeric value 128.
-Tarwn ------------ My Little Dictionary ---------
Reverse Engineering - The expensive solution to not paying for proper documentation

 

[rastkocvetkovic]

Tarwn, newConn.Execute,,adExecuteNoRecords actually solves the problem. Everyone who has similar problem, use it! Thanks again!

Codestorm, I've read about that also, but no-one knew the solution - and it's said that it applies to ODBC and ADO.

Geee, could you please tell me how to retrieve the id value after that query, because I've been trying and I haven't found a solution yet. (Because I would need that for COUNT(id) and MIN(id) too). Thanks in advance!

 

[rastkocvetkovic]

Delaying... grr

I've filtered out some code that was in the middle of INSERT | > code < | UPDATE, and I found out that I'm getting the same problem again. The > code < could be somekind of DELAY function. Therefore I ask, is there a delay function in ASP, so that the INSERT would have enough time to perform it's request? Thanks for answers.

 

[codestorm]

following my post above, if you can change the (from memory) PageTimeout registry key (i _think_ in HKEY_LOCAL_MACHINE - search for PageTimeout in the registry in a Jet 'area') it might reduce the cache update time.. codestorm
Fire bad. Tree pretty. - Buffy
select * from population where talent > 'average'
You're not a complete programmer unless you know how to guess.
I hope I never consider myself an 'expert'.
<insert witticism here>