• 0

[VBScript] Displaying SQL query results from a table


Question

Hello guys,

It's great to finally join the greatness of Neowin :) I've just picked up scripting in VBScript, but I'm having a bit of a difficulty with the code below. Trying to display this ASP page shows error 500 in the browser. For the life of me I can't figure out where the problem is.

<%

<!--#include file="header.asp" --> 
<!--#include file="peopleheader.asp" -->
<BR><br>

<%   
Option Explicit
Dim objConn						' Our Connection Object
Dim objRS							' Our Recordset Object
Dim strSQL						  ' Our SQL String to access the database
Dim strConnection			 ' Our Connection string to access the database
Dim i								' a counter variable

' -- Create objects
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")

' -- Connection String Value
strConnection = "DSN=data;"

' -- Open the Connection
objConn.Open strConnection

' -- Our SQL Statement
strSQL = "SELECT * FROM Data"
' -- Populate our Recordset with data
set objRS = objConn.Execute (strSQL)


if (objRS.BOF and objRS.EOF) then
	response.write "No records found"
	response.end
End if

response.write "<TABLE BORDER='1' CELLPADDING='2' CELLSPACING='1' WIDTH='100%'>"

	' -- Output the Field Names as the first row in the table
	Response.Write "<TR BGCOLOR='"#CCCCCC"'>"
	For i = 0 to objRS.Fields.Count - 1
		Response.Write "<TH><FONT FACE='"ARIAL"' SIZE='"2"'> & objRS.Fields(i).Name & </FONT></TH>"
	Next
	Response.write "</TR>"
	' -- Now output the contents of the Recordset
	objRS.MoveFirst
	Do While Not objRS.EOF
		' -- output the contents
		Response.Write "<TR>"
		For i = 0 to objRS.Fields.Count - 1
			Response.Write "<TD><FONT FACE='"ARIAL"' SIZE='"1"'> & objRS.Fields(i) & </FONT></TD>"
		Next
		Response.write "</TR>"
		' -- move to the next record
		objRS.MoveNext
	Loop

	response.write "</table>"

	objRS.Close
	set objRS = Nothing
	objConn.Close
	set objConn = Nothing


%>
<!--#include file="footer.asp" -->
%>

If anyone is able to offer some assistance with this it will be much appreciated. I'm kind of desperate :)

Edited by vanx
Fixed thread title

9 answers to this question

Recommended Posts

  • 0

I turned off friendly error messages in both browsers but it's still showing that error 500.

Not a problem though, because I've fixed that page's problem. However, I now have an issue with the code below:

<!--#include file="header.asp" --> 
<!--#include file="peopleheader.asp" -->
<br><br>

<%   
Option Explicit
Dim objConn						' Our Connection Object
Dim objRS							' Our Recordset Object
Dim strSQL						  ' Our SQL String to access the database
Dim strConnection			 ' Our Connection string to access the database
Dim i								' a counter variable
Dim searchstr 'what we get from the form on the main page

' -- Create objects
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")

' -- Connection String Value
strConnection = "DSN=data;"

' -- Open the Connection
objConn.Open strConnection

if request.form("firstorlast")="surnamer = "where "&request.form("firstorlast")& & Request.form("lname")& if

' -- Our SQL Statement
strSQL = "SELECT * FROM Data" & searchstr
' -- Populate our Recordset with data
set objRS = objConn.Execute (strSQL)


if (objRS.BOF and objRS.EOF) then
	response.write "No records found"
	response.end
End if
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<!--#include file="header.asp" --> 
<!--#include file="peopleheader.asp" -->
<BR><BR>

<HTML>
<HEAD>
	<TITLE>Testing</TITLE>
</HEAD>

<BODY>
<TABLE BORDER="1" CELLPADDING="2" CELLSPACING="1" WIDTH="100%">
<%
	' -- Output the Field Names as the first row in the table
	Response.Write "<TR BGCOLOR=""#66cccc"">"
	Response.Write "<TH>ID</TH><TH>Name</TH><TH>Surname</TH><TH>Job Title</TH><TH>Department</TH><TH>Manager</TH><TH>Location</TH><TH>Office Phone</TH><TH>Skype</TH><TH>Office Fax</TH><TH>Mobile</TH><TH>Room</TH><TH>Building</TH>"

	Response.write "</TR>"
	' -- Now output the contents of the Recordset
	objRS.MoveFirst
	Do While Not objRS.EOF
		' -- output the contents
		Response.Write "<TR>"
		For i = 0 to objRS.Fields.Count - 1
			Response.Write "<TD><FONT FACE=""ARIAL"" SIZE=""1"">" & objRS.Fields(i) & "</FONT></TD>"

		Next

		Response.write "</TR>"

		' -- move to the next record
		objRS.MoveNext

	Loop


	objRS.Close
	set objRS = Nothing
	objConn.Close
	set objConn = Nothing




 %>
</TABLE>


</BODY>
</HTML>

<!--#include file="footer.asp" -->

It shows the same error message as before. I have a feeling it has something to do with how I create a SQL query string. I need it to be composed of variables that I get from a form on another page.

Once again, all assistance is appreciated!

  • 0

searchstr = "where "&request.form("firstorlast")& & Request.form("lname")&

and

strSQL = "SELECT * FROM Data" & searchstr

will end up with "SELECT * FROM Datawhere ..."

You need a space at the end of one, or the start of the other...

  • 0

Nope, it is not working. Showing same 500 Error message. Here's the revised code for the search.asp page (I've added a space into the SQL statement as per your correction.

<!--#include file="header.asp" --> 
<!--#include file="peopleheader.asp" -->
<br><br>

<%   
Option Explicit
Dim objConn						' Our Connection Object
Dim objRS							' Our Recordset Object
Dim strSQL						  ' Our SQL String to access the database
Dim strConnection			 ' Our Connection string to access the database
Dim i								' a counter variable
Dim searchstr 'what we get from the form on the main page

' -- Create objects
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")

' -- Connection String Value
strConnection = "DSN=data;"

' -- Open the Connection
objConn.Open strConnection

if request.form("firstorlast")="surnamer = "where surname like '" & Request.form("lname")& if

' -- Our SQL Statement
strSQL = "SELECT * FROM Data " & searchstr
' -- Populate our Recordset with data
set objRS = objConn.Execute (strSQL)


if (objRS.BOF and objRS.EOF) then
	response.write "No records found"
	response.end
End if
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<!--#include file="header.asp" --> 
<!--#include file="peopleheader.asp" -->
<BR><BR>

<HTML>
<HEAD>
	<TITLE>Testing</TITLE>
</HEAD>

<BODY>
Response.Write "<p>"&searchstr&"</p>"
<TABLE BORDER="1" CELLPADDING="2" CELLSPACING="1" WIDTH="100%">
<%
	' -- Output the Field Names as the first row in the table
	Response.Write "<TR BGCOLOR=""#66cccc"">"
	Response.Write "<TH>ID</TH><TH>Name</TH><TH>Surname</TH><TH>Job Title</TH><TH>Department</TH><TH>Manager</TH><TH>Location</TH><TH>Office Phone</TH><TH>Skype</TH><TH>Office Fax</TH><TH>Mobile</TH><TH>Room</TH><TH>Building</TH>"

	Response.write "</TR>"
	' -- Now output the contents of the Recordset
	objRS.MoveFirst
	Do While Not objRS.EOF
		' -- output the contents
		Response.Write "<TR>"
		For i = 0 to objRS.Fields.Count - 1
			Response.Write "<TD><FONT FACE=""ARIAL"" SIZE=""1"">" & objRS.Fields(i) & "</FONT></TD>"

		Next

		Response.write "</TR>"

		' -- move to the next record
		objRS.MoveNext

	Loop


	objRS.Close
	set objRS = Nothing
	objConn.Close
	set objConn = Nothing




 %>
</TABLE>


</BODY>
</HTML>

<!--#include file="footer.asp" -->

and here's the code for the form on main.asp that passes in a surname (testing it just with a surname to begin with) to search.asp

<FORM NAME=Form1 METHOD=Post ACTION="search.asp">;INPUT name=firstorlast type=radio VALUE="known_as">First Name
<INPUT name=firstorlast type=radio VALUE="surname">Surname
<INPUT name=firstorlast type=radio checked="Yes" VALUE="ID">ID

<INPUT TYPE=Text NAME=lname>  <INPUT TYPE=Submit VALUE="Search">
</form>

Thank you very much for your help Antaris :)

  • 0

The problem I'm encountering now is when I try to convert some fields into URLs. I use the following code (for example) :

Response.Write "<TD>" & "<a href=department.asp?id=" & objRS.Fields("DEPARTMENT") &  "'></a>" & "</TD>"

but instead it shows just white table cells and the rest of them show ok. I can't think of a place where I've gone wrong...

  • 0
  Antaris said:
You're not putting any content in the anchor tag:

<a href="{url}">{text}</a>

Thank you, here's the revised code, but using that code shifts the data cells by one to the right and the department cell remains white:

"<TD>" & "<a href=department.asp?id=" & objRS.Fields("DEPARTMENT") &  ">" & objRS.Fields("DEPARTMENT") & "</a>" & "</TD>"

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Posts

    • Men are real idiots justifying using any nuclear arms.  
    • OBS Studio 31.1.0 RC1 by Razvan Serea OBS Studio is software designed for capturing, compositing, encoding, recording, and streaming video content, efficiently. It is the re-write of the widely used Open Broadcaster Software, to allow even more features and multi-platform support. OBS Studio supports multiple sources, including media files, games, web pages, application windows, webcams, your desktop, microphone and more. OBS Studio Features: High performance real time video/audio capturing and mixing, with unlimited scenes you can switch between seamlessly via custom transitions. Live streaming to Twitch, YouTube, Periscope, Mixer, GoodGame, DailyMotion, Hitbox, VK and any other RTMP server Filters for video sources such as image masking, color correction, chroma/color keying, and more. x264, H.264 and AAC for your live streams and video recordings Intel Quick Sync Video (QSV) and NVIDIA NVENC support Intuitive audio mixer with per-source filters such as noise gate, noise suppression, and gain. Take full control with VST plugin support. GPU-based game capture for high performance game streaming Unlimited number of scenes and sources Number of different and customizable transitions for when you switch between scenes Hotkeys for almost any action such as start or stop your stream or recording, push-to-talk, fast mute of any audio source, show or hide any video source, switch between scenes,and much more Live preview of any changes on your scenes and sources using Studio Mode before pushing them to your stream where your viewers will see those changes DirectShow capture device support (webcams, capture cards, etc) Powerful and easy to use configuration options. Add new Sources, duplicate existing ones, and adjust their properties effortlessly. Streamlined Settings panel for quickly configuring your broadcasts and recordings. Switch between different profiles with ease. Light and dark themes available to fit your environment. …and many other features. For free. At all. OBS Studio 31.1.0 RC1 changelog: Fixed an issue where a Browser Source or Browser Dock would crash OBS Studio on macOS 13 or older [jcm93/PatTheMav/RytoEX] Fixed an issue where browser error pages could not scroll [WizardCM] Fixed an issue on macOS where menu items would launch unintended actions when OBS was set to certain languages [gxalpha] Fixed an issue in Beta 1-2 where the group icon in the Sources list was not positioned correctly in the System theme [shiina424] Fixed an issue in Beta 2 where the preview zoom button tooltip translations were incorrect [shiina424] Download: OBS Studio 31.1.0 RC1 | Portable | ARM64 | ~200.0 MB (Open Source) View: OBS Studio Homepage | Other Operating Systems | Screenshot Get alerted to all of our Software updates on Twitter at @NeowinSoftware
    • Nice little improvement One improvement I would like to see. Being able to use a voice commands in Firefox "Firefox, 2FA (enter a 2FA code)". "Firefox, Close all tabs right" "Firefox, Pin tab" "Firefox, Bookmarks (name of Bookmark to open a Bookmark)" "Firefox, Settings, X" among others.
    • Microsoft Defender XDR gets TITAN-powered Security Copilot recommendations by Paul Hill Guided Response, a Copilot-powered capability in Microsoft Defender XDR that guides analysts through step-by-step investigation and response flows, is getting a big upgrade with the introduction of TITAN recommendations. With TITAN, Microsoft wants to give security analysts real-time, threat-intel-driven recommendations so they can better prepare against attacks, before they even happen. TITAN is an adaptive threat intelligence graph that uses data from first and third-party telemetry and employs guilt-by-association techniques to warn analysts about unknown IP addresses that could pose a threat, due to their association with known malicious addresses. The primary benefit of TITAN is that security analysts get faster warnings about potential threats before they even have a chance to cause a problem. TITAN is an enhancement of Security Copilot Guided Response, rather than a replacement to it. With this extra tool, security analysts will be able to better keep up with evolving threats. Understanding TITAN's AI-powered threat intelligence The Redmond giant said that TITAN “represents a new wave of innovation” built upon its threat intelligence capabilities that introduces a real-time, adaptive threat intelligence graph. It takes telemetry from first and third-party sources such as Microsoft Defender for Threat Intelligence, Microsoft Defender for Experts, and customer feedback. The graph uses guilt-by-association techniques to mark unknown devices as threats, if they’re associated with known malicious entities. This gives security analysts a window of opportunity to take action and prevent harm. To identify potential threats, Microsoft uses a semi-supervised label propagation technique that assigns reputation scores to nodes based on the score of their neighbors. These reputation scores allow Microsoft’s unified security operation platform to implement containment and remediation actions via attack disruption. Practical impact and future outlook The new TITAN suggestion now appears within Guided Response as triage and containment recommendations. When a suspicious IP is detected, a Guided Response recommendation is automatically generated. These can help security analysts deal with various threats including IP addresses, IP ranges, and email senders. Microsoft said in early testing its TITAN recommendations have shown good results. TITAN boosted Guided Response triage accuracy by 8%, it reduced the time needed to investigate and respond to incidents, and its explainable recommendations gave analysts more confidence in the actions they take. As threats become more sophisticated, Microsoft’s TITAN will help to tackle threats before they even become an issue.
    • China wants the tech... if they were to invade, TSMC would destroy it's fabs and other critical information first. Plus, you can bet they have backups stored NOT in Taiwan.
  • Recent Achievements

    • Enthusiast
      Motoman26 went up a rank
      Enthusiast
    • Mentor
      M. Murcek went up a rank
      Mentor
    • Explorer
      treker_ed went up a rank
      Explorer
    • Apprentice
      CHUNWEI went up a rank
      Apprentice
    • Veteran
      1337ish went up a rank
      Veteran
  • Popular Contributors

    1. 1
      +primortal
      677
    2. 2
      ATLien_0
      267
    3. 3
      Michael Scrip
      177
    4. 4
      +FloatingFatMan
      176
    5. 5
      Steven P.
      139
  • Tell a friend

    Love Neowin? Tell a friend!