| Author |
 Topic  |
|
|
sogoth
New Member
United Kingdom
73 Posts |
 Posted - 23 August 2001 : 04:51:29
|
Here's an interesting one for you...
I have one specific page that calls three seperate recordsets, most of the time the page works perfectly with all the information displaying correctly. Then other times it will claim that one of the recordsets is completely empty, despite the record that I'm trying to display already having displayed properly at the top of the page.
for example...
the recordset in question contains all the company contact details.
The top of the page takes the company name, reference number and contact name from this recordset and displays them perfectly at the top of the page.
Further down the page is a link to add a call note to the contact details so I want to append the reference number from the recordset so that the add_notes form can display the correct company name.
It occasionally claims that the recordset with the reference number in is empty!
Does anyone have any idea what could possibly be causing such a strange response please?
thanks!
Paul -
Complete Muppet, Borderline Feeble! |
|
|
Kat
Advanced Member
United Kingdom
3065 Posts |
Posted - 23 August 2001 : 05:26:31
|
Can you post the code that you are using? Wouldn't it be better to store all the values you need to display in variables and then you could use them anywhere on the page without worrying about retaining the recordset. Or using an array created from the recordset?
KatsKorner
|
 |
|
|
sogoth
New Member
United Kingdom
73 Posts |
Posted - 23 August 2001 : 05:30:29
|
Oooo what a good thought!
back in a sec.. just going to try rewriting it and dumping everything into variables...
Paul -
Complete Muppet, Borderline Feeble! |
|
|
|
Kat
Advanced Member
United Kingdom
3065 Posts |
Posted - 23 August 2001 : 05:58:03
|
Ok! Go get 'em Paul! 
KatsKorner
Edited by - kat on 23 August 2001 05:58:19 |
|
|
|
sogoth
New Member
United Kingdom
73 Posts |
Posted - 23 August 2001 : 09:37:14
|
hoorah! that appears to be working now, thank you!
Hmmm wonder why Ultradev's code didn't work :looks like I'll be stopping using Ultradev's prag and drop connection strings then!
Paul -
Complete Muppet, Borderline Feeble! |
|
|
|
Kat
Advanced Member
United Kingdom
3065 Posts |
Posted - 23 August 2001 : 09:48:11
|
Glad that you sorted it! 
KatsKorner
|
|
|
|
sogoth
New Member
United Kingdom
73 Posts |
Posted - 23 August 2001 : 09:56:29
|
So am I
it was exceedingly confusing to say the least!
Paul -
Complete Muppet, Borderline Feeble! |
|
|
|
Kat
Advanced Member
United Kingdom
3065 Posts |
Posted - 23 August 2001 : 10:28:21
|
Yeah, trying to use recordsets the way you were can end up in a real mess because you have to keep track of your position in the recordset as you try and process it.
If you take out what you need straight away then it is much easier and more efficient too.
KatsKorner
|
|
|
|
sogoth
New Member
United Kingdom
73 Posts |
Posted - 23 August 2001 : 10:51:34
|
Bah! I've found another problem relating to the linking of these **** tables!
the SQL that I'm using binds various tables together (This works properly as I've tested it in SQL server's code window)
these tables are :
contact details
company name (as you can have more than one contact per company)
notes
project details
each of these contains a column called "Con" which is the primary key in the contact details table and the foreign key in the others so they know which links to which.
How do I specify *which* value of Con to place into the variable at the start? because at the moment some of the contacts do not have any notes attached, yet the SQL comes back with the titles for each of the columns so I end up with the *actual* value of Con and a value of NULL for Con and it appears to be picking the value of NULL by default 
Paul -
Complete Muppet, Borderline Feeble! |
|
|
|
davemaxwell
Access 2000 Support Moderator
USA
3020 Posts |
Posted - 23 August 2001 : 13:24:26
|
I am assuming then, that you are doing select * from all the tables. Specify exactly which fields you want to return. Any fields which have common names will require you to prefix the fieldname with the appropriate table (ie contactdetails.con).
This is always a better approach then doing select *. You are bringing back smaller recordsets, which means slower response times. If you specify the fields, you will have faster response times, happier customers and happier servers.....
Dave Maxwell
--------------
Proud to be a "World Class" Knucklehead |
|
|
|
sogoth
New Member
United Kingdom
73 Posts |
Posted - 29 August 2001 : 08:51:29
|
unfortunately I need pretty much all the info from all the tables to build the page.
the one line that I'm having problems with is assigning the value contained in Contact.Con to a variable
the SQL query being used is :-
SELECT *
FROM Services RIGHT OUTER JOIN
Contact ON Services.Ser = Contact.Ser LEFT OUTER JOIN
project ON Contact.Con = project.con LEFT OUTER JOIN
Notes ON Contact.Con = Notes.con RIGHT OUTER JOIN
CompanyName ON Contact.CN = CompanyName.CN
WHERE Contact.CN = var
when you try to put Contact.Con into a variable :-
con = Recordset1.Fields.Item("Con").Value
it doesn't know which value to take so puts nothing in there.
if you try to be more specific ( con = Recordset1.Fields.Item("Contact.Con").Value ) it throws a strop and comes back with "Item cannot be found in the collection corresponding to the requested name or ordinal. "
I've tried putting this through SQL server with a value in where var is and there is a value in Contact.Con so I'm slightly bemused as to why it doesn't put a value in there when called via ASP
Paul -
Complete Muppet, Borderline Feeble! |
|
|
|
sogoth
New Member
United Kingdom
73 Posts |
Posted - 31 August 2001 : 03:49:03
|
anybody? please?
Paul -
*bugger* said the fairy as it flew into a window |
|
|
|
sogoth
New Member
United Kingdom
73 Posts |
Posted - 31 August 2001 : 11:17:04
|
I've had to cheat to fix this problem and create a second RS to just find this one value *sigh*
Paul -
*bugger* said the fairy as it flew into a window |
|
|
| |
Topic |
|
Topic Locked
