-1

I am supporting an old Classic ASP VBscript application. My bad luck is that I can't even debug the code in my servers. After scripting I need to see the output directly on webpage. The below code is Throwing 500 - Internal server error in webpage. Can you please find where this code has gone wrong. Thanks in advance. I've followed the below links while building the code

http://www.devx.com/tips/Tip/13399

http://stackoverflow.com/questions/36174283/executing-stored-procedure-of-sql-in-vbscript?noredirect=1#comment59989620_36174283

http://forums.asp.net/t/1767387.aspx?how+to+call+a+stored+procedure+with+date+time+parameters+from+a+classic+asp+page

'Set Connection String to Database
sConnect = "PROVIDER=SQLOLEDB;DATA SOURCE=SCRBNGADK00XXXX;DATABASE=SMART2XXX;UID=XXXX;PWD=XXXX;"

' Establish connection.
Set oConObj = CreateObject( "ADODB.Connection" )
Set oCmdObj = CreateObject("ADODB.Command")
Set Rs1 = CreateObject( "ADODB.Recordset" )
oConObj.ConnectionString = sConnect
oConObj.Open

Set oCmdObj.ActiveConnection = oConObj
oCmdObj.commandtype=adCmdStoredProc
oCmdObj.CommandText = "date_diff"

Set objParm = oCmdObj.CreateParameter("@from", adDBTimeStamp, adParamInput,, R1("LEAVE_FROM"))
oCmdObj.Parameters.Append objParm
Set objParm = oCmdObj.CreateParameter("@To", adDBTimeStamp, adParamInput,,  R1("LEAVE_TO"))
oCmdObj.Parameters.Append objParm
Set objParm = oCmdObj.CreateParameter("@companycode", adVarChar, adParamInput,200, R2("COMPANY_CODE"))
oCmdObj.Parameters.Append objParm  
Set objParm = oCmdObj.CreateParameter("@locationcode", adVarChar, adParamInput,200, R2("LOCATION_CODE"))
oCmdObj.Parameters.Append objParm
Set objParm = oCmdObj.CreateParameter("@hol", adInteger, adParamOutput)
oCmdObj.Parameters.Append objParm
set Rs1 = oCmdObj.Execute
OutPut = oCmdObj.Parameters("@hol")


If Not IsNull(OutPut) Then
wscript.echo "Result :" & OutPut
End If

Set Rs1 = Nothing
Set oConObj = Nothing
Set oCmdObj = Nothing
schudel
  • 1,235
  • 2
  • 11
  • 18
METALHEAD
  • 2,734
  • 3
  • 22
  • 37
  • Do you have the full error? – user692942 Mar 23 '16 at 14:06
  • The webpage simply throwing 500 error. I am unable to debug this in my servers. I just trying to execute the SP. – METALHEAD Mar 23 '16 at 14:08
  • Also one more thing..`R1("LEAVE_FROM")` is of form `4/18/2016` while `@from` is of `datetime`....will this through any error?? – METALHEAD Mar 23 '16 at 14:12
  • 1
    This isn't .net so you could try to do a try/catch by using "On Error Resume Next" and then displaying the "Err.Description" when "Err.Number <> 0". You'll might have a hard time debugging if you can't see the error. (or check the iis logs) – the_lotus Mar 23 '16 at 14:23
  • 2
    This is likely unanswerable without an error message. 1) Switch on [error reporting](http://stackoverflow.com/questions/2640526/detailed-500-error-message-asp-iis-7-5) 2) To use constants like adDBTimeStamp you must bind to the type library or manually declare/include - have you done so? – Alex K. Mar 23 '16 at 14:34
  • 1
    Have a look using Event Viewer to see if you have any more information about the error in your Application Logs. Also try and user the same param values directly in the database to see if you get an error. – Mych Mar 23 '16 at 14:43
  • 2
    You've got you question badly tagged, also - try removing the VB.NET tag. – Paul Mar 23 '16 at 15:23

1 Answers1

1

The correct answer to this is to turn on detailed error messages (and then make sure your browser doesn't eathide said detailed error messages in the name of "friendliness"). But sometimes you really don't have any control over the server, and can't do any of that. Are you completely screwed? Well, not entirely.

What you can do is turn off error handling via On Error Resume Next, and then manually step through your code via Response.Write and Response.End.

On Error Resume Next
'Set Connection String to Database
sConnect = "PROVIDER=SQLOLEDB;DATA SOURCE=SCRBNGADK00XXXX;DATABASE=SMART2XXX;UID=XXXX;PWD=XXXX;"

' Establish connection.
Set oConObj = CreateObject("ADODB.Connection")
If err.Number <> 0 Then
    Response.Write "Error creating connection"
    Response.End
End If
Set oCmdObj = CreateObject("ADODB.Command")
If err.Number <> 0 Then
    Response.Write "Error creating command object"
    Response.End
End If
Set Rs1 = CreateObject("ADODB.Recordset")
If err.Number <> 0 Then
    Response.Write "Error creating recordset object"
    Response.End
End If
'etc., etc., etc.

In reality, you'd probably only put the error checks after the lines you suspect are causing the problem, and instead of adding dozens of If err.number... sections, you can just have one such section that you keep moving down until you find the error.

Community
  • 1
  • 1
Martha
  • 3,932
  • 3
  • 33
  • 42
  • 1
    Most hosted environments will use iis 7.0 or above now so there is no issue configuring it in the `web.config`. Remember it is IIS that reads the `web.config` not Classic ASP so this is good way for those on hosting without access to the server to configure IIS settings. – user692942 Mar 23 '16 at 15:12
  • 1
    @Martha ....Thank you so much for ur valuable messages..I will turn on detailed error messages and check where the code is getting wrong.... – METALHEAD Mar 24 '16 at 05:31