[VBScript] Displaying SQL query results from a table

2,022 · April 23, 2009

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.

&lt;%

&lt;!--#include file="header.asp" --&gt; 
&lt;!--#include file="peopleheader.asp" --&gt;
&lt;BR&gt;&lt;br&gt;

&lt;%   
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 "&lt;TABLE BORDER='1' CELLPADDING='2' CELLSPACING='1' WIDTH='100%'&gt;"

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

	response.write "&lt;/table&gt;"

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


%&gt;
&lt;!--#include file="footer.asp" --&gt;
%&gt;

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

Edited April 23, 2009 by vanx
Fixed thread title

5,526 · April 24, 2009

Have you disabled friendly error messages in options? We need to know the exact error message its spitting out.

2,022 · April 24, 2009

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:

&lt;!--#include file="header.asp" --&gt; 
&lt;!--#include file="peopleheader.asp" --&gt;
&lt;br&gt;&lt;br&gt;

&lt;%   
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 "&amp;request.form("firstorlast")& &amp; Request.form("lname")& if

' -- Our SQL Statement
strSQL = "SELECT * FROM Data" &amp; 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
%&gt;
&lt;!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"&gt;
&lt;!--#include file="header.asp" --&gt; 
&lt;!--#include file="peopleheader.asp" --&gt;
&lt;BR&gt;&lt;BR&gt;

&lt;HTML&gt;
&lt;HEAD&gt;
	&lt;TITLE&gt;Testing&lt;/TITLE&gt;
&lt;/HEAD&gt;

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

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

		Next

		Response.write "&lt;/TR&gt;"

		' -- move to the next record
		objRS.MoveNext

	Loop


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




 %&gt;
&lt;/TABLE&gt;


&lt;/BODY&gt;
&lt;/HTML&gt;

&lt;!--#include file="footer.asp" --&gt;

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!

5,526 · April 24, 2009

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

and

strSQL = "SELECT * FROM Data" &amp; searchstr

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

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

2,022 · April 24, 2009

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.

&lt;!--#include file="header.asp" --&gt; 
&lt;!--#include file="peopleheader.asp" --&gt;
&lt;br&gt;&lt;br&gt;

&lt;%   
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 '" &amp; Request.form("lname")& if

' -- Our SQL Statement
strSQL = "SELECT * FROM Data " &amp; 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
%&gt;
&lt;!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"&gt;
&lt;!--#include file="header.asp" --&gt; 
&lt;!--#include file="peopleheader.asp" --&gt;
&lt;BR&gt;&lt;BR&gt;

&lt;HTML&gt;
&lt;HEAD&gt;
	&lt;TITLE&gt;Testing&lt;/TITLE&gt;
&lt;/HEAD&gt;

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

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

		Next

		Response.write "&lt;/TR&gt;"

		' -- move to the next record
		objRS.MoveNext

	Loop


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




 %&gt;
&lt;/TABLE&gt;


&lt;/BODY&gt;
&lt;/HTML&gt;

&lt;!--#include file="footer.asp" --&gt;

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

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

&lt;INPUT TYPE=Text NAME=lname&gt;  &lt;INPUT TYPE=Submit VALUE="Search"&gt;
&lt;/form&gt;

Thank you very much for your help Antaris :)

5,526 · April 25, 2009

Remove the Option Explicit at the beginning of the file and see if that makes a difference.

2,022 · April 25, 2009

Antaris said:
Remove the Option Explicit at the beginning of the file and see if that makes a difference.

Yes!!! Thank you so much for your help, now I can make the search work properly :)

2,022 · April 25, 2009

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 "&lt;TD&gt;" &amp; "&lt;a href=department.asp?id=" &amp; objRS.Fields("DEPARTMENT") &amp;  "'&gt;&lt;/a&gt;" &amp; "&lt;/TD&gt;"

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...

5,526 · April 25, 2009

You're not putting any content in the anchor tag:

&lt;a href="{url}"&gt;{text}&lt;/a&gt;

2,022 · April 25, 2009

Antaris said:
You're not putting any content in the anchor tag:
&lt;a href="{url}"&gt;{text}&lt;/a&gt;

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:

"&lt;TD&gt;" &amp; "&lt;a href=department.asp?id=" &amp; objRS.Fields("DEPARTMENT") &amp;  "&gt;" &amp; objRS.Fields("DEPARTMENT") &amp; "&lt;/a&gt;" &amp; "&lt;/TD&gt;"

Sign In

[VBScript] Displaying SQL query results from a table

Question

vanx

Link to comment

Share on other sites

9 answers to this question

Recommended Posts

Antaris Veteran

Link to comment

Share on other sites

vanx

Link to comment

Share on other sites

Antaris Veteran

Link to comment

Share on other sites

vanx

Link to comment

Share on other sites

Antaris Veteran

Link to comment

Share on other sites

vanx

Link to comment

Share on other sites

vanx

Link to comment

Share on other sites

Antaris Veteran

Link to comment

Share on other sites

vanx

Link to comment

Share on other sites

Recently Browsing 0 members

Posts

Recent Achievements

Popular Contributors

Tell a friend