[dba-SQLServer] Combo Boxes not showing Recordset

[David Emerson]

Found the problem.  For this database I called the primary keys in all
tables "ID" (on the request of the client's SQL developer).  Access doesn't
seem to like this in a combobox recordset.  Once I gave all primary keys
unique names the lists show as expected.

Regards

David

-----Original Message-----
From: dba-SQLServer [mailto:dba-sqlserver-bounces at databaseadvisors.com] On
Behalf Of David Emerson
Sent: Thursday, 15 August 2019 10:28 a.m.
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] Combo Boxes not showing Recordset

I have just scripted and restored the database to SQL 2008 SR2 and it has
the same problem :(

-----Original Message-----
From: dba-SQLServer [mailto:dba-sqlserver-bounces at databaseadvisors.com] On
Behalf Of David Emerson
Sent: Wednesday, 14 August 2019 4:35 p.m.
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] Combo Boxes not showing Recordset

Hi Paul,

Initially the value of the parameter is 0 and it is passing correctly and
working as it should.

When I run the stored procedure direct in SQL it returns the correct
records.
In Access I have checked the records in the recordset by looping through
them when the recordset is retrieved and the correct records are there -
they are just not showing in the combobox list.

David

-----Original Message-----
From: dba-SQLServer [mailto:dba-sqlserver-bounces at databaseadvisors.com] On
Behalf Of Paul Hartland via dba-SQLServer
Sent: Wednesday, 14 August 2019 4:25 p.m.
To: SQLServerList
Cc: Paul Hartland
Subject: Re: [dba-SQLServer] Combo Boxes not showing Recordset

David,

In the stored procedure what is the value of the parameter @DistributorID
and have you checked that both tables dbo.tblDistributor and
dbo.tblDistributorLossCode have that value in the dbo.tblDistributor.ID
<http://dbo.tbldistributor.id/> and dbo.tblDistributorLossCode.DistributorID
fields

Paul


On Wed, 14 Aug 2019, 04:27 David Emerson, <newsgrps at dalyn.co.nz> wrote:

> Hi Listers,
>
> Access 2010 accdb, SQL 2014 backend.
>
> I have a screen that gets recordsets from SQL using the following code:
>
>     Dim rstTemp As ADODB.Recordset
>
>     Set rstTemp = basRunDataObject("dbo.spfrmDistributorLossCodeFactor " &
> Nz(Me!cboFilter, 0), adCmdText)
>     Set Me.Recordset = rstTemp
>
>     Set rstTemp = rstTemp.NextRecordset
>     Set Me!cboFilter.Recordset = rstTemp
>
>     Set rstTemp = rstTemp.NextRecordset
>     Set Me!cboDistributorLossCodeID.Recordset = rstTemp
>
>     rstTemp.Close
>     Set rstTemp = Nothing
>
>     Me.UniqueTable = "dbo.tblDistributorLossCodeFactor"
>     Me.ResyncCommand = "SELECT dbo.tblDistributorLossCodeFactor.* FROM
> dbo.tblDistributor INNER JOIN dbo.tblDistributorLossCode ON
> dbo.tblDistributor.ID = dbo.tblDistributorLossCode.DistributorID INNER
> JOIN
> dbo.tblDistributorLossCodeFactor ON dbo.tblDistributorLossCode.ID =
> dbo.tblDistributorLossCodeFactor.DistributorLossCodeID WHERE
> dbo.tblDistributorLossCodeFactor.ID = ?"
>     Me.Requery
>
>
> The relevant part of the stored procedure is as follows:
>
> ALTER PROCEDURE [dbo].[spfrmDistributorLossCodeFactor]
>         (
>                 @DistributorID bigint
>         )
> AS
> BEGIN
>         SET NOCOUNT ON;
>
>         SELECT dbo.tblDistributorLossCodeFactor.*
>         FROM dbo.tblDistributor INNER JOIN dbo.tblDistributorLossCode ON
> dbo.tblDistributor.ID = dbo.tblDistributorLossCode.DistributorID
>                 INNER JOIN dbo.tblDistributorLossCodeFactor ON
> dbo.tblDistributorLossCode.ID =
> dbo.tblDistributorLossCodeFactor.DistributorLossCodeID
>         WHERE dbo.tblDistributor.ID = @DistributorID OR @DistributorID = 0
>         ORDER BY dbo.tblDistributor.Name, dbo.tblDistributorLossCode.Code,
> dbo.tblDistributorLossCodeFactor.StartDate
>
>         SELECT dbo.tblDistributor.ID, dbo.tblDistributor.Name AS Name,
> dbo.tblDistributor.RegistryCode AS Code, CASE WHEN
> dbo.tblDistributor.IsActive = 1 THEN 'Yes' ELSE 'No' END AS Active
>         FROM dbo.tblDistributor
>         ORDER BY dbo.tblDistributor.Name
>
>         SELECT dbo.tblDistributorLossCode.ID,
> dbo.tblDistributorLossCode.Code, dbo.tblDistributor.Name AS Distributor
>         FROM dbo.tblDistributor INNER JOIN dbo.tblDistributorLossCodeON
> dbo.tblDistributor.ID = dbo.tblDistributorLossCode.DistributorID
>         WHERE dbo.tblDistributor.ID = @DistributorID OR @DistributorID = 0
>         ORDER BY dbo.tblDistributorLossCode.Code, dbo.tblDistributor.Name
>
> /*      SELECT dbo.tblDistributorLossCode.ID,
> dbo.tblDistributorLossCode.Code, 'None' AS Distributor
>         FROM dbo.tblDistributorLossCode
>         ORDER BY dbo.tblDistributorLossCode.Code
>
> */
>
>
> When the screen opens the combo box that uses the third record set has
> nothing in it.  It shows the correct number of columns but there are no
> headers or data.
> If I replace the third record set with the remmed out recordset then the
> combo box shows the headers and data.  The only difference in the
> statements
> is that there is not a link to a second table.
> The data for the screen and the first combo box show correctly.  I have
> checked the records in the recordset by looping through them when the
> recordset is retrieved and the correct records are there - they are not
> showing in the combobox list.
>
> The PK/FK ID fields are all bigint.  It tried changing them to int, and
> numeric(18,0) but neither helped.
>
> I have tried recreating the combobox but this didn't help.  I have also
> decompiled, compacted and repaired.
>
> This same behaviour happens in similar screens where the source for a
combo
> box includes more than 1 table.
>
> Is this a peculiarity with SQL 2014?  I have successfully used this method
> with SQL 2008.
>
> Regards
>
> David Emerson
> Dalyn Software Ltd
> Wellington, New Zealand