Solved

ASP+SQL procedure to update

Posted on 2004-08-02
9
415 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:amulya_333
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 1

Expert Comment

by:RichWallace
Comment Utility
Curious...where exactly does it fail, on cmd.Execute?  Are you trapping the error message anywhere?

-Rich
0
 
LVL 1

Author Comment

by:amulya_333
Comment Utility
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
 
LVL 1

Expert Comment

by:RichWallace
Comment Utility
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
 
LVL 2

Expert Comment

by:MarcRosenberg
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 2

Expert Comment

by:MarcRosenberg
Comment Utility
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
 
LVL 1

Author Comment

by:amulya_333
Comment Utility
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
 
LVL 1

Author Comment

by:amulya_333
Comment Utility
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
 

Accepted Solution

by:
RomMod earned 0 total points
Comment Utility
The question has been PAQ'd and the 30 points have been refunded.
RomMod
Community Support Moderator
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now