[thelist] datatype conversion error
Warden, Matt
mwarden at mattwarden.com
Fri Jun 29 11:13:19 CDT 2001
thelist,
I'm having a lot of trouble with a stored procedure I wrote for MS SQL
Server 7. Specifically, I'm having problems with the "getting it to work"
part, which is arguably the most important part. I'm getting the following
error whether I call the stored proc from ASP or directly in a database SQL
window:
Microsoft OLE DB Provider for SQL Server error '80040e07'
Error converting data type varchar to int.
/tracking/ProcessTracking.asp, line 37
I get the same error when directly running it against the DB (sans the line
number, of course) and there is no pointer as to where in my stored
procedure the problem lies. Below is the entire stored procedure after much
"well, let's try this" trial-and-error debugging (for instance, the CASTs
were added with little reasoning):
CREATE PROCEDURE sp_UpdateStats
@ReturnedStatsSessionID VARCHAR(20) OUTPUT,
@StatsSessionID INT,
@IPAddress VARCHAR(15),
@UserAgentString VARCHAR(100),
@ClientName VARCHAR(50),
@ClientVersion INT,
@URL VARCHAR(100)
AS
DECLARE @Owner VARCHAR(30)
DECLARE @NewStatsSessionID INT
BEGIN TRANSACTION
--SET XACT_ABORT ON
SET @Owner = 'trackingsystem at synchrony.net'
IF EXISTS (SELECT 1 FROM sync_stats_session WHERE StatsSession_ID =
@StatsSessionID)
BEGIN
SET @ReturnedStatsSessionID = (SELECT CAST(@StatsSessionID AS integer));
-- if session already exists
UPDATE sync_stats_session SET Hits=(Hits+1) WHERE StatsSession_ID =
@StatsSessionID;
INSERT INTO sync_stats_hit
(StatsSession_ID,URL,IsActive,Owner)
VALUES
(@StatsSessionID,LOWER(@URL),1, at Owner);
END
ELSE
BEGIN
-- create session record
INSERT INTO sync_stats_session
(InitialIPAddress,UserAgentString,ClientName,ClientVersion,IsJavaScriptEnabl
ed,URLFirstVisited,Hits,LastRequestDateTime,IsActive,Owner)
VALUES
(@IPAddress, at UserAgentString, at ClientName, at ClientVersion,0, at URL,1,getdate(),1
, at Owner);
SET @NewStatsSessionID = (SELECT @@Identity FROM sync_dual);
SET @ReturnedStatsSessionID = (SELECT CAST(@NewStatsSessionID AS
integer));
INSERT INTO sync_stats_hit
(StatsSession_ID,URL,IsActive,Owner)
VALUES
(@NewStatsSessionID,LOWER(@URL),1, at Owner);
END
--SET XACT_ABORT OFF
COMMIT TRANSACTION
IF (@ReturnedStatsSessionID > 0)
RETURN 0
ELSE
RETURN 99
And I'm calling it like this (one line):
exec sp_UpdateStats 0,'10.2.3.115','Mozilla/4.0 (compatible; MSIE 5.01;
Windows NT 5.0)','Microsoft Internet Explorer',5,NULL
Any idea what's going on? I can't see where the datatype problem is.
I know this is a long post so I'll include a tip.
<tip type="ASP Code Modularization">
The best way to make multi-function (display, save, delete, add) scripts for
site admin sections is to completely modularize your code. Put everything in
subroutines and functions except for some global vars (if needed) and a
SELECT CASE at the bottom which determines which subroutines to call. For
example:
SELECT CASE lcase(trim(request("action")))
CASE "display"
CALL PageHeader()
CALL DisplayItem(iItemID)
CALL PageClose()
CASE "add"
CALL PageHeader()
CALL DisplayNewItemForm()
CALL PageClose()
CASE "delete"
CALL DeleteItem(iItemID)
CALL RedirectToThisPage("?action=select")
CASE "save"
CALL SaveItem(iItemID)
CALL RedirectToThisPage("?action=display&itemid=" & iItemID)
CASE "select", ""
CALL PageHeader()
CALL ShowSelectItemForm()
CALL PageClose()
CASE ELSE
Response.Write "Mr. T says: Whatchoo doin', fool?"
END SELECT
</tip>
Thanks and sorry about the length.
--
mattwarden
mattwarden.com
More information about the thelist
mailing list