Robert Francis
asked on
SQL statement causing 500 - Internal server error using ASP
I am getting a 500 - Internal server error while trying to pull data from SQL Express using Classic ASP. The connection string is working just fine. You will see I have a commented out SQL statement and this works fine. If I try to use the SQL statement that has the inner join I get the error. Any ideas?
Also, why don't I get better error messages. If I am working on the local host and testing using IE I get:
An error occurred on the server when processing the URL. Please contact the system administrator.
If you are the system administrator please click here to find out more about this error.
This is worthless. Can this be fixed? Can I use anything other that IE? Do I need to be on the local host?
Thanks in advance for your time.
<% Dim connectstr, mSQL1, rs1
set connectstr = server.createobject("ADODB.Connection")
connectstr.open = "Provider=SQLNCLI10;Server=SERVER\EXACTSQLEXPRESS;Database=ppp;Uid=portal; Pwd=password;"
'mSQL1 = "SELECT Job, Customer FROM Job"
mSQL1 = "SELECT Job.Job, Job.Customer, Job.Part_Number, Job.Description, Job.Rev, Job.Status, Job.Customer_PO, Job.Customer_PO_LN,
Delivery.Promised_Quantity, Job.Order_Unit, Delivery.Promised_Date, Delivery.Comment, Job.Terms
FROM Job INNER JOIN Delivery ON Job.Job = Delivery.Job"
Set rs1 = connectstr.Execute(mSQL1)
'Do until rs1.EOF
%>
<tr>
<td width="200"><%Response.Write rs1("job")%></td>
<td><%Response.Write rs1("customer")%></td>
</tr>
<%'rs1.MoveNext()
Also, why don't I get better error messages. If I am working on the local host and testing using IE I get:
An error occurred on the server when processing the URL. Please contact the system administrator.
If you are the system administrator please click here to find out more about this error.
This is worthless. Can this be fixed? Can I use anything other that IE? Do I need to be on the local host?
Thanks in advance for your time.
If you comment out the larger sql and un comment the basic sql does it work?
have you run the sql statement directly in the database? that would be a good place to start?
Also, to have better error messages, make sure you're sending them to the browser:
In Internet Information Services (IIS) Manager —>
Default Web Site —>
Click Error Pages properties and select Detail errors
Also, to have better error messages, make sure you're sending them to the browser:
In Internet Information Services (IIS) Manager —>
Default Web Site —>
Click Error Pages properties and select Detail errors
What version of IIS are you using?
You need to look at the IIS logs to get further information on the error message
These log files are located at :
IIS6 : %windir%\System32\LogFiles
IIS7: %SystemDrive%\inetpub\logs \LogFiles
You need to look at the IIS logs to get further information on the error message
These log files are located at :
IIS6 : %windir%\System32\LogFiles
IIS7: %SystemDrive%\inetpub\logs
The 500 error indicates and error with your application, if it works when the sql statement is commented out then that's your problem.
Run the query in sql query window abd you'll find the problem.
Run the query in sql query window abd you'll find the problem.
ASKER
Scott - Yes
Big Monty and becraig- I actually created the SQL statement using SQL Server Management Server. See attached file. It works there.
I am using IIS7. Here are some of the errors from the log file:
2014-09-17 18:14:14 10.10.0.2 GET /portal/delivery.asp |15|800a0409|Unterminated_ string_con stant 80 - 10.10.0.11 Mozilla/5.0+(Windows+NT+6. 1;+WOW64)+ AppleWebKi t/537.36+( KHTML,+lik e+Gecko)+C hrome/37.0 .2062.103+ Safari/537 .36 500 0 0 218
2014-09-17 18:15:00 10.10.0.2 GET /portal/delivery.asp |15|800a0409|Unterminated_ string_con stant 80 - 10.10.0.11 Mozilla/5.0+(Windows+NT+6. 1;+WOW64)+ AppleWebKi t/537.36+( KHTML,+lik e+Gecko)+C hrome/37.0 .2062.103+ Safari/537 .36 500 0 0 202
2014-09-17 18:15:03 10.10.0.2 GET /portal/delivery.asp |15|800a0409|Unterminated_ string_con stant 80 - 10.10.0.11 Mozilla/5.0+(Windows+NT+6. 1;+WOW64)+ AppleWebKi t/537.36+( KHTML,+lik e+Gecko)+C hrome/37.0 .2062.103+ Safari/537 .36 500 0 0 202
2014-09-17 18:15:04 10.10.0.2 GET /portal/delivery.asp |15|800a0409|Unterminated_ string_con stant 80 - 10.10.0.11 Mozilla/5.0+(Windows+NT+6. 1;+WOW64)+ AppleWebKi t/537.36+( KHTML,+lik e+Gecko)+C hrome/37.0 .2062.103+ Safari/537 .36 500 0 0 202
2014-09-17 18:16:01 10.10.0.2 GET /portal/delivery.asp |15|800a0409|Unterminated_ string_con stant 80 - 10.10.0.11 Mozilla/5.0+(Windows+NT+6. 1;+WOW64)+ AppleWebKi t/537.36+( KHTML,+lik e+Gecko)+C hrome/37.0 .2062.103+ Safari/537 .36 500 0 0 202
2014-09-17 18:16:03 10.10.0.2 GET /portal/delivery.asp |15|800a0409|Unterminated_ string_con stant 80 - 10.10.0.11 Mozilla/5.0+(Windows+NT+6. 1;+WOW64)+ AppleWebKi t/537.36+( KHTML,+lik e+Gecko)+C hrome/37.0 .2062.103+ Safari/537 .36 500 0 0 202
2014-09-17 18:19:42 10.10.0.2 GET /portal/delivery.asp |15|800a0409|Unterminated_ string_con stant 80 - 10.10.0.2 Mozilla/5.0+(Windows+NT+6. 1;+WOW64)+ AppleWebKi t/537.36+( KHTML,+lik e+Gecko)+C hrome/37.0 .2062.120+ Safari/537 .36 500 0 0 1
2014-09-17 18:19:42 10.10.0.2 GET /favicon.ico - 80 - 10.10.0.2 Mozilla/5.0+(Windows+NT+6. 1;+WOW64)+ AppleWebKi t/537.36+( KHTML,+lik e+Gecko)+C hrome/37.0 .2062.120+ Safari/537 .36 404 0 2 22
2014-09-17 18:21:41 10.10.0.2 GET /portal/delivery.asp |15|800a0409|Unterminated_ string_con stant 80 - 10.10.0.11 Mozilla/5.0+(Windows+NT+6. 1;+WOW64)+ AppleWebKi t/537.36+( KHTML,+lik e+Gecko)+C hrome/37.0 .2062.103+ Safari/537 .36 500 0 0 202
2014-09-17 18:21:44 10.10.0.2 GET /portal/delivery.asp |15|800a0409|Unterminated_ string_con stant 80 - 10.10.0.11 Mozilla/5.0+(Windows+NT+6. 1;+WOW64)+ AppleWebKi t/537.36+( KHTML,+lik e+Gecko)+C hrome/37.0 .2062.103+ Safari/537 .36 500 0 0 187
2014-09-17 18:21:46 10.10.0.2 GET /portal/delivery.asp |15|800a0409|Unterminated_ string_con stant 80 - 10.10.0.11 Mozilla/5.0+(Windows+NT+6. 1;+WOW64)+ AppleWebKi t/537.36+( KHTML,+lik e+Gecko)+C hrome/37.0 .2062.103+ Safari/537 .36 500 0 0 202
2014-09-17 18:21:59 10.10.0.2 GET /portal/delivery.asp |24|800a000d|Type_mismatch 80 - 10.10.0.11 Mozilla/5.0+(Windows+NT+6. 1;+WOW64)+ AppleWebKi t/537.36+( KHTML,+lik e+Gecko)+C hrome/37.0 .2062.103+ Safari/537 .36 500 0 0 202
2014-09-17 18:22:01 10.10.0.2 GET /portal/delivery.asp |24|800a000d|Type_mismatch 80 - 10.10.0.11 Mozilla/5.0+(Windows+NT+6. 1;+WOW64)+ AppleWebKi t/537.36+( KHTML,+lik e+Gecko)+C hrome/37.0 .2062.103+ Safari/537 .36 500 0 0 202
2014-09-17 18:22:14 10.10.0.2 GET /portal/delivery.asp - 80 - 10.10.0.11 Mozilla/5.0+(Windows+NT+6. 1;+WOW64)+ AppleWebKi t/537.36+( KHTML,+lik e+Gecko)+C hrome/37.0 .2062.103+ Safari/537 .36 200 0 0 109
2014-09-17 18:22:14 10.10.0.2 GET /portal/css/style.css - 80 - 10.10.0.11 Mozilla/5.0+(Windows+NT+6. 1;+WOW64)+ AppleWebKi t/537.36+( KHTML,+lik e+Gecko)+C hrome/37.0 .2062.103+ Safari/537 .36 304 0 0 202
2014-09-17 18:22:14 10.10.0.2 GET /portal/css/reset.css - 80 - 10.10.0.11 Mozilla/5.0+(Windows+NT+6. 1;+WOW64)+ AppleWebKi t/537.36+( KHTML,+lik e+Gecko)+C hrome/37.0 .2062.103+ Safari/537 .36 304 0 0 202
2014-09-17 18:22:34 10.10.0.2 GET /portal/delivery.asp - 80 - 10.10.0.11 Mozilla/5.0+(Windows+NT+6. 1;+WOW64)+ AppleWebKi t/537.36+( KHTML,+lik e+Gecko)+C hrome/37.0 .2062.103+ Safari/537 .36 200 0 0 109
2014-09-17 18:22:34 10.10.0.2 GET /portal/css/style.css - 80 - 10.10.0.11 Mozilla/5.0+(Windows+NT+6. 1;+WOW64)+ AppleWebKi t/537.36+( KHTML,+lik e+Gecko)+C hrome/37.0 .2062.103+ Safari/537 .36 304 0 0 202
2014-09-17 18:22:34 10.10.0.2 GET /portal/css/reset.css - 80 - 10.10.0.11 Mozilla/5.0+(Windows+NT+6. 1;+WOW64)+ AppleWebKi t/537.36+( KHTML,+lik e+Gecko)+C hrome/37.0 .2062.103+ Safari/537 .36 304 0 0 202
2014-09-17 18:23:25 10.10.0.2 GET /portal/delivery.asp |18|800a0409|Unterminated_ string_con stant 80 - 10.10.0.11 Mozilla/5.0+(Windows+NT+6. 1;+WOW64)+ AppleWebKi t/537.36+( KHTML,+lik e+Gecko)+C hrome/37.0 .2062.103+ Safari/537 .36 500 0 0 218
2014-09-17 18:24:15 10.10.0.2 GET /portal/delivery.asp |18|800a0409|Unterminated_ string_con stant 80 - 10.10.0.2 Mozilla/5.0+(Windows+NT+6. 1;+WOW64;+ Trident/7. 0;+rv:11.0 )+like+Gec ko 500 0 0 0
2014-09-17 18:25:07 10.10.0.2 GET /portal/delivery.asp |18|800a0409|Unterminated_ string_con stant 80 - 10.10.0.2 Mozilla/5.0+(Windows+NT+6. 1;+WOW64;+ Trident/7. 0;+rv:11.0 )+like+Gec ko 500 0 0 0
Capture.JPG
Big Monty and becraig- I actually created the SQL statement using SQL Server Management Server. See attached file. It works there.
I am using IIS7. Here are some of the errors from the log file:
2014-09-17 18:14:14 10.10.0.2 GET /portal/delivery.asp |15|800a0409|Unterminated_
2014-09-17 18:15:00 10.10.0.2 GET /portal/delivery.asp |15|800a0409|Unterminated_
2014-09-17 18:15:03 10.10.0.2 GET /portal/delivery.asp |15|800a0409|Unterminated_
2014-09-17 18:15:04 10.10.0.2 GET /portal/delivery.asp |15|800a0409|Unterminated_
2014-09-17 18:16:01 10.10.0.2 GET /portal/delivery.asp |15|800a0409|Unterminated_
2014-09-17 18:16:03 10.10.0.2 GET /portal/delivery.asp |15|800a0409|Unterminated_
2014-09-17 18:19:42 10.10.0.2 GET /portal/delivery.asp |15|800a0409|Unterminated_
2014-09-17 18:19:42 10.10.0.2 GET /favicon.ico - 80 - 10.10.0.2 Mozilla/5.0+(Windows+NT+6.
2014-09-17 18:21:41 10.10.0.2 GET /portal/delivery.asp |15|800a0409|Unterminated_
2014-09-17 18:21:44 10.10.0.2 GET /portal/delivery.asp |15|800a0409|Unterminated_
2014-09-17 18:21:46 10.10.0.2 GET /portal/delivery.asp |15|800a0409|Unterminated_
2014-09-17 18:21:59 10.10.0.2 GET /portal/delivery.asp |24|800a000d|Type_mismatch
2014-09-17 18:22:01 10.10.0.2 GET /portal/delivery.asp |24|800a000d|Type_mismatch
2014-09-17 18:22:14 10.10.0.2 GET /portal/delivery.asp - 80 - 10.10.0.11 Mozilla/5.0+(Windows+NT+6.
2014-09-17 18:22:14 10.10.0.2 GET /portal/css/style.css - 80 - 10.10.0.11 Mozilla/5.0+(Windows+NT+6.
2014-09-17 18:22:14 10.10.0.2 GET /portal/css/reset.css - 80 - 10.10.0.11 Mozilla/5.0+(Windows+NT+6.
2014-09-17 18:22:34 10.10.0.2 GET /portal/delivery.asp - 80 - 10.10.0.11 Mozilla/5.0+(Windows+NT+6.
2014-09-17 18:22:34 10.10.0.2 GET /portal/css/style.css - 80 - 10.10.0.11 Mozilla/5.0+(Windows+NT+6.
2014-09-17 18:22:34 10.10.0.2 GET /portal/css/reset.css - 80 - 10.10.0.11 Mozilla/5.0+(Windows+NT+6.
2014-09-17 18:23:25 10.10.0.2 GET /portal/delivery.asp |18|800a0409|Unterminated_
2014-09-17 18:24:15 10.10.0.2 GET /portal/delivery.asp |18|800a0409|Unterminated_
2014-09-17 18:25:07 10.10.0.2 GET /portal/delivery.asp |18|800a0409|Unterminated_
Capture.JPG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I got excited there for a second Paul but it didn't work. Here is the new lines:
mSQL1 = "SELECT Job.Job, Job.Customer, Job.Part_Number, Job.Description, Job.Rev, Job.Status, Job.Customer_PO, Job.Customer_PO_LN," &_
"Delivery.Promised_Quantity, Job.Order_Unit, Delivery.Promised_Date, Delivery.Comment, Job.Terms" &_
"FROM Job INNER JOIN Delivery ON Job.Job = Delivery.Job"
Set rs1 = connectstr.Execute(mSQL1)
Can you check the logs again and see if the same error is occurring now
looks like you need to put a space on the end of line 2 before the string termination character, or else its getting combined with the FROM on the next line:
mSQL1 = "SELECT Job.Job, Job.Customer, Job.Part_Number, Job.Description, Job.Rev, Job.Status, Job.Customer_PO, Job.Customer_PO_LN," &_
"Delivery.Promised_Quantity, Job.Order_Unit, Delivery.Promised_Date, Delivery.Comment, Job.Terms " &_
"FROM Job INNER JOIN Delivery ON Job.Job = Delivery.Job"
What Paul has http:#a40328917 is correct. If you need to build t he sql on the fly, I also use
sql="SELECT field1, field2 FROM myTable"
sql=sql&"WHERE somefield = 'abc'"
sql="SELECT field1, field2 FROM myTable"
sql=sql&"WHERE somefield = 'abc'"
tip include a space at the beginning of each line (maybe not line 1)
sql="SELECT field1, field2 FROM myTable"
sql=sql&" WHERE somefield = 'abc'"
^
^
^
that's a space at the beginning
without that space you could get:
SELECT field1, field2 FROM myTableWHERE somefield = 'abc'
a: there is no table called myTableWHERE
b: thare is now no WHERE clause, but there is this weird predicate somefield = 'abc' dangling at the end
>> sql will error
sql="SELECT field1, field2 FROM myTable"
sql=sql&" WHERE somefield = 'abc'"
^
^
^
that's a space at the beginning
without that space you could get:
SELECT field1, field2 FROM myTableWHERE somefield = 'abc'
a: there is no table called myTableWHERE
b: thare is now no WHERE clause, but there is this weird predicate somefield = 'abc' dangling at the end
>> sql will error
You will probably still have to log into the server to see the errors. If shared hosting, contact the provider.
http://www.iis.net/learn/application-frameworks/running-classic-asp-applications-on-iis-7-and-iis-8/classic-asp-script-error-messages-no-longer-shown-in-web-browser-by-default