Solved

ASP+SQL procedure to update

Posted on 2004-08-02
9
459 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
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
form builder not starting 3 55
mysql database, schema and table creation 13 57
Merging spreadsheets 8 60
SQLite with Mobile Development 4 20
This article describes some very basic things about SQL Server filegroups.
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
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…

861 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