sql involving three tables

[puzzleofurheart_23]

hello..i can't get my code to work..here's the details
i have three tables

table 1 :Company_Profile
fields: address,comp_no

table 2: Project_Info
fields: pro_no, title

table 3: Company_Project
fields : com_no, pro_no

I want to link the tree tables..I have a search button..the input is address..
So, I need to get the pro_no and comp_no of the address given including the title..

this is my current code:

adodc1.recordsource = "select * from address, a.comp_no, b.pro_no from Company_Profile,Company_Profile a,Project_Info b where address = txt2.text and a.comp_no = comp_no and b.pro_no = a.pro_no"
adodc1.refresh..


please..i Need it so bad..
tnx..

[lintz]

This is one way

"SELECT Company_Project.pro_no AS Pro_No, Company_Project.comp_no AS Comp_No FROM Company_Project, Company_Profile WHERE Company_Profile.Address = '" & txt2.text & "'"

[puzzleofurheart_23]

This is one way

"SELECT Company_Project.pro_no AS Pro_No, Company_Project.comp_no AS Comp_No FROM Company_Project, Company_Profile WHERE Company_Profile.Address = '" & txt2.text & "'"

how can I get the title?

[RobDog888]

Ad that field to your select field list.

[puzzleofurheart_23]

Ad that field to your select field list.

i already did..but i want to filter it by pro_no..can you tell me what's wrong this code :

Adodc1.RecordSource = "SELECT Company_Profile.address AS address, Project_Info.title AS title, Project_Info.pro_no as Pro_no1, Company_Projects.pro_no as Pro_no2 FROM Company_Projects, Company_Profile, Project_Info WHERE Project_Info.pro_no = Company_Projects.pro_no And Company_Profile.contact_lname like " & hold
Adodc1.Refresh

I have an error..object not required.

[RobDog888]

Ah you are using that ADODC data bound control. This makes things more difficult. Using all ADO code instead makes it easier but for now are you binding your controls to the fields in the data control?

[puzzleofurheart_23]

Ah you are using that ADODC data bound control. This makes things more difficult. Using all ADO code instead makes it easier but for now are you binding your controls to the fields in the data control?

I have textbox where the address will be entered..and On the interactive change of the textbox, i have a datagrid that will show the matched address. I use ADODC so that it will show all the data on real time..I need to have a double condition so that I can filter the result by pro_no bcoz it appears many times on the grid.

I can't get it to work.

Can you help me?

[RobDog888]

I dont believe the adodc control has a fileter property but if you had used pure ado code then all these issues wouldnt be an issue. I dont use the ado data control for these reasons. Perhaps someone who does may know a way around that.

the only thing I can think of is to re execute a sql statement that will add the extra filtering in the where clause.

[si_the_geek]

Thread moved to Database Development forum

Adodc1.RecordSource = "SELECT Company_Profile.address AS address, Project_Info.title AS title, Project_Info.pro_no as Pro_no1, Company_Projects.pro_no as Pro_no2 FROM Company_Projects, Company_Profile, Project_Info WHERE Project_Info.pro_no = Company_Projects.pro_no And Company_Profile.contact_lname like " & hold
Adodc1.Refresh

I have an error..object not required.

I've never heard of that error.. I presume it was actually "Object required".

If that is the case, check what Adodc1 is - as that not being an Object (a control etc) is the only thing which could cause the error.

[RobDog888]

I think if adodc code is in form load it may give that error as the control isnt initialized yet perhaps.

[zeezee]

I dont know about the ADODC, but a SQL with INNER JOIN could get you the information.

eg:

SELECT * FROM Company_Project INNER JOIN Project_Info  ON Company_Project.pro_no = Project_Info. pro_no WHERE Company_Profile.Address = '" & txt2.text & "'"

You could get Title from this.

Why dont you use ADO instaed of ADODC?

[puzzleofurheart_23]

I dont know about the ADODC, but a SQL with INNER JOIN could get you the information.

eg:

SELECT * FROM Company_Project INNER JOIN Project_Info  ON Company_Project.pro_no = Project_Info. pro_no WHERE Company_Profile.Address = '" & txt2.text & "'"

You could get Title from this.

Why dont you use ADO instaed of ADODC?

i don't know how to use ado..can you give a good link where I could learn about it?

[si_the_geek]

See the "Classic VB - ADO" section of our Database Development FAQs/Tutorials (at the top of this forum)

[zeezee]

i don't know how to use ado..can you give a good link where I could learn about it?

Certainly, Check our own DB FAQs.

http://www.vbforums.com/showthread.php?t=337051#ado

http://www.vbforums.com/showthread.php?t=337051#bound
http://www.vbforums.com/showthread.php?t=337051

Data Bound Control is good, its easy and not much PIA, but its still better if you know whats going on.
Databound control is for lazy people who doesnt want to learn to do better. (no any offense to you though )

[RobDog888]

In his other thread it was pointed out that his current statement is missing double quotes on part of the statemet.

http://www.vbforums.com/showthread.php?t=525189