SQL query with join not working

[rkasnick]

Here's my pronlem. I have an MSDE database with two tables, tblHeader and tblDetail, a one to many relationship. In my code (posted below) I use an ADODC control. Data is returned, but using the control on the form I have to click the next record arrow as many times as there are detail records to advance 1 record. In other words, if I have 1 header and 2 details, I have to click the next arrow twice to move to the next detail record. The ADODC caption says 4 records are returned in stead of two. I am fairly sure the error is in my join statement, and have tried other join types, but to no avail. Any suggestions?

Public Sub form_activate()
On Error Resume Next
frmHatsEdit.Width = 14000
frmHatsEdit.Height = 11000

With Adodc2
    .ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=Products;Data Source=RODKASNICK"
    .RecordSource = "SELECT tblHeader.*, tblDetail.* FROM tblHeader inner join tblDetail on tblHeader.preliminary_number = tblDetail.preliminary_number where tblHeader.preliminary_number = '" & hatskey & "' order by tblDetail.comp_number ASC"
    .CursorLocation = adUseClient
End With
Adodc2.Refresh
Adodc2.Caption = Adodc2.Recordset.AbsolutePosition & " of " & Adodc2.Recordset.RecordCount
Set Text1.DataSource = Adodc2
Set Text2.DataSource = Adodc2
Set Text3.DataSource = Adodc2
Set Text4.DataSource = Adodc2
Set Text5.DataSource = Adodc2

      
      Text1.DataField = "preliminary_number"
      Text2.DataField = "comp_number"
      Text3.DataField = "factory_number"
      Text4.DataField = "series_vendor"
      Text5.DataField = "series_description"

End Sub

 

[gmmastros]

First.... I don't think you need the 'User Id=sa' in your connection string singe you are using 'Integrated Security=SSPI'

so your connection string could be....

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Products;Data Source=RODKASNICK

That probably won't fix your data issues though. Are you expecting just 2 records, eventhough you are selecting all fields from both tables. I suggest you post some sample data, and explain what data you are expecting to return.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[rkasnick]

Thanks George, for your response. Yes, I am expecting just two records (in this case) however there are other header/detail relationships that have as many as 12 details to one header. I have intentionally marked all fields to be returned for simplicity in posting, whereas I actually will select the fields themselves in the final version. My problem is that the join is returning 4 records when there are only 2 in the table, 9 when there are 3, 36 when there are 6. See the issue? for every detail record, it is multiplying each record in the recordset by as many as there are unique detail records selected.

 

[wangdong]

Hi rkasnick, I think if you check your sql "where" condition, you may find a answer for this.

 

[HarleyQuinn]

Wangdong - I don't see what you're driving at there?????

Harleyquinn

---------------------------------
For tsunami relief donations

 

[johnwm]

There is a single quote either side of hatskey:

'" & hatskey & "'

It appears to be a numeric field

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first
'If we're supposed to work in Hex, why have we only got A fingers?'
Essex Steam UK for steam enthusiasts

 

[HarleyQuinn]

Ah, I'm used to working in Oracle. I take it that's a no-no in MSDE.

Harleyquinn

---------------------------------
For tsunami relief donations

 

[rkasnick]

Thanks to all who have responded, I have resolved this with other coding structures.