Solved

SQL statement causing 500 - Internal server error using ASP

Posted on 2014-09-17
12
681 Views
Last Modified: 2014-09-23
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?

<% 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()

Open in new window


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.
0
Comment
Question by:princeservice
  • 4
  • 3
  • 2
  • +3
12 Comments
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40328765
You need to start by turning on asp errors in iis

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
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40328771
If you comment out the larger sql and un comment the basic sql does it work?
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 40328772
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
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 40328788
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
0
 
LVL 28

Expert Comment

by:becraig
ID: 40328828
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.
0
 

Author Comment

by:princeservice
ID: 40328867
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_constant 80 - 10.10.0.11 Mozilla/5.0+(Windows+NT+6.1;+WOW64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/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_constant 80 - 10.10.0.11 Mozilla/5.0+(Windows+NT+6.1;+WOW64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/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_constant 80 - 10.10.0.11 Mozilla/5.0+(Windows+NT+6.1;+WOW64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/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_constant 80 - 10.10.0.11 Mozilla/5.0+(Windows+NT+6.1;+WOW64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/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_constant 80 - 10.10.0.11 Mozilla/5.0+(Windows+NT+6.1;+WOW64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/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_constant 80 - 10.10.0.11 Mozilla/5.0+(Windows+NT+6.1;+WOW64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/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_constant 80 - 10.10.0.2 Mozilla/5.0+(Windows+NT+6.1;+WOW64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/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)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/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_constant 80 - 10.10.0.11 Mozilla/5.0+(Windows+NT+6.1;+WOW64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/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_constant 80 - 10.10.0.11 Mozilla/5.0+(Windows+NT+6.1;+WOW64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/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_constant 80 - 10.10.0.11 Mozilla/5.0+(Windows+NT+6.1;+WOW64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/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)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/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)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/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)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/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)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/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)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/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)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/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)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/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)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/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_constant 80 - 10.10.0.11 Mozilla/5.0+(Windows+NT+6.1;+WOW64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/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_constant 80 - 10.10.0.2 Mozilla/5.0+(Windows+NT+6.1;+WOW64;+Trident/7.0;+rv:11.0)+like+Gecko 500 0 0 0
2014-09-17 18:25:07 10.10.0.2 GET /portal/delivery.asp |18|800a0409|Unterminated_string_constant 80 - 10.10.0.2 Mozilla/5.0+(Windows+NT+6.1;+WOW64;+Trident/7.0;+rv:11.0)+like+Gecko 500 0 0 0
Capture.JPG
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 29

Accepted Solution

by:
Paul Jackson earned 500 total points
ID: 40328882
Your sql string is over multiple lines but you are not concatenating the sql string together, you need to do it like :

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"

so essentially you need to terminate the string at the end of each line and add &_
0
 

Author Comment

by:princeservice
ID: 40328904
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)

Open in new window

0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 40328911
Can you check the logs again and see if the same error is occurring now
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 40328917
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"

Open in new window

0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40328947
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'"
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40329357
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
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now