p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

proasp_codeclinic thread: Help Record is being overwritten


Message #1 by "Nadia Hamza" <hamzan@s...> on Tue, 5 Mar 2002 00:43:31
I really appreciate your help.  The maxnum number that I'm using can't be 
modified to an autonumber.  We have a very specific way of numbering 
papers that come in.  I, originally, thought the problem might be 2 papers 
coming in at the same time but this isn't the case.  There can be hours 
between the 2 papers.  Sometimes the second one is overwriting the first 
one.  I don't know why.  It's as if Access doesn't realize that the record 
exists and just carries on and puts the new information over the existing 
information instead of going to a new record.  This is a sporadic problem 
that I need to solve.  It really has me stumped.  qPapers is an Access 
querry.  Will the array work with the query?

Thanks for all your help and ideas!!!!!

> Hello Nadia,
> 
> Unless you need to keep the original name of the file why not just use 
> the CreateUniqueFile or the GetTempname method from Sa-FileUp. The 
> problem with the content being overwritten is the manner in which you're 
> generating and then using maxnum. What I mean is if two folks hit the 
> page at the same time maxnum is likely to be the same for both of them, 
> remember the changes aren't committed till you issue the Update on the 
> recordset. I'm also assuming you meant to use adCmdTableDirect instead 
> of adCmdStoredProc. I'd also like to suggest using the With keyword and 
> the Array form of the Update method since you have a lot of 
> fields/values to contend with preparing the Arrays before opening the 
> recordset should improve the performance.
> 
> Dim objFldRay, objValRay
> 
> Set objFldRay = Array("Title",  "FName",  "LName", etc...)
> Set objValRay = Array( title, fname, lname, etc..)
> 
> Set objRec = Server.CreateObject("ADODB.RecordSet")
> With objRec
>     .Open "qPapers", strConnect, adOpenKeyset, adLockOptimistic, 
> adCmdTableDirect + adExecuteNoRecords
>     .AddNew objFldRay, objValRay ' No need for Update statement when 
> using Arrays
> End With
> 
> ' Check errors and close recordset as before
> 
> If you need to know the number for some reason you can add something 
> like this...
> Dim intID
> 
> With objRec
>     .Open ...
>     .AddNew ...
>     'Check for Errors
>     If Err.Number = 0 Then ' errors can be negative numbers
>         intID = .Fields("ID")
>         .Fields("PaperNumber") = intID
>         .Update
>     End If
> End With
> 
> 'Close recordset etc...
> 
> John
> 
> >Subject: Help Record is being overwritten
> >From: "Nadia Hamza" <hamzan@s...>
> >Date: Tue, 5 Mar 2002 00:43:31
> >X-Message-Number: 1
> >
> >Hi,
> > 
> >I'm getting users to submit their contact information along with a 
paper.  
> >What is happening is that occasionally, the contact information is 
being 
> >overwritten by the next paper that is coming along.  This is becoming 
an 
> >urgent problem.  Any help would be GREATLY appreciated.  The code is 
below.
> > 
> >Nadia
> > 
> ><%
> >
> >Set objRec = Server.CreateObject("ADODB.Recordset")
> >          sql = "SELECT Max(PaperNumber) AS maxnum FROM qPapers WHERE 
> > (Conference) ='" & conference & "'"
> >          objRec.Open sql, strConnect, adOpenStatic, adLockReadOnly, 
> > adCmdText
> >
> >          If IsNull (objRec("maxnum")) Then
> >             maxnum = 1
> >         Else
> >             maxnum = objRec("maxnum").Value + 1
> >          End If
> >
> >         Set objRec = Server.CreateObject("ADODB.Recordset")
> >         objRec.Open "qPapers", strConnect, adOpenKeyset, 
> >adLockOptimistic, 
> > adCmdStoredProc
> > 
> >     objRec.AddNew
> >     objRec("Title") = title
> >     objRec("FName") = fname
> >         objRec("LName") = lname
> >         objRec("Wcompany") = company
> >     objRec("Wadd1") = address1
> >     objRec("Wadd2") = address2
> >         objRec("Wadd3") = address3
> >         objRec("Wcity") = city
> >     objRec("WProvorState") = province
> >     objRec("WaddCode") = postalcode
> >         objRec("Wcountry") = country
> >        objRec("Wphone") = phone
> >         objRec("Wext") = phoneext
> >         objRec("Wfax") = fax
> >         objRec("Wpager") = pager
> >         objRec("email") = email
> >         objRec("Password") = password
> >     objRec("PaperFunction") = "Contact Author"
> >         objRec("Conference") = conference
> >         objRec("Session") = session
> >         objRec("PaperNumber") = maxnum
> >         objRec("PaperTitle") = papertitle
> >         objRec("Keyword1") = area1
> >         objRec("Keyword2") = area2
> >         objRec("Keyword3") = area3
> >        objRec("Keyword4") = area4
> >         objRec("InitialPaperFile") = newfilenamewithpath
> > 
> >         CheckLastError(Err)
> > 
> >         If Not Err.Number > 0 Then
> >             ' Response.Write "Hello"
> >             upload.SaveAs newfilename
> >             objRec.Update
> >         End If  
> >
> >
> >    ' Response.Write "<BR> Your Paper Number is:" & conference & "-" & 
> > maxnum
> > 
> > ' now close and clean up
> > objRec.Close
> > Set objRec = Nothing
> >     Set objCommand = Nothing
> > 
> > 
> > %> 
> > 
> > 
> >
> 
> 

  Return to Index