ASP Programming
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed Forums > Programming Languages - More > ASP Programming
Reload this Page

Can't execute a sp from the asp page...?

Discuss Can't execute a sp from the asp page...? in the ASP Programming forum on Dev Shed.
Can't execute a sp from the asp page...? ASP Programming forum discussing Active Server Pages coding techniques and problem solving methods. Use VBScript or Jscript to make dynamic web applications.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Page 1 of 2 1 2 >
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old October 16th, 2003, 08:11 AM
tarry tarry is offline
Junior Member
Dev Shed Newbie (0 - 499 posts)
  
Join Date: May 2003
Location: Netherlands
Posts: 23 tarry User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Can't execute a sp from the asp page...?
Hi there,

OS : Windows(obviously)
Appl. tier : IIS/ASP pages
Db Tier : Sql 7

PHP Code:
<html>

<head>
    <title>Delete Alumni</title>
    <link rel="stylesheet" href="../includes/stylesheet.css">
    <script language="JavaScript" src="../includes/script.js"></script>
</head>

<%
    Dim cnnAlumni
    Dim cmdDelAlumn
    Dim i
    
        If Request.Form("btnCancel") = "Cancel" Then
            Response.Clear
            Response.Redirect("main.asp")
        Else
        
        Set cnnAlumni = Server.CreateObject("ADODB.Connection")
        cnnAlumni.Open strDSNAlumni
    
        ' Create the commands
          Set cmdDelAlumni = Server.Createobject("ADODB.Command")
          
          With cmdDelAlumni
              .ActiveConnection = cnnAlumni
            [b].CommandText = "sp_Del_Alumni"[/b]
            .CommandType = adCmdStoredProc
            
            ' Create Parameters
            .Parameters.Append .CreateParameter("@naw_al",adVarWChar,adParamInput,12)
            
            For i=1 to Request.Form("txtNaw_al").Count
            
                .Parameters("@naw_al") = Request.Form("txtNaw_al")(i)
            
                .Execute , , adExecuteNoRecords
                
            Next
        
        End With
        
    cnnAlumni.Close
    
    Set cmdDelAlumni = Nothing
    Set cnnAlumni = Nothing
    
    End If
%>

<body class='content'>
<h1 class='content'>Delete alumni</h1>
<h3 class='content'><a class='content' href="main.asp"> alumniburo pages </a> > <a class='content' href="searchalumni.asp?id=<% = Request.QueryString("id") %>"> search alumni</a > > search results > confirm delete > delete</h3>
<h3 class='content'>Delete alumni</h3>
<p class='content'>Deletion complete.</p>
<form action="main.asp" method="post">
<hr class='content'>
<input class='button' type="submit" value="OK" name="btnOK">
</form>
</body>

</html> 


The highlighted stuff stored procedure should do trhe del;ete action, but fails constantly with a odbc error...

Cam anyone tell me what's wrong with this asp page.
I'm a DBA chipping in for my webadm who's ill.
Any help/input will be appreciated.
I've pasted the whole page that's why I'm not being descriptive here...
I end up getting an ODBC error..apparently connection...

upon punching delete the odbc error appears...
Last edited by tarry : October 16th, 2003 at 08:21 AM.
Reply With Quote
  #2  
Old October 16th, 2003, 03:43 PM
Utopia's Avatar
Utopia Utopia is offline
superficial
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Mar 2002
Location: Peterborough, England
Posts: 188 Utopia User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 36 m 8 sec
Reputation Power: 7
what odbc error is it?
Reply With Quote
  #3  
Old October 16th, 2003, 04:12 PM
tarry tarry is offline
Junior Member
Dev Shed Newbie (0 - 499 posts)
  
Join Date: May 2003
Location: Netherlands
Posts: 23 tarry User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Viewing this code from home(now) I think I see the issue in declaration. I'll try to rectify it and then will report and also on that precise ODBC error message.

Thanks for your time.

Tarry
Reply With Quote
  #4  
Old October 17th, 2003, 03:58 AM
tarry tarry is offline
Junior Member
Dev Shed Newbie (0 - 499 posts)
  
Join Date: May 2003
Location: Netherlands
Posts: 23 tarry User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
I've tried it but the error is...

Microsoft OLE DB Provider fro ODBC Drivers error '80040e31' [Microsoft][ODBC SQL Server Driver] Timeout expired.
/alumniburo/deletealumni.asp, line 47


The error keeps comnig back....

Anyone have experience with this and what may be the underlying problem.

I've check/tested the DSN connection on the IIS server to the SQL Server and it works ok.

Any one...?
Reply With Quote
  #5  
Old October 17th, 2003, 04:05 AM
Utopia's Avatar
Utopia Utopia is offline
superficial
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Mar 2002
Location: Peterborough, England
Posts: 188 Utopia User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 36 m 8 sec
Reputation Power: 7
Timeout expired.

The script is taking a long time to run? The results set is very big or SQL Server is taking a long time to return anything?

You can increase the script timeout in IIS under the Home directory tab on the default website, select "configuration" for the application in question and then select the options tab and the script timeout is displayed.

Matt
__________________
_______________
Matt
Reply With Quote
  #6  
Old October 17th, 2003, 04:14 AM
tarry tarry is offline
Junior Member
Dev Shed Newbie (0 - 499 posts)
  
Join Date: May 2003
Location: Netherlands
Posts: 23 tarry User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Matt,

Thanks for your prompt reply...
I'm gonna see at the IIS end.
One more q, I saw above in the decalration
Dim cmdalumn

where as elsewhere it's using cmdAlumni
I've changed the Dim to cmdalumni
Was/could that be a jinx? The ASP thing is new to me...
Reply With Quote
  #7  
Old October 17th, 2003, 04:18 AM
Utopia's Avatar
Utopia Utopia is offline
superficial
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Mar 2002
Location: Peterborough, England
Posts: 188 Utopia User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 36 m 8 sec
Reputation Power: 7
unlikely, but add <%Option Explict%> at the top of the page, below the <%@Language statement (if you have one)

This will ensure that you have declared all variables, rather than referenced them implicitly
Reply With Quote
  #8  
Old October 17th, 2003, 04:31 AM
tarry tarry is offline
Junior Member
Dev Shed Newbie (0 - 499 posts)
  
Join Date: May 2003
Location: Netherlands
Posts: 23 tarry User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Matt,

Thru the computer management/ii service/and right clicking my required application, I can only see the connection property.,connection timeout = 900 sec..Is this a script connection timeout? I think it's the (inactive) use connection
or have I misuderstood you??

Cheers...

Tarry
Reply With Quote
  #9  
Old October 17th, 2003, 04:34 AM
Utopia's Avatar
Utopia Utopia is offline
superficial
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Mar 2002
Location: Peterborough, England
Posts: 188 Utopia User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 36 m 8 sec
Reputation Power: 7
That's under the website tab, click the home directory tab and then click "configuration" on the application. If you dont have an application, click create and then click configuration. then, select the options tab.
Reply With Quote
  #10  
Old October 17th, 2003, 04:46 AM
tarry tarry is offline
Junior Member
Dev Shed Newbie (0 - 499 posts)
  
Join Date: May 2003
Location: Netherlands
Posts: 23 tarry User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Thanks Matt!

I've increased the timeout setting on the app/asp scripts option.

I'll try to also see the exec plan on that stored proc on the sql server. Maybe it can be written better..?

Thanks I'll report back as soon as the end user reports back...
Reply With Quote
  #11  
Old October 17th, 2003, 04:50 AM
Utopia's Avatar
Utopia Utopia is offline
superficial
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Mar 2002
Location: Peterborough, England
Posts: 188 Utopia User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 36 m 8 sec
Reputation Power: 7
How long does it take to give you a results set in Query Analyzer? Can you create a backup and try executing the query. Is the box under powered in terms of number-crunching power? Indexes? Relationships? The execution plan is normally by default good enough aint it? I'd have thought other areas might need sorting. maybe!

Matt
Reply With Quote
  #12  
Old October 17th, 2003, 06:44 AM
tarry tarry is offline
Junior Member
Dev Shed Newbie (0 - 499 posts)
  
Join Date: May 2003
Location: Netherlands
Posts: 23 tarry User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Mike,

It's a stored proc which with one input param performs...
and then performs an update on 3 tables.

It's really no big deal. I just heard from the user that the connection timeout expired again. I had increased the asp script timeout settings to 900 sec. (More than enough I presume)

Anything else that I need to check?
Reply With Quote
  #13  
Old October 17th, 2003, 06:49 AM
Utopia's Avatar
Utopia Utopia is offline
superficial
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Mar 2002
Location: Peterborough, England
Posts: 188 Utopia User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 36 m 8 sec
Reputation Power: 7
Must be an infinite loop somewhere.

How many form elements are there in the loop? comment out the stored proc execution bits of the ASP page and make it write out each request.form item to the screen.
Reply With Quote
  #14  
Old October 17th, 2003, 08:09 AM
tarry tarry is offline
Junior Member
Dev Shed Newbie (0 - 499 posts)
  
Join Date: May 2003
Location: Netherlands
Posts: 23 tarry User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
How do I debug the code then? The imput of @naw_al, which the end user does to select and to delete(or let that stored proc delete within the sql server) is al it needs to do.
The error is pounded back in a few seconds..like 10~15. I was on the phone with the user when this happened.

Anyways how do I debug the code to see if the code is the problem. I'm suspecting the security issue here/connecting user has no accessgrants(somewhere) ..as the stored proc..as I mentioned above does an....

Here the DSNconnection is made with the user who's default database is the one which shoot's that stored proc...

[1] update on a linked server
[2] update on its own schema/inserting the rec in a active_arch table.
Reply With Quote
  #15  
Old October 24th, 2003, 03:30 AM
tarry tarry is offline
Junior Member
Dev Shed Newbie (0 - 499 posts)
  
Join Date: May 2003
Location: Netherlands
Posts: 23 tarry User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Tested this stuff in the test env
I have tested thisstuff in the test environment with representative data and it worked completely...

What else should I check and where? The sad part is that all this was part of the Project which some guys did and I joined hte bandwagon a lot later...

Any takes , anyone?


TIA
Reply With Quote
Reply
Page 1 of 2 1 2 >

Viewing: Dev Shed Forums > Programming Languages - More > ASP Programming > Can't execute a sp from the asp page...?

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread: