ASP+SQL procedure to update

Hi,
I have a sql procedure that uses sp_executesql fro updating a table as below



ALTER PROCEDURE UPDATE_COUNT
@OUT_BOX_NAME AS VARCHAR(100),
@CUST_NAME AS VARCHAR(100)
AS
BEGIN

   DECLARE @TEMP VARCHAR(100)
   declare @SPCall nvarchar(128)
   DECLARE @COUNTER_VAL INT
   DECLARE @STMT nVARCHAR(2000)
   DECLARE @STMT1 nVARCHAR(2000)
   DECLARE SUMMARY_CURSOR CURSOR FOR SELECT TABLE_NAME FROM SUMMARY_DETAILS
   DECLARE @declare nvarchar(255)
   SET @declare = '@COUNTER_VAL INT OUTPUT'
   --OPEN CURSOR
   OPEN SUMMARY_CURSOR                              
   --FETCH THE OBJECT FROM CURSOR
   FETCH NEXT FROM SUMMARY_CURSOR INTO @TEMP      

   --CHECK IF THERE IS A NEXT RECORD
   WHILE @@FETCH_STATUS = 0
     
      BEGIN
        --IF THE OBJECT IS SCRIPT
        IF @TEMP='S_BUSCOMP_SCRIPT' or @TEMP='S_APLT_BRSSCRPT' or @TEMP='S_APPLET_SCRIPT'or @TEMP='S_APPL_WEBSCRPT'or @TEMP='S_APPL_BRSSCRPT'or @TEMP='S_APPL_SCRIPT'or @TEMP='S_BC_BRS_SCRPT'or @TEMP='S_SVC_BRS_SCRPT'or @TEMP='S_SERVICE_SCRPT'
        BEGIN
                --FOR SCRIPTS WE NEED TO GET THE LOC
              SET @STMT1='SET @COUNTER_VAL =(SELECT (SUM(DATALENGTH (SCRIPT))/40) FROM '+@TEMP+' WHERE REPOSITORY_ID IN(SELECT ROW_ID FROM S_REPOSITORY WHERE NAME='+CHAR(39)+@CUST_NAME+char(39)+')and NAME not in(SELECT NAME FROM '+@TEMP+' WHERE REPOSITORY_ID IN (SELECT ROW_ID FROM S_REPOSITORY WHERE NAME='+char(39)+@OUT_BOX_NAME+char(39)+')))'                              
                print @stmt1
              EXEC @SPCall= sp_executesql @stmt1,@declare,@COUNTER_VAL OUTPUT
                PRINT @COUNTER_VAL  
                IF @COUNTER_VAL IS NULL
                   BEGIN
                       SET @COUNTER_VAL=0
                   END
              UPDATE SUMMARY_DETAILS SET COUNT_VALUE=@COUNTER_VAL WHERE TABLE_NAME=@TEMP
            FETCH NEXT FROM SUMMARY_CURSOR INTO @TEMP
                print 'temp is '+@TEMP  
              CONTINUE
       END
      --IF THE OBJECT IS OTHER THAN SCRIPT COUNT THE NUMBER
      SET @STMT='SET @COUNTER_VAL=(SELECT COUNT(NAME)AS COUNTER FROM '+@TEMP+' WHERE REPOSITORY_ID IN(SELECT ROW_ID FROM S_REPOSITORY WHERE NAME='+char(39)+@CUST_NAME+char(39)+')and NAME not in(SELECT NAME FROM '+@TEMP+' WHERE REPOSITORY_ID IN (SELECT ROW_ID FROM S_REPOSITORY WHERE NAME='+char(39)+@OUT_BOX_NAME+char(39)+')))'              
       
        EXEC @SPCall= sp_executesql @stmt,@declare, @COUNTER_VAL OUTPUT
        PRINT @COUNTER_VAL
        ---UPDATE THE SUMMARY IN THE TABLE
        UPDATE SUMMARY_DETAILS SET COUNT_VALUE=@COUNTER_VAL WHERE TABLE_NAME=@TEMP
      FETCH NEXT FROM SUMMARY_CURSOR INTO @TEMP
        END                      
      
   --CLOSE CURSOR
   CLOSE SUMMARY_CURSOR                    
   DEALLOCATE SUMMARY_CURSOR  
END


This works perfectly when executes in query analyzer but does not work when called from ASP or VB.
 The VB code is,
Public Function call_updt_count(ByVal oob As String, ByVal cust As String)
' On Error GoTo ErrorHandler
Dim strSQL As String

  'Create and set the connection information.
  conObj = Connect()
  'Create and set the Command object information
  Dim cmd As ADODB.Command
  Set cmd = CreateObject("ADODB.Command")
  strSQL = "UPDATE_COUNT " & "'" & oob & "'," & "'" & cust & "'"
  cmd.ActiveConnection = Conn
  cmd.CommandText = strSQL
  cmd.Execute
  cmd.ActiveConnection = Nothing
  Conn.Close
  Set Conn = Nothing
  Exit Function
ErrorHandler:
  MsgBox "Error: Unable to retrieve Objects"
  Set cmd = Nothing
  Set Conn = Nothing
End Function


Please help!
Thanks.
LVL 1
amulya_333Asked:
Who is Participating?
 
RomModConnect With a Mentor Commented:
The question has been PAQ'd and the 30 points have been refunded.
RomMod
Community Support Moderator
0
 
RichWallaceCommented:
Curious...where exactly does it fail, on cmd.Execute?  Are you trapping the error message anywhere?

-Rich
0
 
amulya_333Author Commented:
Yes, It fails on cmd.Execute. The error message is not of much help. It is as below,

Runtime-time error'-2147217871(80040e31):'
[Micrsoft][ODBC][SQL Server Driver][SQL Server]156
Thanks.
Amulya.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
RichWallaceCommented:
Nice error, really narrows that down doesn't it :)

I'm honestly not sure on this one.  I use .NET and haven't used the previous ADO environment for quite some time; I'm afraid I'm not much help but good luck, I'm sure someone on here will pick this up.

-Rich
0
 
MarcRosenbergCommented:
I would try the follwoing two approaches:

1. Do not use a command object but rather just :
conObj.execute strSQL

2. Use a command object with parameters in conjunction with your stored procedure:
 Set cmd = CreateObject("ADODB.Command")
 cmd.ActiveConnection = Conn
 cmd .CommandType = adCmdStoredProc  
 cmd.CommandText = "Update_Count"
 Set Param1=Create("Adodb.Parameter")
 Set Param1= cmd.CreateParameter("OUT_BOX_NAME ", adVarChar, adParamInput, 100, oob  )
 Set Param2=Create("Adodb.Parameter")
 Set Param2= cmd.CreateParameter("@CUST_NAME", adVarChar, adParamInput, 100, cust )
 cmd.Parameters.Append Param1
 cmd.Parameters.Append Param2
 cmd.Execute


Whats cool about this approach is that you don't have to quote delimit the strings and if the strings have ' or , in them they don't affect the sql.
Hope one of these option puts you on the right path.

Marc
0
 
MarcRosenbergCommented:
Another thing I noticed...

I believe this line:
cmd.ActiveConnection = Conn

Should be

cmd.ActiveConnection = conObj

unless you are using Conn as a global variable
0
 
amulya_333Author Commented:
Hi MarcRosenberg and  RichWallace,
    Thanks for taking time to answer my question.
    MarcRosenberg, conn is a global variable and conobj is a boolean returned after connect() depending on whether or not the connection was established.
    I have similar call to procs and all of them work. I believe it has something to do with sp_executesql bec' thats the only diff btwn other procs and this one.
Amulya.
     
0
 
amulya_333Author Commented:
The reason as to why this does not work is due to a inherent bug that sp_executesql. The resolution is that we avoid using sp_executesql with in procedures called from asp and incase we are forced then avoid the IF ...else loop.
For details refer
http://www.microsoft.com

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.