?
Solved

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

Posted on 2016-11-01
11
Medium Priority
?
79 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
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 2000 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 2000 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

719 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