Records double up after second log in 1

[evr72]

Hello,

I have a long script that takes records from one table and puts them into another table depending on the "companytype", "cycle", "functionalarea" and "companyuser"
this site is for admins of the different companies, one company can have multiple "companyuser"
So for example company xyz has a "companyuser" named Joe.
Joe belongs to
"companytype" Home
"cycle" Expenses
"functionalarea" Dine Out

When an admin for company bbb logs in he can see all his questions properly but if he logs in again it loads Joe into the "companyuser" field for the "cycle" and "funcitionalarea" of company bbb.

I have narrowed to the folloing part of my code

set rstmpmy = dal_users.Query(" cycle='" & rstmp("cycle") & "' and functionalarea='"&rstmp("functionalarea")&"'","")
if not rstmpmy.eof then
dal_answers.Param("itm")= rstmp("itm")
dal_answers.Value("companyuser")=rstmpmy("companyuser")
strUpdate = "update answers set companyuser='"&rstmpmy("companyuser")&"' where itm in (select itm from questions where cycle='"&rstmp("cycle")&"' and functionalarea='"&rstmp("functionalarea")&"')"
CustomQuery(strUpdate)
dal_answers.Update()
end if
rstmpmy.close
set rstmpmy=nothing

what I was thinking in doing was to add this

elseif
if not rstmpmy.eof then
dal_answers.Param("itm")= rstmp("itm")
dal_answers.Value("companyuser")=rstmpmy("companyuser")
strUpdate = "update answers set companyuser='"&rstmpmy("companyuser")&"' where itm in (select itm from questions where cycle='"&rstmp("cycle")&"' and functionalarea='"&rstmp("functionalarea")&"')"
CustomQuery(strUpdate)
dal_answers.Update()

but I get the following error

Error Type:
ADODB.Field (0x80020009)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

Not sure how to go about fixing this one any help is much appreciated!!!

 

[guitarzan]

Psuedo code of your original code:

if not rstmpmy.eof then
   [COLOR=green]'rstmpmy returned one or more record, proceed[/color]
   [COLOR=blue]Do some stuff with rstmpmy recordset.[/color]
end if

And what you want to change it to:

if not rstmpmy.eof then
   [COLOR=green]'rstmpmy returned at least one record, proceed[/color]
   [COLOR=blue]Do some stuff with rstmpmy recordset.[/color]
else
   [COLOR=green]'rstmpmy returned NO records[/color]
   [COLOR=red]Do some stuff with rstmpmy recordset anyway! error![/color]
end if

At least, that explains the error.

 

[evr72]

guitarzan,
correct, but what I don't know or understand or I am confused is how to handle the "elseif" statement.

Because if I take the "elseif" statement out then the program works but when the admin user logs in more than once it pulls in the "companyuser" eventhough it does not belong to his/her company.
but even if I try this

set rstmpmy = dal_users.Query(" cycle='" & rstmp("cycle") & "' and functionalarea='"&rstmp("functionalarea")&"'","")
if not rstmpmy.eof then
dal_answers.Param("itm")= rstmp("itm")
dal_answers.Value("companyuser")=rstmpmy("companyuser")
strUpdate = "update answers set companyuser='"&rstmpmy("companyuser")&"' where itm in (select itm from questions where cycle='"&rstmp("cycle")&"' and functionalarea='"&rstmp("functionalarea")&"' [COLOR=red]and company='"&rstmp("company")&"'[/color])"
CustomQuery(strUpdate)
dal_answers.Update()
end if
rstmpmy.close
set rstmpmy=nothing

I still get the same error

Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.

 

[evr72]

I triple check all my field names and all are correct

 

[guitarzan]

Regarding the new error, something is clearly off. I can't see the underlying sql for "rstmpmy", and I don't even see "rstmp" being defined here. If adding the line in red caused the error and removing it allowed it to work, then you have to look at the SQL behind rstmp and see if a "company" field is included.

Regarding the rest, you might need to post more code, as I really don't understand the problem. If I read it literally, it looks like the wrong value is being read when a user logs in... so, maybe your queries are correct but the login code is wrong?

 

[evr72]

guitarzan,
you were correct my "company" was not defined in the questions table.
I corrected that. but what is going on is this
users table

companyuser   company   cycle    functionalarea   username  pwd
joe             xyz      home     dishes     
                xyz                               sam       123
                www                               phil      345

questions table

questionid    description   cycle  functionalarea
1             what color?   home    dishes
2             what size     home    dishes
3             what type     patio   garden
4             what lenght   patio   garden

answers table will load questions and users for each company
this is the way is supposed to work

questionid description cycle functionalarea companyuser company
1          what color? home  dishes           joe        xyz
2          what size   home  dishes           joe        xyz
3          what type   patio garden                      xyz
4          what lenght patio garden                      xyz
1          what color? home  dishes                      www   2          what size   home  dishes                      www     
3          what type   patio garden                      www
4          what lenght patio garden                      www

so when the "username" first logs in the answers table looks like the above example
when the "username" logs off and back in the answers table looks like this

questionid description cycle functionalarea companyuser company
1          what color? home  dishes           joe        xyz
2          what size   home  dishes           joe        xyz
3          what type   patio garden                      xyz
4          what lenght patio garden                      xyz
1          what color? home  dishes           joe        www   2          what size   home  dishes           joe        www     
3          what type   patio garden                      www
4          what lenght patio garden                      www

I think is because of this part of the code

set rstmpmy = dal_users.Query(" cycle='" & rstmp("cycle") & "' and functionalarea='"&rstmp("functionalarea")&"'","")
if not rstmpmy.eof then
dal_answers.Param("itm")= rstmp("itm")
dal_answers.Value("companyuser")=rstmpmy("companyuser")
[COLOR=red]strUpdate = "update answers set companyuser='"&rstmpmy("companyuser")&"' where itm in (select itm from questions where cycle='"&rstmp("cycle")&"' and functionalarea='"&rstmp("functionalarea")&"')"[/color]
CustomQuery(strUpdate)
dal_answers.Update()
end if
rstmpmy.close
set rstmpmy=nothing

so I was trying to come up with something that will also indicate the "company" unfortunatelly "company" does not exist in the "questions" table, hence me trying "rstmp"
I do have the company in my "users" table and my "answers" table.

I just added

set rstmpmy = dal_users.Query(" cycle='" & rstmp("cycle") & "' and functionalarea='"&rstmp("functionalarea")&"'","")
if not rstmpmy.eof then
dal_answers.Param("itm")= rstmp("itm")
dal_answers.Value("companyuser")=rstmpmy("companyuser")
strUpdate = "update answers set companyuser='"&rstmpmy("companyuser")&"' where itm in (select itm from questions where cycle='"&rstmp("cycle")&"' and functionalarea='"&rstmp("functionalarea")&"'[COLOR=red]and rstmp1("company")=rstmpans("company")&"'&"'[/color])"
CustomQuery(strUpdate)
dal_answers.Update()
end if
rstmpmy.close
set rstmpmy=nothing

rstmp1 is the users table
and
rstmpans is the answers table

but I still get the "companyuser" added to the wrong "company"

 

[guitarzan]

Something like this?

strUpdate = "update answers set companyuser='"&rstmpmy("companyuser")&"' where [COLOR=red]company='"&rstmpmy("company")&"') and [/color]itm in (select itm from questions where cycle='"&rstmp("cycle")&"' and functionalarea='"&rstmp("functionalarea")&"')"

 

[evr72]

I tried something similar and on what I tried and what you suggested I get the following error

Error Type:
Microsoft JET Database Engine (0x80040E14)
Extra ) in query expression 'company='xyz') and itm in (select itm from questions where cycle='home' and functionalarea='dishes')'
[code]

this heppens if the "username" tries to log in more than one time.

the first time works well but after that I get the error above

 

[evr72]

Arggg, fixed the error but that did not work, still get the "companyuser" added to all the companies

 

[guitarzan]

If you've narrowed it down to the sql in strUpdate, all I can suggest is to "response.write strUpdate" when it works, and again when it doesn't, and see why the sql is not updating what you expect.

 

[evr72]

After much work I was able to get this working, I changed the

dal_answers.Value("companyuser")=rstmpmy("companyuser")
 
to
[COLOR=red]rsans[/color]("companyuser")=rstmpmy("companyuser")


and 

strUpdate = "update answers set companyuser='"&rstmpmy("companyuser")&"' where itm in (select itm from questions where cycle='"&rstmp("cycle")&"' and functionalarea='"&rstmp("functionalarea")&"'and rstmp1("company")=rstmpans("company")&"'&"')"

to

strUpdate = "update answers set companyuser='"&rstmpmy("companyuser")&"' where itm in (select itm from questions where cycle='"&rstmp("cycle")&"' and functionalarea='"&rstmp("functionalarea")&"'and rstmp1("company")=[COLOR=red]rsans[/color]("company")&"'&"')"

this works well but from time to time I get the following error

Error Type:
ADODB.Recordset (0x800A0CB3)
Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.

not sure why or what is causing it, it seems to randomly happen

any ideas???

 

[guitarzan]

Well, you can check the link below for a variety of scenarios that cause that error:

http://tutorials.aspfaq.com/8000xxxxx-errors/why-do-i-get-800a0cb3-errors.html

To identify the sql that causes the error, surround the command that executes the query with the code below

On Error Resume Next
LINE THAT EXECUTES THE QUERY
if err.number <> 0 then 
   response.write "Got Err#" & err.number & ": " & err.description & "<BR>sql:<BR>" & strUpdate
End If
On Error Goto 0

As far as debugging the problem, depends on the database you are using. If SQL Server, maybe paste the query into Query Analyzer

 

[evr72]

I am using an ms access 2007 db