Solved

Error Message in Pass-Thru Query

Posted on 2016-11-02
15
26 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 47

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 47

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

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 47

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 47

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 47

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 47

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Whats wrong in this query - Select * from tableA,tableA 11 28
Reference Controls on subforms 7 27
sql server tables from access 18 16
Return 0 on SQL count 24 28
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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.

785 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