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
Solved

Error Message in Pass-Thru Query

Posted on 2016-11-02
15
28 Views
Last Modified: 2016-11-02
I have a pass thru query that is passing parameter to a stored procedure in SQL. When I try to run the query I am getting an error that says:

"ODBC - Call Failed. Error converting data type varchar to numeric. (#8114)

This is what my query looks like with the parameters manually entered into it. Normally they are put in the query from form controls but to make it simple to post here I just put the parameters in manually. Once I fire that query I get that error message.

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

Open in new window


Below is my stored procedure in SQL. Not sure if I have the data types set properly and which one is causing that error.

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

Open in new window

0
Comment
Question by:Lawrence Salvucci
  • 8
  • 7
15 Comments
 
LVL 48

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 500 total points
ID: 41870102
You'll need to pass the dates as 'YYYYMMDD':
Execute upMySprocHistory '20161101', '20161101', '100987','','',''

Open in new window

0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41870128
I changed it to the correct format for the dates but I am still getting the same error message.
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41870133
Just for sanity check, if you provide values for FieldQtyMin and FieldQtyMax the error still occur?
0
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.

 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41870136
Yes. I just put values in for the final 3 parameters and I still get the error message.

Execute upMySprocHistory '20161101', '20161001', '100987','465','50','500'

Open in new window

0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41870144
Ok. It might be a problem with my environment because I don't have any error.
Anyway, why do you have quantities as varchar and not as numeric?
Is v.Quantity varchar as well?
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41870148
I just set them all up as varchar. They should actually be numeric. I will change those to numeric. let me change those to numeric and try it again. Did it work for you with all the data types as varchar?
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41870152
Yes but I'm working only with variables and not with tables or views so this may justify the different behavior.
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41870155
I changed those to numeric and I am still getting that error message. Is there a way to see which parameter is failing?


ALTER PROCEDURE [dbo].[upMySprocHistory]
(
@FieldDateFrom Varchar(50) = '',
@FieldDateTo Varchar(50) = '',
@FieldCustNo numeric(6,0) = '',
@FieldGroupCode Varchar(50) = '',
@FieldQtyMin numeric = '',
@FieldQtyMax numeric = ''
)
AS
SELECT *
FROM viewHistoricalData v
WHERE ((@FieldDateFrom = '') Or (v.DateCreated)>= @FieldDateFrom) AND
      ((@FieldDateTo = '') Or (v.DateCreated)<= @FieldDateTo) AND
      ((@FieldCustNo = '') Or Convert(numeric(6,0),(v.CustNo))= @FieldCustNo) AND
	  ((@FieldGroupCode = '') Or (v.GroupCode)= @FieldGroupCode) AND
	  ((@FieldQtyMin = '') Or Convert(numeric(18,0),(v.Quantity))>= @FieldQtyMin) AND
	  ((@FieldQtyMax = '') Or Convert(numeric(18,0),(v.Quantity))<= @FieldQtyMax)

Open in new window

0
 
LVL 48

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 41870159
Numeric can't be ''. Try NULL instead. And do you allow decimal values for quantities?

FieldQtyMin numeric(6,0) = NULL
FieldQtyMax numeric(6,0) = NULL
(...)
SELECT
(...)
((@FieldQtyMin IS NULL) Or Convert(numeric(18,0),(v.Quantity))>= @FieldQtyMin) AND
  ((@FieldQtyMax IS NULL) Or Convert(numeric(18,0),(v.Quantity))<= @FieldQtyMax)
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41870176
Yes we do allow for decimals. I forgot about that since the majority of the time it's whole numbers.
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41870179
It's working now. I guess changing those to NULL did the trick.
0
 
LVL 48

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 500 total points
ID: 41870180
Yes, just realized now that you had the same mistake for the FieldCustNo:
@FieldCustNo numeric(6,0) = ''

Did you changed it also to NULL? @FieldCustNo numeric(6,0) = NULL
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41870195
Yes I changed that one as well. That field doesn't have any decimals. If I change the other 2 parameters to numeric(18,2) it will always have to have 2 decimals in the value, correct?
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41870200
Correct.
0
 
LVL 1

Author Closing Comment

by:Lawrence Salvucci
ID: 41870208
Thanks for all the help! I greatly appreciate it!
0

Featured Post

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.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

839 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