[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

ASP+SQL procedure to update

Posted on 2004-08-02
9
Medium Priority
?
494 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

656 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