Stored Procedure call not working

[wbodger]

I have this code

set Conn = server.createobject("ADODB.Connection")
Conn.open DB_CONN

SET cmd = server.CreateObject("ADODB.Command")

with cmd
	.ActiveConnection = Conn
	.CommandText = "plant.dbo.sp_insert_rh_housekeeping_survey"
	.CommandType = adCmdStoredProc

		' Input parameters
       .Parameters.Append .createparameter("@Building", adVarChar, adParamInput, 50, Building)
       .Parameters.Append .createparameter("@Floor", adVarChar, adParamInput, 50, Floor)
       .Parameters.Append .createparameter("@YearInSchool", adVarChar, adParamInput, 50, YearInSchool)
       .Parameters.Append .createparameter("@RLStatus", adVarChar, adParamInput, 50, RLStatus)
       .Parameters.Append .createparameter("@SweepHallwayFloors", adVarChar, adParamInput, 50, SweepHallwayFloors)
       .Parameters.Append .createparameter("@SweepStairwells", adVarChar, adParamInput, 50, SweepStairwells)
       .Parameters.Append .createparameter("@VacuumLoungeCarpets", adVarChar, adParamInput, 50, VacuumLoungeCarpets)
       .Parameters.Append .createparameter("@ArrangeLoungeFurniture", adVarChar, adParamInput, 50, ArrangeLoungeFurniture)
       .Parameters.Append .createparameter("@IroningTypingFoors", adVarChar, adParamInput, 50, IroningTypingFoors)
       .Parameters.Append .createparameter("@WallSmudges", adVarChar, adParamInput, 50, WallSmudges)
       .Parameters.Append .createparameter("@WaterFountains", adVarChar, adParamInput, 50, WaterFountains)
       .Parameters.Append .createparameter("@EmptyTrash", adVarChar, adParamInput, 50, EmptyTrash)
       .Parameters.Append .createparameter("@ChangeBulbs", adVarChar, adParamInput, 50, ChangeBulbs)
       .Parameters.Append .createparameter("@BathroomSinks", adVarChar, adParamInput, 50, BathroomSinks)
       .Parameters.Append .createparameter("@CleaningToilets", adVarChar, adParamInput, 50, CleaningToilets)
       .Parameters.Append .createparameter("@ShowerFloorsWalls", adVarChar, adParamInput, 50, ShowerFloorsWalls)
       .Parameters.Append .createparameter("@SupplyDispenserSupplies", adVarChar, adParamInput, 50, SupplyDispenserSupplies)
       .Parameters.Append .createparameter("@FriendlyHelpfulAttitude", adVarChar, adParamInput, 50, FriendlyHelpfulAttitude)
       .Parameters.Append .createparameter("@DiligentWithTime", adVarChar, adParamInput, 50, DiligentWithTime)
       .Parameters.Append .createparameter("@Comment", adVarChar, adParamInput, 4000, Comment)
        
        'Execute the function
	    'If not returning a recordset, use the adExecuteNoRecords parameter option
	Set rs=.Execute

end with
set cmd = nothing

I know that the db connection is open and working because on the same page if I comment out the sproc call and just use inline SQL, then it works fine. Am I missing something? I checked the permissions on the sproc and public has execute permissions, I tested the sproc from management studio and it worked fine. And all of the variables are defined and populated above, so I know it is something to do with the sproc, just not sure what...

Any ideas?

Thanks,
Willie

 

[gmmastros]

What's not working about this? Are you getting any errors? Is it not inserting a row in to your table?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wbodger]

I am getting a 500 error and nothing is getting inserted into the database. Unfortunately, it is not an environment that I have any control over. I have asked a similar question of those that do have control over the environment, but I thought I would ask here as well to see if I had just overlooked something (I know sometimes I see what I want to see, not what is really there...).

wb

 

[wbodger]

Is there anything I can do to capture better what error I am getting back from the webserver without having admin rights on the server?

 

[guitarzan]

Is there anything I can do to capture better what error I am getting back from the webserver without having admin rights on the server?

You don't need admin rights to see runtime errors... Uncheck "Show friendly HTTP error messages"... See faq333-5427

 

[guitarzan]

... uncheck that option in your web browser, not the server

 

[wbodger]

Well, I did two things at once. I copied the page to a new file name and uploaded that and went to test it in IE with the Show friendly HTTP error messages box unchecked. However, the file worked fine with the new file name. I am wondering if perhaps there was some code in the global.asa file on the site that was causing me grief for changing an asp page? Not sure, but at least I know that my code was right and ti appears to have been something weird about the server.

Thanks for the help!