Overflow error using Stored Procedure 1

[RonRepp]

Hi all:

My app is using stored procedures to insert data into tables. This works fine when running through Query Analyzer, but I get an overflow error when running it through code.

[code>>VB]


Public Sub InsertCCDecline()
Dim Con As ADODB.Connection
Dim SQL As String
Set Con = New ADODB.Connection
LastRunDate = GetLastRunDate(1)
StartDate = DateAdd("s", 1, LastRunDate)
On Error GoTo AfterErr
''Update Last Run Date
UpdateLastRunDate 1, ThisRunDate

SQL = "SP_InsertCC_Decline '" & ClientID & "', '" & StartDate & "', '" & ThisRunDate & "'"

Con.ConnectionString = GetConstring("3Source")

Con.Open

Con.Execute (SQL)

Con.Close
Set Con = Nothing
Exit Sub

AfterErr:
frmAdmin.TrayIcon1.FlashEnabled = False
LogError OrderID, Err.Number, Err.Description, EM_TypeID
Exit Sub

End Sub
[/code]

[code>>Stored Procedure]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_InsertCC_Decline]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_InsertCC_Decline]
GO

CREATE PROC SP_InsertCC_Decline
(
@ClientID NVarChar(50),
@StartDate DATETIME,
@EndDate DATETIME
)

AS

SET NOCOUNT ON

INSERT INTO EM_CC_Decline

SELECT TOP 100 PERCENT
O.OrderID,
O.OrderDate,
O.OrderTime,
SRC.DNIS,
OD.ProductID,
P.ProductName,
P.Description,
OD.Quantity,
OD.UnitPrice,
OD.Discount,
OD.Shipping,
OD.Tax,
CurStat.priorStatusId,
CurStat.currentStatusId,
CurStat.priorStatusCode,
CurStat.currentStatusCode,
CurStat.priorStatusName,
CurStat.currentStatusName,
C.ClientID,
C.Client,
O.ShipFirstName AS sFName,
O.ShipLastName AS sLName,
O.ShipAddress AS sAddress1,
O.ShipAddress2 AS sAddress2,
O.ShipCity AS sCity,
O.ShipStateOrProvince AS sState,
O.ShipPostalCode AS sZIP,
O.ShipCountry AS sCountry,
O.WebOrderNumber,
O.AdKey, O.RushOrder,
Cust.ContactFirstName AS bFName,
Cust.ContactLastName AS bLName,
Cust.BillingAddress AS bAddress1,
Cust.Address2 AS bAddress2,
Cust.City AS bCity,
Cust.StateOrProvince AS bState,
Cust.PostalCode AS bZIP,
Cust.Country AS bCountry,
Cust.EmailAddress AS bEMail,
CurStat.stamp,
PM.PaymentMethod

FROM

report_weboms.dbo.tblOrderItemStatusUpdate CurStat INNER JOIN
report_weboms.dbo.[Order Details] OD ON CurStat.orderDetailId = OD.OrderDetailID INNER JOIN
report_weboms.dbrders O ON OD.OrderID = O.OrderID INNER JOIN
report_weboms.dbo.tblSource SRC ON O.SourceID = SRC.SourceID INNER JOIN
report_weboms.dbo.tblClient C ON SRC.ClientID = C.ClientID INNER JOIN
report_weboms.dbo.Payments PAY ON O.OrderID = PAY.OrderID INNER JOIN
report_weboms.dbo.Customers Cust ON O.CustomerID = Cust.CustomerID INNER JOIN
report_weboms.dbo.[Payment Methods] PM ON PAY.PaymentMethodID = PM.PaymentMethodID INNER JOIN
report_weboms.dbo.tblProducts P ON OD.ProductID = P.ProductID
WHERE
(CurStat.Stamp BETWEEN @StartDate AND @EndDate) AND
(CurStat.currentStatusCode = '15') AND
(C.ClientID = @ClientID) AND
(NOT (PAY.PaymentMethodID IN (1, 6, 7, 8, 9)))


ORDER BY O.OrderID

Return

GO
[/code]

Is there something I'm missing?

Thanks,


Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net

 

[JoeAtWork]

Overflow generally means you are trying to assign a value to a variable that is bigger than what the variable can hold. Like trying to assign one million to an int (whose maximum is around 32,000).

Step through the code to find exactly which line this is occurring on.

 

[RonRepp]

Joe:

I've done that. What "cured" it was deleting the autoseed column. I was not inserting there, but because it was a bulk insert, I believe that's what was wrong.

Thanks,


Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net