Solved

Error Message in Pass-Thru Query

Posted on 2016-11-02
15
24 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 46

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 46

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

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 46

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
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: 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 46

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 46

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 46

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

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…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

920 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

13 Experts available now in Live!

Get 1:1 Help Now