Jump to content

Querying an updated recordset from ASP to MS Access


Ceekaye
 Share

Recommended Posts

Hello,I am developing a web application using ASP, ADO, JScript, and MS Access.I am having difficulties with viewing a recently updated record in its 'new' status.I am not certain if the discrepancy lies within my SQL query, or in my coded ASP pages.Specifics: My Transactions Table has 7 fields: TransactionID [autonum], TransactionTypeID [num], UserID [num], JobNo [number], StageDate [date/time], Notes [test], Sequence [number].The purpose of the table is to provide a transaction history for each Job number stored - its current status, date, notes from user, and the sequence number is incremented through a function which lives in an included page.Example of issue: Let's say job number 1100 was in status of 'billing' and now its status has changed to 'closed'. First the sql selects correct record, then a status edit page appears; user forms on the page collect new 'status' information, and is submitted to the editGO page which executes the ADO update command.Checking manually in the AccessDB, I find that the new record was successfully added to the Transactions table.BUTWhen I return to my status info pages, and look for things in the status of 'billing', my recently changed job is still being reported in 'billing' status. When searching for things in the status of 'closed', my job 1100 is listed there as well.If someone may have some input for me? I realize there may be more detailed info needed in order for one of you smart folks to come up with suggestions for me...so i will wait to hear from someone.Many thanks before-hand for your time and assistance!Sincerely,Christina~

Link to comment
Share on other sites

hit Refresh...it sounds like the browser is caching your pages.look into some no-cache techniques.
Well, i tried logging out, closing the browser and logging back in - no residual session variables, etc...the result is still the same. Tried from a different computer even...same result.Could you maybe elaborate on your thoughts about browser chaching [in brief, how does that work]? In the meantime i will look into some no-cache techniques.Thanks! :)
Link to comment
Share on other sites

well when I used to use ASP sometimes when a record was updated and I did a response.redirect back to a viewing page the data would not be updated (it was in DB but not showing on screen), in my case a simple Refresh showed the new values. Obviously that is not the case for you.You said that you checked the database and the data is indeed updated?Can you post the code that displays the data fromt he DB?

Link to comment
Share on other sites

Checking manually in the AccessDB, I find that the new record was successfully added to the Transactions table.BUTWhen I return to my status info pages, and look for things in the status of 'billing', my recently changed job is still being reported in 'billing' status. When searching for things in the status of 'closed', my job 1100 is listed there as well.
Just to be clear, is the record being added as a new record rather than updating the existing record?
Link to comment
Share on other sites

sure! This is from my statuseditGO page:<!--#Include file="Lib.asp"-->'function from Lib: createtransactionrec(parmjobNO, parmtranstype, parmuser, parmstagedate, parmnotes)createtransactionrec session("txtJobNo"), _ request.form ("transtypeid"), _ session("CustomerID"), _ now (), _ request.form ("txtNotes")response.redirect("statusupdateok.asp")%>-----------------------------------------------------------------------------------------And here is the function from my included Lib page:public function createtransactionrec(parmjobNO, parmtranstype, parmuser, parmstagedate, parmnotes)createtransactionrec=0'create an instance of the Database Input/Output classdim atcbset atcb = new DBIOif atcb is nothing then response.write("<br><br>CRITICAL ERROR - Unable to allocate class instance.<br>") session("Message1")="CRITICAL ERROR - Unable to allocate class instance." response.redirect("Home.asp") createtransactionrec=0end ifatcb.constructordim tempsql'GET HIGHEST SEQ - update IDNumbers and use svars for text!!tempsql = "SELECT tblTransactions.JobNo, tblTransactions.TransactionTypeID AS ID, " & _" tblTransactions.UserID, tblTransactions.StageDate, tblTransactions.Notes, tblTransactions.Sequence" & _" FROM tblTransactions " & _" WHERE JobNo=" & parmJobNO & " " & _" ORDER BY Sequence DESC;"atcb.execsql(tempsql)session("Sequence")=parmseqsession("CustomerID")=parmuserresponse.write(tempsql)response.write ("<p> JobNo:" & parmjobNO)response.write ("<p> TransTypeID:" & parmtranstype & "</p>")response.write ("<p>UserID:" & parmuser & "</p>")response.write ("<p> Stagedate:" & parmstagedate & "</p>")response.write ("<p> Notes:" & parmnotes & "</p>")dim nextIDif atcb.rs.recordcount > 0 then atcb.rs.movefirst nextID=atcb.rs("Sequence")+1else nextID=1end ifresponse.write ("<p> seq:" & nextID & "</p>")if atcb.rs.RecordCount > -1 then atcb.rs.AddNew atcb.rs("JobNo") = parmJobNO atcb.rs("ID") = parmtranstype atcb.rs("UserID") = parmuser atcb.rs("StageDate") = now() atcb.rs("Notes") = parmnotes atcb.rs("Sequence") = nextID atcb.rs.updateend ifsession ("txtJobNo") = (atcb.rs("JobNo"))session ("txtTransactionID") = (atcb.rs("TransactionID"))session ("txtStageDate")=(atcb.rs("StageDate"))session ("txtNotes")=(atcb.rs("Notes"))session ("txtSequence")=(atcb.rs("Sequence"))atcb.destructorset atcb=nothingresponse.write ("<p>ALL DONE!!!</p>")createtransactionrec=1end function-------------------------------------------------------------------------------ok, let me know if you need more stuff...Thanks for taking timeout to help me - I greatly appreciate that!! :)

Just to be clear, is the record being added as a new record rather than updating the existing record?
Hello Jesh, The existing record is being updated. [You can take a gander at the code i just posted if you are so inclined...] :)
Link to comment
Share on other sites

I have run it in Access....in there the SQL runs correctly, and my data is ordered appropriately.more food for thought: The transaction table can contain many rows of data that pertain to one particular job number; hence, there can be many records with JobNo value=1100, but the sequence field increments along with the autonumber ID field. The latest sequence # is the field i use to be capable of choosing the latest entry time/date, the userID [who updated it] and accompanying notes.So now I think that the query is ok. (since job 1100 did the status 'billing' and the most recent with that status is pulled by the sql - as it was supposed to do, but i need some discernment between a records absolute current status of 'closed' and the record's previous status instance, the record was in 'billing' status, was in 'scheduling' status at some point).Does what i am asking make sense? It seems tough to explain.The problem I think is that I need to find a way to ensure that only the recordset with the most recent date, most recent and current status, and the highest sequence number is chosen from the Transactions table. Is there some way in asp to move to next record based on above criteria...some way to devise an if-then statement that will solve the problem?This is a real head-scratcher for me [ :) I am no code guru, at least not yet].Hopefully, with your help of course, we'll come up with something.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...