We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Stored Procedure Not Working in VB

Medium Priority
255 Views
Last Modified: 2012-05-11
Hi Experts,

I have the following sp that works fine executed on SSMS. All it does is retrun all the sales the day before by SKU code.

USE [DB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sl_dailyItemSales]
AS
BEGIN
	SET NOCOUNT ON;
	SELECT		TOP (100) ct.SKU, SUM(OrdLines.olProdQty) AS Qty, SUM(OrdLines.olProdPrice) AS Revenue, ct.CartTitle
	FROM		Orders o
	INNER JOIN	OrdLines ON o.oID = OrdLines.oID 
	INNER JOIN	Cartridges ct ON OrdLines.olProdCode = ct.CartID
	WHERE		(CAST(CONVERT(varchar, o.oDate, 101) AS datetime) = CONVERT(DATETIME, dateadd(day, 0, datediff(day, 0, dateadd(day, -1, getdate()))), 101))
	GROUP BY	ct.SKU, ct.CartTitle
	ORDER BY	ct.SKU
END

Open in new window


The data is supposed to be formatted and sent by email using some VB (classic asp I'm afraid) code saved as a .vbs file that will be fired by a scheduled task. See snippet:

Dim oConn: Set oConn = Createobject("ADODB.Connection")

With oConn
	.ConnectionTimeout = 3600
	.ConnectionString =	"Provider=SQLNCLI10.1;Data Source=localhost;Catalog=DB;User Id=un; Password=pw;"
	.Open
End With
				
		set rstMx = Createobject("adodb.recordset")
		set rstMx = oConn.Execute("sl_dailyItemSales")  ' <<< fails here

		If Not rstMx.EOF Then
			Do While Not rstMx.EOF 
				ArrSales = rstMx.GetRows()
			Loop
			Const arrSKU = 0
			Const arrVOL = 1
			Const arrVAL = 2
			Const arrCN = 3
		End If

Open in new window


The error I get is:


ERROR: The conversion of a varchar data type to a datatime data type resulted in an out of range value.
CODE: 80040E07

So what the hell am I doing wrong here? Totally baffled!

Any help would be really appreciated.

Regs,
Numb


Comment
Watch Question

CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Fantastic! Did the job. Thanks so much...
CERTIFIED EXPERT

Commented:
Or better even change it to:
WHERE (o.oDate >= CONVERT(datetime, CONVERT(varchar, getdate()-1, 101),101) and o.oDate < CONVERT(datetime, CONVERT(varchar, getdate(), 101),101))

Open in new window

So an index (if present) can be used on the o.oDate column.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.