How to refresh SQL database using ASP script?

[Bon]

Dear all

I have a javascript function which embeds ASP script for inserting data
into database. After the data is inserted database, I select the
maximum id (the latest inserted record id). But, it always select the
previous id. It seems the database hasn't been refreshed after insert a
new row.

my code is like

set objrs = server.createobject("ADO.recordset")
objrs.open "myTableName", objDBConnection
objrs.AddNew
objrs.Fields("ForeignID") = Request("foreignid")
objrs.Fields("FirstName") = Request("firstname")
objrs.Fields("LastName") = Request("lastname")
objrs.Fields("Phone") = Request("phone")
objrs.update
objrs.close
objrs.open "select max("PrimaryID") from tbl_myTable", objDBConnection

No matter I use objrs.refresh or objrs.requery instead of objrs.close.
The database is not refreshed. How can I refresh the database after I
add a new row in it? Please give me some suggestions.

Cheers
Bon

 

[Ray Costanzo [MVP]]

Scrap the whole way you're doing this and used a stored procedure that
returns SCOPE_IDENTITY. And have the stored procedure handle the insert
instead of creating a recordset object the way you're doing.

http://www.aspfaq.com/show.asp?id=2174

Ray at work

 

[Bon]

I created a stored procedure for inserting new data to database and
then select scope_identity. The returned ID result is correct when i
run the SP. But, ASP recordset return a wrong result. It always return
the previous ID. For example, the current ID is 100. After I call the
SP from ASP page, SP is run and the ID is 101. But, the ASP recordset
would return 100. Any suggestion?

My ASP code is embedded in Javascript:

<%
Dim objrs-dr
%>

function AddRow_clk()
{
'use xmlhttp to pass parameters
....
<%
Set objrs_dr = dbconnection.execute("Exec sp_stored_procedure_name
@param1='" & Request("Name") & "',@param2='" & Request("Address") &
"'")
session("ReturnedScopeID") = objrs_dr.Fields("SPReturnedID")
%>
alert ( <%=session("ReturnedScopeID")%> );
....
}

I use Javascript to test the returned result. It always returns 100
instead of 101. Could you give me some suggestions?

Cheers
Bon
Ray Costanzo [MVP] 寫é“：

 

[Mike Brind]

Try response.write-ing the new id to the page directly to see its
value. Your asp could be returning the correct value, but the js
function might not be doing what you think it is.