[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