Solved

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

Posted on 2016-11-01
11
54 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
ID: 41869025
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 40

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 500 total points
ID: 41869034
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
ID: 41869039
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41869040
@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
ID: 41869052
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
 
LVL 40

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 500 total points
ID: 41869103
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
ID: 41869112
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
ID: 41869127
I am still getting another error saying "Converting data type varchar to numeric."
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 41869217
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
ID: 41869337
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 40

Accepted Solution

by:
Kyle Abrahams earned 500 total points
ID: 41871250
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

821 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