[dba-SQLServer] Combo Boxes not showing Recordset

[Paul Hartland]

Well done been thinking about this every so often

On Thu, 15 Aug 2019, 22:17 David Emerson, <newsgrps at dalyn.co.nz> wrote:

> 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
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>