?
Solved

Error Message in Pass-Thru Query

Posted on 2016-11-02
15
Medium Priority
?
34 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
[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
  • 8
  • 7
15 Comments
 
LVL 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 2000 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 51

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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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 51

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 51

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 51

Accepted Solution

by:
Vitor Montalvão earned 2000 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 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 2000 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 51

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

764 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