• 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

    • Windows 10 EOL is coming, so how many is “forced”? (to be prepared and not wait until the last moment in October?)
    • I guess more Windows users are switching on their desktops/laptops than people buying new Steam Decks. I wouldn't be surprised. Deck doesn't have the mainstream appeal of other handhelds, usually people who would want one already have one. Glad to see Linux go up!
    • Any news about Windows and Microsoft always brings up crazy people saying; - Windows 11 is terrible, it destroys my hardware, it had sex with my wife, it told the FBI that I visit shemale sites, it collects my data where I get illegal anime. Never use Windows 11, go for Linux and it will never have sex with your wife.
    • QOwnNotes 25.6.0 by Razvan Serea QOwnNotes is a open source (GPL) plain-text file notepad with markdown support and todo list manager for GNU/Linux, Mac OS X and Windows, that (optionally) works together with the notes application of ownCloud (or Nextcloud). So you are able to write down your thoughts with QOwnNotes and edit or search for them later from your mobile device (like with CloudNotes) or the ownCloud web-service. The notes are stored as plain text files and you can sync them with your ownCloud sync client. Of course other software, like Dropbox, Syncthing, Seafile or BitTorrent Sync can be used too. Features: the notes folder can be freely chosen (multiple note folders can be used) sub-string searching of notes is possible and search results are highlighted in the notes application can be operated with customizable keyboard shortcuts external changes of note files are watched (notes or note list are reloaded) older versions of your notes can be restored from your ownCloud server trashed notes can be restored from your ownCloud server differences between current note and externally changed note are showed in a dialog markdown highlighting of notes and a markdown preview mode notes are getting their name from the first line of the note text (just like in the ownCloud notes web-application) and the note text files are automatically renamed, if the the first line changes compatible with the notes web-application of ownCloud and mobile ownCloud notes applications compatible with ownCloud's selective sync feature by supporting an unlimited amount of note folders with the ability to choose the respective folder on your server manage your ownCloud todo lists (ownCloud tasks or Tasks Plus / Calendar Plus) or use an other CalDAV server to sync your tasks to encryption of notes (AES-256 is built in or you can use custom encryption methods like Keybase.io (encryption-keybase.qml) or PGP (encryption-pgp.qml)) dark mode theme support theming support for the markdown syntax highlighting all panels can be placed wherever you want, they can even float or stack (fully dockable) support for freedesktop theme icons, you can use QOwnNotes with your native desktop icons and with your favorite dark desktop theme support for hierarchical note tagging and note subfolders support for sharing notes on your ownCloud server portable mode for carrying QOwnNotes around on USB sticks Evernote import QOwnNotes is available in many different languages like English, German, French, Polish, Chinese, Japanese, Russian, Portuguese, Hungarian, Dutch and Spanish Changes in QOwnNotes 25.6.0: QOwnNotes now builds with with Botan 3, when built with the system Botan library (cmake build parameter -DBUILD_WITH_SYSTEM_BOTAN=ON), because of the end of life of Botan 2 (for #2786) Keep in mind that Botan 3 needs C++20, which causes issues on Qt5 QMake and Qt5 will still use the internal Botan 2 amalgamation Creating an amalgamation Botan 3 did work, but caused major issues with various build processes The filename in checksum files of the Qt6 AppImages in the releases on GitHub was fixed (for #3286) Download: QOwnNotes 25.6.0 | 37.3 MB (Open Source) Download: QOwnNotes for Other Operating Systems View: QOwnNotes Home Page | Screenshot Get alerted to all of our Software updates on Twitter at @NeowinSoftware
    • There are lots of older PC's that are now going to be unsupported on W10, but for modern systems, you should be able to update to W11. Even so, there are ways to bypass the official hardware requirements as well as the MSA (which imo is the bigger annoyance). My only gripe with W11 is all the telemetry collection / bloatware - yes, it is a problem compared to W10 and Linux.
  • Recent Achievements

    • Week One Done
      Epaminombas earned a badge
      Week One Done
    • Week One Done
      Prestige Podiatry Care earned a badge
      Week One Done
    • Week One Done
      rollconults earned a badge
      Week One Done
    • One Month Later
      lilred1938 earned a badge
      One Month Later
    • Week One Done
      lilred1938 earned a badge
      Week One Done
  • Popular Contributors

    1. 1
      +primortal
      148
    2. 2
      Xenon
      132
    3. 3
      ATLien_0
      123
    4. 4
      +Edouard
      103
    5. 5
      snowy owl
      96
  • Tell a friend

    Love Neowin? Tell a friend!