• 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

    • Elden Ring Nightreign's first update targets solo runs to reduce difficulty by Pulasthi Ariyasinghe FromSoftware released its first multiplayer-centric game last week, letting players jump into a variant of its Elden Ring universe but with friends. The three-player cooperative title, Nightreign, does offer players a separate option to try their luck at beating its levels solo. Following feedback, it seems the studio has realized the mode needs some tweaking. Today, May 2, FromSoftware released patch 1.01.1 to Elden Ring Nightreign, bringing with it changes to the roguelike's balance, as well as more than a few bug fixes. For single-player players exclusively, FromSoftware has added the effect "Automatic Revival Upon Defeat." This will get the player back into the action exactly once for free whenever they are fighting a night boss battle. It should also stack with the reviving item that's already available to find during runs. Moreover, single-player runs will give out more runes when defeating enemies, making the level-up process easier. This is an important change considering that with three players, the squad can split up to farm the level-up currency and clear out camps much faster than when playing alone. Other changes in this patch include higher drops of high-rarity Relics from surviving till Day 3, as well as when purchasing random relics from the Small Jar Bazaar at Roundtable Hold. Some important bug fixes are here too, including camera fixes for Guardian's Ultimate Art, Raider dealing more than intended damage, issues with the climbing up action, and much more. Find the complete changelog for patch 1.01.1 over here. Elden Ring Nightreign had a massive launch last week, going on to sell two million copies of the game at launch. While not as spectacular as the original Elden Ring, we found Nightreign to be a marvelous action game that never lets up on the accelerator. Read the complete review from Neowin here. Elden Ring Nightreign is now available on PC, Xbox One, Xbox Series X|S, PlayStation 4, and PlayStation 5.
    • Just because Windows 10 will stop receiving updates doesn't mean your Windows 10 installed there will stop working. There are people with iPhone 8/9/10 that are still working, even without updates. There are people with Android 8/9/10 that are still working even without receiving any updates for over 5 years. There are people with Windows 7 and 8 that are still working for over 10 years. And most people install Windows and disable updates, so there are still a lot of people running Windows 10 without improvements and security updates for over 10 years.
    • 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.
  • 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!