Solved

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

Posted on 2016-11-01
11
62 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 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

734 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