Recordset Help!

[danz321]

ok so ive made this site that adds jobs to a database. that works fine, its just when u go on the page that displays the info on the databsae the results are strange....

because the code is connecting to multiple tables i had to

Recordset.Close
Recordset.Open var

a lot. and this messed up the looping and i dont know how to fix it.
if you dont know what im talking about the code is below :D
Please help mee!!!

this is the page that displays the database info:
http://www.intopeople.developer.graphyx.net/jobs2.asp

and this is the code:
http://www.intopeople.developer.graphyx.net/jobs2.txt

and it should look like this....
http://www.intopeople.developer.graphyx.net/should.txt

thanks

dan

[buntine]

How is your database set out? There should be relations between the jobs table and all of the other mentioned tables (JobSector, Per, Region, Type) by means of a foreign key.

At the moment you have about 5 SQL queries, though, it could be made into one SQL query that grabs and sorts the data in a logical manner. Also, your not telling SQL which JobSector, Per, Region, and Type record to grab. Your just getting them all in selection order.

Regards.

[danz321]

how do u put all those entries into one tho? i tried doing that previously but i could only use one FROM statement in it so i couldnt work out how to get the info from every table.

write that for me and ill be sorted! please help!

thanks

dan

[buntine]

You only need one FROM statement. Good knowledge of SQL is imperative when developing data-driven Web Applications.

You need to perform a "join". You will need to set it out somewhat like this: (note, your query will differ depending on your naming conventions)

SELECT jobs.EmailAddress, jobs.JobTitle, jobs.Salary1, jobs.Salary2,
jobs.SalaryRateOTE, jobs.SalaryRateIncBen, jobs.SalaryRateProRata,
jobs.SalaryRateNegShow, jobs.SalaryRateNegHide, jobs.Town, JobSector.jobSector, Per.per, Region.region, Type.type
FROM jobs, JobSector, Per, Region, Type
WHERE JobSector.job_id = Jobs.job_id AND
Per.job_id = Jobs.job_id AND
Region.job_id = Jobs.job_id AND
Type.job_id = Jobs.job_id;

This query assumes you have designed your database relationally by referencing the primary key in Jobs with Foreign Keys in all related entities (tables).

If none of this makes sense to you, there are two possible reasons. 1) I am a horrible teacher; or 2) You need to research database design and acknowledge the key concepts and conventions.

Regards.

[danz321]

riight im pretty sure i understood what u said, but im still getting problems..
in the data base the primary key in "jobs" is "ID_no" and the primary key in all the other tables is "id", so when im joining them they should be

WHERE per.id = jobs.id_no AND type.id = jobs.id_no .. etc etc...

correct?

but i still get the error:

ADODB.Recordset error '800a0e78'

Operation is not allowed when the object is closed.

/jobs2.asp, line 134

dont suppose u know whats going on there?

please help!!

thanks
dan

[phpnovice]

That error message has nothing to do with the format of nor the selections in your SQL statement. The error message indicates that you're attempting to use either a connection object or a recordset object that is not currently open. This means you either haven't opened it yet or you have prematurely closed it without reopening it.

[buntine]

Are you still opening and closing the other recordsets? You no longer need recordset2, recordset3, etc.

There should be one .open and one .close. Can you post your updated code?

Regards.

[danz321]

ok i have a theory to why it doesnt work,
in the "add_to_jobs.asp" page i still have the strSQL1, 2 ,3 etc. to add data to the database, so im guessing becuae in teh jobs2.asp there is only strSQL the problem is happening...

anyway i realised this so i changed the addtojobs.asp page so there was only strSQL, but now im getting another error! :mad:

here's the code and error etc.

addtojobs:
http://www.intopeople.developer.graphyx.net/add_to_jobs.txt

jobs2:
http://www.intopeople.developer.graphyx.net/asp_in_jobs2.txt

error:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver] Cannot update. Database or object is read-only.

/add_to_jobs.asp, line 27

and no, the database isn't read-only :p

hope u can help!!

thanks

dan