Solved

Application uses a value of the wrong type for the current operation

Posted on 2012-04-13
15
1,581 Views
Last Modified: 2012-04-13
Hello Everyone,

Quick question regarding 'types' specifically datetypes in VBScript statements that call MS SQL Stored Procedures..

I have a vbscript statement that calls an SP, the statement reads --

set CMDProductSales = Server.CreateObject("ADODB.Command")
CMDProductSales.ActiveConnection = MM_Connection_STRING
CMDProductSales.CommandText = "dbo.usp_DirectProductQuantitySales"
CMDProductSales.CommandType = 4
CMDProductSales.CommandTimeout = 0
CMDProductSales.Prepared = true
CMDProductSales.Parameters.Append CMDProductSales.CreateParameter("@RETURN_VALUE", 3, 4)
CMDProductSales.Parameters.Append CMDProductSales.CreateParameter("@BID", 3, 1,4,CMDProductSales__BID)
CMDProductSales.Parameters.Append CMDProductSales.CreateParameter("@FDate", 135, 1,14,CMDProductSales__FDate)
CMDProductSales.Parameters.Append CMDProductSales.CreateParameter("@TDate", 135, 1,14,CMDProductSales__TDate)
set RSResults = CMDProductSales.Execute
RSResults_numRows = 0

Open in new window


My query is surrounding the type and length settings for @FDate & @TDate.

These are datetime variables..

FYI, my SP reads --

Create Procedure [dbo].[usp_DirectProductQuantitySales]
@BID INT,
@FDate datetime,
@TDate datetime
AS
BEGIN
IF @BID <> ''
BEGIN
select O.ProductID, 
REPLACE(REPLACE(P.[Product-Name],'&#40;','('),'&#41;',')') 
ProductName, 
P.SKUCode, 
sum(O.Quantity) Quantity 
from dbo.OrderDetail O 
inner join dbo.[Direct-Product] P ON P.ID = O.ProductID 
inner join dbo.Ordertb D on D.OrderID = O.OrderID 
Where D.BatchID = @BID 
group by O.ProductID, REPLACE(REPLACE(P.[Product-Name],'&#40;','('),'&#41;',')'),  P.SKUCode  order by O.ProductID
END
IF @FDate <> '' AND @TDate <> ''
BEGIN
select O.ProductID, 
REPLACE(REPLACE(P.[Product-Name],'&#40;','('),'&#41;',')') 
ProductName, 
P.SKUCode, 
sum(O.Quantity) Quantity 
from dbo.OrderDetail O 
inner join dbo.[Direct-Product] P ON P.ID = O.ProductID 
inner join dbo.Ordertb D on D.OrderID = O.OrderID 
Where D.OrderDate BETWEEN @FDate AND @TDate
group by O.ProductID, REPLACE(REPLACE(P.[Product-Name],'&#40;','('),'&#41;',')'),  P.SKUCode  order by O.ProductID
END
End

Open in new window


"Currently when i try ruinning the ASP page, I get --
ADODB.Command error '800a0d5d'
Application uses a value of the wrong type for the current operation.
/test5.asp, line 25"

Where line 25 is -

CMDProductSales.Parameters.Append CMDProductSales.CreateParameter("@FDate", 135, 1,14,CMDProductSales__FDate)


Many thanks
0
Comment
Question by:garethtnash
  • 8
  • 7
15 Comments
 
LVL 10

Expert Comment

by:plummet
ID: 37841823
Hi,

What is the datatype of your variables CMDProductSales__FDate and CMDProductSales__TDate? Are they date variables that include a time portion, because datatypeenum of 135 is a date/time type. Can you give us some more information?

Thanks
John
0
 

Author Comment

by:garethtnash
ID: 37841836
Hi John,

Do you mean the declaration of the variables?

Dim CMDProductSales__FDate
CMDProductSales__FDate = ""
if(Request("FDate") <> "") then CMDProductSales__FDate = Request("FDate")

Dim CMDProductSales__TDate
CMDProductSales__TDate = ""
if(Request("TDate") <> "") then CMDProductSales__TDate = Request("TDate")

Open in new window


So the entire script reads --

<%

Dim CMDProductSales__BID
CMDProductSales__BID = ""
if(Request("BID") <> "") then CMDProductSales__BID = Request("BID")

Dim CMDProductSales__FDate
CMDProductSales__FDate = ""
if(Request("FDate") <> "") then CMDProductSales__FDate = Request("FDate")

Dim CMDProductSales__TDate
CMDProductSales__TDate = ""
if(Request("TDate") <> "") then CMDProductSales__TDate = Request("TDate")


set CMDProductSales = Server.CreateObject("ADODB.Command")
CMDProductSales.ActiveConnection = MM_Connection_STRING
CMDProductSales.CommandText = "dbo.usp_DirectProductQuantitySales"
CMDProductSales.CommandType = 4
CMDProductSales.CommandTimeout = 0
CMDProductSales.Prepared = true
CMDProductSales.Parameters.Append CMDProductSales.CreateParameter("@RETURN_VALUE", 3, 4)
CMDProductSales.Parameters.Append CMDProductSales.CreateParameter("@BID", 3, 1,4,CMDProductSales__BID)
CMDProductSales.Parameters.Append CMDProductSales.CreateParameter("@FDate", 135, 1,14,CMDProductSales__FDate)
CMDProductSales.Parameters.Append CMDProductSales.CreateParameter("@TDate", 135, 1,14,CMDProductSales__TDate)
set RSResults = CMDProductSales.Execute
RSResults_numRows = 0

Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "attachment; filename=Competition_Entries.xls" 
 
if NOT RSResults.EOF then
dim arrResults
arrResults = RSResults.GetRows()
End if
%>

Open in new window


Does that help?

Alternatively, if you are asking about the column that the database is querying, -

[OrderDate] [datetime]

Hope that helps?

When i ran the page, I only send a BID= value..

if you can see the SP it returns one of two potential select statements, one if BID <> "" and a different one if FDate AND TDate <> ""

Appreciate your help with this TY
0
 
LVL 10

Expert Comment

by:plummet
ID: 37841845
It would be useful to see the contents of CMDProductSales__FDate and CMDProductSales__TDate before the error, can you do a response.write of them (you might need to remark out the error lines to get to see the resulting text)

I think that's the first thing to check. You may need to add a cdate, or change the assignment to something like:

if(isdate(Request("FDate")) then CMDProductSales__FDate = Request("FDate")

But if we can see the value that's being passed that's the first step.

Cheers
John
0
 

Author Comment

by:garethtnash
ID: 37841867
Hi,

So I have changed the code so that it reads -

<%

Dim CMDProductSales__BID
CMDProductSales__BID = ""
if(Request("BID") <> "") then CMDProductSales__BID = Request("BID")

Dim CMDProductSales__FDate
CMDProductSales__FDate = ""
if(isdate(Request("FDate"))) then CMDProductSales__FDate = Request("FDate")

Dim CMDProductSales__TDate
CMDProductSales__TDate = ""
if(isdate(Request("TDate"))) then CMDProductSales__TDate = Request("TDate")


set CMDProductSales = Server.CreateObject("ADODB.Command")
CMDProductSales.ActiveConnection = MM_Connection_STRING
CMDProductSales.CommandText = "dbo.usp_DirectProductQuantitySales"
CMDProductSales.CommandType = 4
CMDProductSales.CommandTimeout = 0
CMDProductSales.Prepared = true
CMDProductSales.Parameters.Append CMDProductSales.CreateParameter("@RETURN_VALUE", 3, 4)
CMDProductSales.Parameters.Append CMDProductSales.CreateParameter("@BID", 3, 1,4,CMDProductSales__BID)
CMDProductSales.Parameters.Append CMDProductSales.CreateParameter("@FDate", 135, 1,14,CMDProductSales__FDate)
CMDProductSales.Parameters.Append CMDProductSales.CreateParameter("@TDate", 135, 1,14,CMDProductSales__TDate)
set RSResults = CMDProductSales.Execute
RSResults_numRows = 0

Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "attachment; filename=Competition_Entries.xls" 
 
if NOT RSResults.EOF then
dim arrResults
arrResults = RSResults.GetRows()
End if
%>

Open in new window


CMDProductSales__FDate and CMDProductSales__TDate  are both empty, as my querystring reads -

test5.asp?BID=2

So in this instance I only want the first select statement in the SP to run.....

But I'm still getting --

"ADODB.Command error '800a0d5d'
Application uses a value of the wrong type for the current operation.
/test5.asp, line 25"

Thanks
0
 
LVL 10

Expert Comment

by:plummet
ID: 37841884
Because the store proc doesn't have any defaults for the dates you're either going to have to specify them in the sp or the code. It will be easier to do that in code, but you'll have to work out what dates you need, here's an example:

Dim CMDProductSales__BID
CMDProductSales__BID = ""
if(Request("BID") <> "") then CMDProductSales__BID = Request("BID")

Dim CMDProductSales__FDate
CMDProductSales__FDate = ""
if(isdate(Request("FDate"))) then CMDProductSales__FDate = Request("FDate")

if not isdate(CMDProductSales__FDate) then CMDProductSales__FDate=cdate("01 Jan 2012 00:00:00")

Dim CMDProductSales__TDate
CMDProductSales__TDate = ""
if(isdate(Request("TDate"))) then CMDProductSales__TDate = Request("TDate")

if not isdate(CMDProductSales__TDate) then CMDProductSales__TDate=cdate("31 Dec 2012 23:59:59")

Open in new window


I've given it a from date of 1/1/2012 and a to date of 31/12/2012, you'd have to change that to encompass all the data you want returned, but you'll see what I mean.
0
 

Author Comment

by:garethtnash
ID: 37842144
Do I need to change the SP in that case?

As I only want to return one select statement?

IF @BID <> ''
BEGIN
select O.ProductID, 
REPLACE(REPLACE(P.[Product-Name],'&#40;','('),'&#41;',')') 
ProductName, 
P.SKUCode, 
sum(O.Quantity) Quantity 
from dbo.OrderDetail O 
inner join dbo.[Direct-Product] P ON P.ID = O.ProductID 
inner join dbo.Ordertb D on D.OrderID = O.OrderID 
Where D.BatchID = @BID 
group by O.ProductID, REPLACE(REPLACE(P.[Product-Name],'&#40;','('),'&#41;',')'),  P.SKUCode  order by O.ProductID
END
IF @FDate <> '' AND @TDate <> ''
BEGIN
select O.ProductID, 
REPLACE(REPLACE(P.[Product-Name],'&#40;','('),'&#41;',')') 
ProductName, 
P.SKUCode, 
sum(O.Quantity) Quantity 
from dbo.OrderDetail O 
inner join dbo.[Direct-Product] P ON P.ID = O.ProductID 
inner join dbo.Ordertb D on D.OrderID = O.OrderID 
Where D.OrderDate BETWEEN @FDate AND @TDate
group by O.ProductID, REPLACE(REPLACE(P.[Product-Name],'&#40;','('),'&#41;',')'),  P.SKUCode  order by O.ProductID
END

Open in new window


Should there be an ELSE in there?

And would I need to change the SP to read

ELSE IF @FDate <> DEFAULTVALUE AND @TDate <> DEFAULTVALUE

??

Thanks
0
 
LVL 10

Expert Comment

by:plummet
ID: 37842230
You don't need to change the SP if you use my example, but if you do you can either specify the dates or give them a default value ie

Create Procedure [dbo].[usp_DirectProductQuantitySales]
@BID INT= 0,
@FDate datetime = null,
@TDate datetime = null
AS
BEGIN

Open in new window


Or you could give them a default value, eg:

Create Procedure [dbo].[usp_DirectProductQuantitySales]
@BID INT = 0,
@FDate datetime = '20010101',
@TDate datetime = '21000101'
AS
BEGIN

Open in new window


Then you can check for the default values in the SP, or even just use them as they are. If there are no dates to pass then don't do the createparameter for them, and the SP should use the default values.

You can then create some dynamic SQL, or check for the defaults in code like you're doing:

IF @BID <> 0
BEGIN
	select 
		O.ProductID, 
		REPLACE(REPLACE(P.[Product-Name],'&#40;','('),'&#41;',')') ProductName, 
		P.SKUCode, 
		sum(O.Quantity) Quantity 
		from dbo.OrderDetail O 
			inner join dbo.[Direct-Product] P ON P.ID = O.ProductID 
			inner join dbo.Ordertb D on D.OrderID = O.OrderID 
		Where D.BatchID = @BID 
		group by 
			O.ProductID, 
			REPLACE(REPLACE(P.[Product-Name],'&#40;','('),'&#41;',')'),  
			P.SKUCode  
		order by O.ProductID
END
ELSE
	IF @FDate <> '20010101' AND @TDate <> '21000101'
	BEGIN
		select O.ProductID, 
		REPLACE(REPLACE(P.[Product-Name],'&#40;','('),'&#41;',')') ProductName, 
		P.SKUCode, 
		sum(O.Quantity) Quantity 
		from dbo.OrderDetail O 
			inner join dbo.[Direct-Product] P ON P.ID = O.ProductID 
			inner join dbo.Ordertb D on D.OrderID = O.OrderID 
		Where D.OrderDate BETWEEN @FDate AND @TDate
		group by 
			O.ProductID, 
			REPLACE(REPLACE(P.[Product-Name],'&#40;','('),'&#41;',')'),  
			P.SKUCode  
		order by O.ProductID
	END

Open in new window


Something like that, obv I can't test it here...
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:garethtnash
ID: 37842586
Thanks John, Like it, thank you.

I've changed the SP to read like --

"Create Procedure [dbo].[usp_DirectProductQuantitySales]
@BID INT = 0,
@FDate datetime,
@TDate datetime
AS
IF @FDate is null
SET @FDate = dateadd(d, -10000, getdate())
IF @TDate is null
SET @TDate = getdate()
BEGIN
IF @BID <> 0"

I've also edited your code from 37841884 above ever so slightly, so that it reads --

Dim CMDProductSales__FDate
CMDProductSales__FDate = ""
if(isdate(Request("FDate"))) then CMDProductSales__FDate = Request("FDate")
if not isdate(CMDProductSales__FDate) then CMDProductSales__FDate= DateAdd("d", -10000, Now())

Dim CMDProductSales__TDate
CMDProductSales__TDate = ""
if(isdate(Request("TDate"))) then CMDProductSales__TDate = Request("TDate")
if not isdate(CMDProductSales__TDate) then CMDProductSales__TDate= DateAdd("d", -1, Now())

Open in new window


Can I just check one more thing with you, just for clarity please..

"When i try calling the page sending either just BID, or nothing I get
Microsoft OLE DB Provider for SQL Server error '80040e07'
Error converting data type varchar to datetime.
/test5.asp, line 29"

Where 29 is --

set RSResults = CMDProductSales.Execute

If I remove the lines

if not isdate(CMDProductSales__FDate) then CMDProductSales__FDate= DateAdd("d", -10000, Now())

AND

if not isdate(CMDProductSales__TDate) then CMDProductSales__TDate= DateAdd("d", -1, Now())

it works fine if BID is sent...

but nothing if nothing is sent?

Sorry if I'm being a pain..

Thanks
0
 
LVL 10

Expert Comment

by:plummet
ID: 37842764
Hi Gareth

No problem. If you remove those 2 lines, and don't send a BID it should be sending those default dates...can't see why it would be sending a varchar...can you post the section of code where you set the parameters and call the SP again, just in case there's something amiss there?
0
 

Author Comment

by:garethtnash
ID: 37842786
Sure John,

Here you go, and once again, thank you.

<%

Dim CMDProductSales__BID
CMDProductSales__BID = 0
if(Request("BID") <> "") then CMDProductSales__BID = Request("BID")

Dim CMDProductSales__FDate
CMDProductSales__FDate = ""
if(isdate(Request("FDate"))) then CMDProductSales__FDate = Request("FDate")
if not isdate(CMDProductSales__FDate) then CMDProductSales__FDate= DateAdd("d", -10000, Now())

Dim CMDProductSales__TDate
CMDProductSales__TDate = ""
if(isdate(Request("TDate"))) then CMDProductSales__TDate = Request("TDate")
if not isdate(CMDProductSales__TDate) then CMDProductSales__TDate= DateAdd("d", -1, Now())


set CMDProductSales = Server.CreateObject("ADODB.Command")
CMDProductSales.ActiveConnection = MM_Connection_STRING
CMDProductSales.CommandText = "dbo.usp_DirectProductQuantitySales"
CMDProductSales.CommandType = 4
CMDProductSales.CommandTimeout = 0
CMDProductSales.Prepared = true
CMDProductSales.Parameters.Append CMDProductSales.CreateParameter("@RETURN_VALUE", 3, 4)
CMDProductSales.Parameters.Append CMDProductSales.CreateParameter("@BID", 3, 1,4,CMDProductSales__BID)
CMDProductSales.Parameters.Append CMDProductSales.CreateParameter("@FDate", 200, 1,50,CMDProductSales__FDate)
CMDProductSales.Parameters.Append CMDProductSales.CreateParameter("@TDate", 200, 1,50,CMDProductSales__TDate)
set RSResults = CMDProductSales.Execute
RSResults_numRows = 0

Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "attachment; filename=Competition_Entries.xls" 
 
if NOT RSResults.EOF then
dim arrResults
arrResults = RSResults.GetRows()
End if
%>

Open in new window


Thanks
0
 

Author Comment

by:garethtnash
ID: 37842799
And SP Now reads --

Create Procedure [dbo].[usp_DirectProductQuantitySales]
@BID INT = 0,
@FDate datetime,
@TDate datetime
AS
IF @FDate is null 
SET @FDate = dateadd(d, -10000, getdate())
IF @TDate is null 
SET @TDate = getdate()
BEGIN
IF @BID <> 0
BEGIN
select O.ProductID, 
REPLACE(REPLACE(P.[Product-Name],'&#40;','('),'&#41;',')') 
ProductName, 
P.SKUCode, 
sum(O.Quantity) Quantity 
from dbo.OrderDetail O 
inner join dbo.[Direct-Product] P ON P.ID = O.ProductID 
inner join dbo.Ordertb D on D.OrderID = O.OrderID 
Where D.BatchID = @BID 
group by O.ProductID, REPLACE(REPLACE(P.[Product-Name],'&#40;','('),'&#41;',')'),  P.SKUCode  order by O.ProductID
END
Else IF @FDate <> '' AND @TDate <> ''
BEGIN
select O.ProductID, 
REPLACE(REPLACE(P.[Product-Name],'&#40;','('),'&#41;',')') 
ProductName, 
P.SKUCode, 
sum(O.Quantity) Quantity 
from dbo.OrderDetail O 
inner join dbo.[Direct-Product] P ON P.ID = O.ProductID 
inner join dbo.Ordertb D on D.OrderID = O.OrderID 
Where D.OrderDate BETWEEN @FDate AND @TDate
group by O.ProductID, REPLACE(REPLACE(P.[Product-Name],'&#40;','('),'&#41;',')'),  P.SKUCode  order by O.ProductID
END
End
GO

Open in new window


Although in both cases 01.01.1900 as the FDate might be better than 10,000 days ago?


TY
0
 
LVL 10

Accepted Solution

by:
plummet earned 500 total points
ID: 37842812
Hi Gareth

I think the problem is that you've changed the datatype enum in the createparameter method to 200 (adVarChar) when it was 135 (adDBTimeStamp). I reckon it should probably be 133 (adDBDate), give that a spin.

Fingers crossed!
0
 

Author Closing Comment

by:garethtnash
ID: 37843011
Thank you so much John,

That is fantastic, thank you, thank you, thank you.

Have a great weekend

:)

incidently, what is 133 (& where can i finf these codes?) Thanks
0
 
LVL 10

Expert Comment

by:plummet
ID: 37843055
I'm glad it worked!

The codes are all listed here, I like the W3Schools website: http://www.w3schools.com/ado/met_comm_createparameter.asp

Have a good weekend!

John
0
 

Author Comment

by:garethtnash
ID: 37843069
Thanks John
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video discusses moving either the default database or any database to a new volume.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

747 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