Solved

ASP+SQL procedure to update

Posted on 2004-08-02
9
469 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 1

Expert Comment

by:RichWallace
ID: 11699705
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
ID: 11700796
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
ID: 11704806
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Expert Comment

by:MarcRosenberg
ID: 11708984
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
 
LVL 2

Expert Comment

by:MarcRosenberg
ID: 11709597
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
ID: 11712580
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
ID: 11751023
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
ID: 11796984
The question has been PAQ'd and the 30 points have been refunded.
RomMod
Community Support Moderator
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
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…

726 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