Solved

Stored Procedure with Parameters from Pass-Thru Query in MS Access

Posted on 2016-11-01
11
37 Views
Last Modified: 2016-11-03
I am taking a stab at creating a stored procedure in SQL that is used with a Pass-Thru query from my front-end Access DB. I have multiple parameters that I am looking to pass to the stored procedure but I am getting errors about the data types, etc. I don't think I have those set properly and need a little help figuring them out. Below is the stored procedure. The first 2 parameters are dates (11/1/2016). The next 2 are text fields. And the final 2 are numbers (200.00) . I set them all to Varchar(50) thinking that would cover all the data types but it errors out on the date ones to start. Can someone help me set these to the correct data types?


ALTER PROCEDURE [dbo].[upMySprocHistory]
(
@FieldDateFrom Varchar(50) = NULL,
@FieldDateTo Varchar(50) = NULL,
@FieldCustNo Varchar(50) = NULL,
@FieldGroupCode Varchar(50) = NULL,
@FieldQtyMin Varchar(50) = NULL,
@FieldQtyMax Varchar(50) = NULL
)
AS
SELECT *
FROM viewHistoricalData v
WHERE ((@FieldDateFrom Is Null) Or (v.DateCreated)>= @FieldDateFrom) AND
      ((@FieldDateTo Is Null) Or (v.DateCreated)<= @FieldDateTo) AND
      ((@FieldCustNo Is Null) Or (v.CustNo)= @FieldCustNo) AND
	  ((@FieldGroupCode Is Null) Or (v.GroupCode)= @FieldGroupCode) AND
	  ((@FieldQtyMin Is Null) Or (v.Quantity)>= @FieldQtyMin) AND
	  ((@FieldQtyMax Is Null) Or (v.Quantity)<= @FieldQtyMax)

Open in new window


On the MS Access side I am using the following code on my form to pass the parameters from controls on my form. Then it will populate my listbox with the results. I also think the syntax of this is incorrect too because when I leave some of the controls blank it add ' ' in place of the null value so the '' don't line up with each blank parameter in the pass-thru query. Can someone help me set the SQL up to line up with the parameters if there are actual values and if there isn't any values for some of them then how do I pass those to the pass-thru query without passing just ' ' marks?

            strQry = "qryPTupMySprocHistory"
            strSQL = "Execute upMySprocHistory"
            strOldSQL = ChangeSQL(strQry, strSQL & "'" & _
                    Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtDateFrom] & "'" & _
                    Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtDateTo] & "'" & _
                    Forms![frmSalesOrderQuoteHistoryBrowseBeta]![cboCustomer] & "'" & _
                    Forms![frmSalesOrderQuoteHistoryBrowseBeta]![cboGroupCode] & "'" & _
                    Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtQtyMin] & "'" & _
                    Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtQtyMax] & "'") 'etc. for more parameters

Open in new window

0
Comment
Question by:Lawrence Salvucci
  • 6
  • 4
11 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
Comment Utility
Type the word NULL where you need NULLS, '' is a zero length string (or use the Access IsNull Function to do that (e.g. IsNull(Forms![frmSalesOrderQuoteHistoryBrowseBeta]![cboGroupCode] , "NULL")

Make sure that when you get that string that there's a comma between each parameter. What I find easiest is to build the string in VBA and then call the pass through query from VBA. That way you can test the string manually if needed.


kelvin
0
 
LVL 39

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 500 total points
Comment Utility
The easiest thing to do would be to change your sql:
@FieldDateFrom Varchar(50) = '',

WHERE ((@FieldDateFrom = '') Or (v.DateCreated)>= @FieldDateFrom) 

Open in new window


Note that you're open to sql injection using this way.
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
Comment Utility
So it would look like this?

            strOldSQL = ChangeSQL(strQry, strSQL & "'" & _
                    IsNull(Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtDateFrom], "NULL") & "'" & _
                    IsNull(Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtDateTo], "NULL") & "'" & _
                    IsNull(Forms![frmSalesOrderQuoteHistoryBrowseBeta]![cboCustomer], "NULL") & "'" & _
                    IsNull(Forms![frmSalesOrderQuoteHistoryBrowseBeta]![cboGroupCode], "NULL") & "'" & _
                    IsNull(Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtQtyMin], "NULL") & "'" & _
                    IsNull(Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtQtyMax], "NULL") & "'") 'etc. for more parameters

Open in new window

0
 
LVL 1

Author Comment

by:Lawrence Salvucci
Comment Utility
@Kyle,
So I would leave the SQL code in my VBA form the same and just remove the word Null from my SP?
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
Comment Utility
I change the sql in my SP to '' instead of using Null. Now when I run my SQL from access I get this build in my pass-thru query when only selecting the first 3 parameters. It doesn't look right to me. Then when I try to run it I get an error that says "Incorrect syntax near '11'. (#102) Unclosed quotation mark after the character string '100987'''


Execute upMySprocHistory'11/1/2014'11/1/2016'100987''''

Open in new window

0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 39

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 500 total points
Comment Utility
You need commas as well between each of the fields.

       strOldSQL = ChangeSQL(strQry, strSQL & "'" & _
                    IsNull(Forms![frmSalesOrderQuoteHistoryBrowseBeta]![txtDateFrom], "NULL") & "', " & _

Open in new window

0
 
LVL 1

Author Comment

by:Lawrence Salvucci
Comment Utility
I changed the SQL in my SP to show '' instead of using Null. Do I still need to use the IsNull in my VBA code or just add the commas?

If I just add the commas I still get an error with the syntax of the PT query. There are 3 other parameters that I left blank and it creates the PT query like this:

Execute upMySprocHistory '11/1/2016', 11/1/2016', 100987', ', ', '

Open in new window


How do I not pass through the parameters that should just be null?
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
Comment Utility
I am still getting another error saying "Converting data type varchar to numeric."
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
Comment Utility
You have to pass them all, just start them as blank.

The query should look like:

Execute upMySprocHistory '11/1/2016', '11/1/2016', '100987', '','', ''
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
Comment Utility
Just to test it out I put that in the query manually and I get that error message about converting data type varchar to numeric. Which leads me to the first part of my post about the datatype I have in my SP. Is that correct? If so then why am I getting this error?
0
 
LVL 39

Accepted Solution

by:
Kyle Abrahams earned 500 total points
Comment Utility
It's the blanks . . . they're causing an issue.


Going back to the original select statement, with a modification for using blanks from Access:

-- default parameters as blank.
ALTER PROCEDURE [dbo].[upMySprocHistory]
(
@FieldDateFrom Varchar(50) = '',
@FieldDateTo Varchar(50) = '',
@FieldCustNo Varchar(50) = '',
@FieldGroupCode Varchar(50) = '',
@FieldQtyMin Varchar(50) = '',
@FieldQtyMax Varchar(50) = ''
)
AS

-- convert variables back to nulls if blank.
select 
@FieldDateFrom = case when @FieldDateFrom   = '' then null else @FieldDateFrom end,
@FieldDateTo = case when @FieldDateTo = '' then null else @FieldDateTo end,
@FieldCustNo = case when @FieldCustNo = '' then null else @FieldCustNo end,
@FieldGroupCode = case when @FieldGroupCode = '' then null else @FieldGroupCode end,
@FieldQtyMin = case when @FieldQtyMin = '' then null else @FieldQtyMin end,
@FieldQtyMax = case when @FieldQtyMax = '' then null else @FieldQtyMax end


SELECT *
FROM viewHistoricalData v
WHERE ((@FieldDateFrom Is Null) Or (v.DateCreated)>= @FieldDateFrom) AND
      ((@FieldDateTo Is Null) Or (v.DateCreated)<= @FieldDateTo) AND
      ((@FieldCustNo Is Null) Or (v.CustNo)= @FieldCustNo) AND
	  ((@FieldGroupCode Is Null) Or (v.GroupCode)= @FieldGroupCode) AND
	  ((@FieldQtyMin Is Null) Or (v.Quantity)>= @FieldQtyMin) AND
	  ((@FieldQtyMax Is Null) Or (v.Quantity)<= @FieldQtyMax)

Open in new window

0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now