Filtering records retrieved from SQL server (1 Viewer)

NigelBishop

New member
Local time
Today, 03:32
Joined
Oct 4, 2019
Messages
14
Hi I'm hoping one of you lovely people will be able to assist.
I am migrating an Access backend to SQL server and have created a stored procedure for retrieving records.
Code:
[FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]USE[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] [IPTTracking]
[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]GO
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#008000][FONT=Consolas][SIZE=2][COLOR=#008000][FONT=Consolas][SIZE=2][COLOR=#008000]/****** Object: StoredProcedure [Issues].[sp_Select_tabled] Script Date: 04/10/2019 10:40:52 ******/
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]SET[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]ANSI_NULLS[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]ON
GO
SET[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]QUOTED_IDENTIFIER[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]ON
GO
ALTER[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]PROCEDURE[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] [Issues][/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2][sp_Select_tabled][/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff] [/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]@ref [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]Nvarchar[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]20[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080]))[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]AS
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2][/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]BEGIN
declare[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] @sql [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]Nvarchar[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]4000[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080])
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]begin[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]transaction
[INDENT]set[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] @sql [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#ff0000][FONT=Consolas][SIZE=2][COLOR=#ff0000][FONT=Consolas][SIZE=2][COLOR=#ff0000]N'select i.ID
, i.Reference
, i.AssignedOwner
from issue.T_IPTTrackerIssues as I
where dc.IPTGroup = @ref'
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2][/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]exec[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#800000][FONT=Consolas][SIZE=2][COLOR=#800000][FONT=Consolas][SIZE=2][COLOR=#800000]sp_executesql[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff] [/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]@sql[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#ff0000][FONT=Consolas][SIZE=2][COLOR=#ff0000][FONT=Consolas][SIZE=2][COLOR=#ff0000]N'@Ref Nvarchar(20)'[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]@ref
[/INDENT][/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]END
[COLOR=black]
[/COLOR]
I've entered the code below into the On Load event of my continuous form and I'm able to return the correct rows into the form based on the value entered into the string.

Code:
[/COLOR]
[INDENT]Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open "Provider=SQLOLEDB.1;Initial Catalog=IPTTracking;Data Source=PHE-3J021434H;Integrated Security=SSPI;"
 
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
 
rs.CursorLocation = adUseClient
rs.Open "EXEC issues.sp_Select_tabled '[B]QC[/B]'", conn
Set Me.Recordset = rs
[/INDENT]
So far so good.

I need to pass a variable into the EXEC string and have altered the code as below and once the code works I'll change the variable to refer to a dropdown field
Code:
[/COLOR]
[INDENT][COLOR=black]Dim conn As ADODB.Connection
[COLOR=red][B]Dim strArea As String[/B][/COLOR]
Set conn = New ADODB.Connection
[/COLOR][COLOR=red][B]strArea = "QC"[/B][/COLOR]
[COLOR=black]conn.Open "Provider=SQLOLEDB.1;Initial Catalog=IPTTracking;Data Source=PHE-3J021434H;Integrated Security=SSPI;"[/COLOR]
[COLOR=#000000][/COLOR] 
[COLOR=black]Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset[/COLOR]
[COLOR=#000000][/COLOR] 
[COLOR=black]rs.CursorLocation = adUseClient[/COLOR]
[COLOR=black]rs.Open "EXEC issues.sp_Select_tabled [COLOR=red][B]strArea[/B][/COLOR]", conn[/COLOR]
[COLOR=black]Set Me.Recordset = rs[/COLOR]
[/INDENT]
Unfortunately when I open the form this change causes no records to be returned, I'm guessing it is probably a syntax error in the
rs.Open "EXEC issues.sp_Select_tabled strArea", conn string but no matter what I try I'm unable to get it to work.
Any help would be gratefully received.

I know that I could probably use a passthrough query to achieve the same goal however the long term plan is to go for a web based interface at some point so I'd prefer to take this approach.

Thank you in advance
[/SIZE][/FONT]
[/SIZE][/FONT][/COLOR][/SIZE][/FONT]
 

Minty

AWF VIP
Local time
Today, 10:32
Joined
Jul 26, 2013
Messages
10,353
You need to concatenate your variable into the Exec statement ;

Code:
rs.Open "EXEC issues.sp_Select_tabled '" & strArea & "'", conn

If you built the exec string into a variable you would have seen it ;

Code:
sExec = "EXEC issues.sp_Select_tabled '" & strArea & "'"
Debug.Print sExec

rs.Open sExec , conn
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 21:32
Joined
Jan 20, 2009
Messages
12,849
Using sp_executesql really isn't the right direction to be heading with your stored procedure.
 

NigelBishop

New member
Local time
Today, 03:32
Joined
Oct 4, 2019
Messages
14
instead of sp_executesql what would you recommend?, as you might have guessed i'm new to this game
 

NigelBishop

New member
Local time
Today, 03:32
Joined
Oct 4, 2019
Messages
14
A bit cheeky I know how do I make the change from
strArea = "QA"
to
strArea = [Forms]![F_IPTTrackerIssues1]!
  • ?
 

Minty

AWF VIP
Local time
Today, 10:32
Joined
Jul 26, 2013
Messages
10,353
Actually, in addition to Galaxioms statement, I'm unsure why you would be using a transaction for a simple select?

Code:
USE [IPTTracking]
GO
/****** Object: StoredProcedure [Issues].[sp_Select_tabled] Script Date: 04/10/2019 10:40:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [Issues].[sp_Select_tabled]
@ref Nvarchar(20)
AS

SET NOCOUNT ON

BEGIN

SELECT i.ID
, i.Reference
, i.AssignedOwner
from issue.T_IPTTrackerIssues as I
where dc.IPTGroup = @ref

END
 

Minty

AWF VIP
Local time
Today, 10:32
Joined
Jul 26, 2013
Messages
10,353
And to answer your second question, assuming you are on the form you are running this from;

Code:
Dim conn As ADODB.Connection
Dim strArea As String
Dim sExec as String

Set conn = New ADODB.Connection

[COLOR="Red"]strArea = Me.List[/COLOR]

conn.Open "Provider=SQLOLEDB.1;Initial Catalog=IPTTracking;Data Source=PHE-3J021434H;Integrated Security=SSPI;"
 
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
 
rs.CursorLocation = adUseClient
sExec = "EXEC issues.sp_Select_tabled '" & strArea & "'"
Debug.Print sExec

rs.Open sExec , conn
Set Me.Recordset = rs
 

NigelBishop

New member
Local time
Today, 03:32
Joined
Oct 4, 2019
Messages
14
Thank you all for your help, it all makes perfect sense now.
 

Users who are viewing this thread

Top Bottom