MSSQL table - cannot display all of the data?

Forum|Forum|16 years ago
September 3, 2009
1 reply
827 views

Hi
I'll try and explain this best I can, please bear wth me as I'm a newbie with SQL database's.

I have set up a test table on an MSSQL DB (called test) and most of the fields are 'text' type (with 5 'int' type and 2 'Date' types) , I'm trying to display all data in any order on a page using ASP VBSCRIPT.

The problem:
If I create a recordset using the SQL statement ' SELECT * FROM dbo.test ' and bind the data to the page, I can only display all of the records if they are listed on the page in the exact same order as the recordset.
If I move the bindings around on the page most of the data is missing.

Examples:

Bindings listed in same order as RS - http://www.fuerteventura.com/dbtest.asp

2 or 3 Bindings listed in different order as RS - http://www.fuerteventura.com/dbtestzz.asp

Can anyone give me any pointers so I can freely list the data in any order on the page?
Many thanks
Gary

Server side applications

This topic has been closed for replies.

B

bregent

Participating Frequently

>I can only display all of the records if they are listed

>on the page in the exact same order as the recordset.

I think you mean 'all of the fields', not records. In any case, you should be able to display the fields in any order. We will probably need to see the underlying ASP code to determine what is wrong.

A

Anonymous

Hi

Thanks for the swift response. It's a pretty straight forward basic test page really.

A screen grab of the layout in design view in dreamweaver is here (as you can see the fields on the page are in a different order to the recordset) which seems to be the reason the data is not being displayed..

I also had problems before because some of the fields were 'varchar' so I converted the majority to 'text' to no avail.

Actual code is a as follows:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>

<%
Dim Recordset1
Dim Recordset1_cmd
Dim Recordset1_numRows

Set Recordset1_cmd = Server.CreateObject ("ADODB.Command")
Recordset1_cmd.ActiveConnection = MM_sql_STRING
Recordset1_cmd.CommandText = "SELECT * FROM dbo.test"
Recordset1_cmd.Prepared = true

Set Recordset1 = Recordset1_cmd.Execute
Recordset1_numRows = 0
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<table width="100%" border="1">
<tr>
 <td width="21%">Area</td>
 <td width="79%"><%=(Recordset1.Fields.Item("Area").Value)%></td>
</tr>
<tr>
 <td>name</td>
 <td><%=(Recordset1.Fields.Item("name").Value)%></td>
</tr>
<tr>
 <td>ID</td>
 <td><%=(Recordset1.Fields.Item("ID").Value)%></td>
</tr>
<tr>
 <td>filename</td>
 <td><%=(Recordset1.Fields.Item("filename").Value)%></td>
</tr>
<tr>
 <td>rooms</td>
 <td><%=(Recordset1.Fields.Item("rooms").Value)%></td>
</tr>
<tr>
 <td>password</td>
 <td><%=(Recordset1.Fields.Item("password").Value)%></td>
</tr>
<tr>
 <td>pax</td>
 <td><%=(Recordset1.Fields.Item("pax").Value)%></td>
</tr>
<tr>
 <td>high</td>
 <td><%=(Recordset1.Fields.Item("high").Value)%></td>
</tr>
<tr>
 <td>hyperlink</td>
 <td><%=(Recordset1.Fields.Item("hyperlink").Value)%></td>
</tr>
<tr>
 <td>low</td>
 <td><%=(Recordset1.Fields.Item("low").Value)%></td>
</tr>
<tr>
 <td>Date</td>
 <td><%=(Recordset1.Fields.Item("Date").Value)%></td>
</tr>
<tr>
 <td>Email</td>
 <td><%=(Recordset1.Fields.Item("Email").Value)%></td>
</tr>
<tr>
 <td>Description</td>
 <td><%=(Recordset1.Fields.Item("Description").Value)%></td>
</tr>
<tr>
 <td>username</td>
 <td><%=(Recordset1.Fields.Item("username").Value)%></td>
</tr>
<tr>
 <td>bizcategory</td>
 <td><%=(Recordset1.Fields.Item("bizcategory").Value)%></td>
</tr>
<tr>
 <td>address</td>
 <td><%=(Recordset1.Fields.Item("address").Value)%></td>
</tr>
<tr>
 <td>tel</td>
 <td><%=(Recordset1.Fields.Item("tel").Value)%></td>
</tr>
<tr>
 <td>webtext</td>
 <td><%=(Recordset1.Fields.Item("webtext").Value)%></td>
</tr>
<tr>
 <td>emailtext</td>
 <td><%=(Recordset1.Fields.Item("emailtext").Value)%></td>
</tr>
<tr>
 <td>shortdescription</td>
 <td><%=(Recordset1.Fields.Item("shortdescription").Value)%></td>
</tr>
<tr>
 <td>addressfree</td>
 <td><%=(Recordset1.Fields.Item("addressfree").Value)%></td>
</tr>
<tr>
 <td>telfree</td>
 <td><%=(Recordset1.Fields.Item("telfree").Value)%></td>
</tr>
<tr>
 <td>bizcategoryfree</td>
 <td><%=(Recordset1.Fields.Item("bizcategoryfree").Value)%></td>
</tr>
<tr>
 <td>bizcattext</td>
 <td><%=(Recordset1.Fields.Item("bizcattext").Value)%></td>
</tr>
<tr>
 <td>teltext</td>
 <td><%=(Recordset1.Fields.Item("teltext").Value)%></td>
</tr>
<tr>
 <td>addresstext</td>
 <td><%=(Recordset1.Fields.Item("addresstext").Value)%></td>
</tr>
<tr>
 <td>dash</td>
 <td><%=(Recordset1.Fields.Item("dash").Value)%></td>
</tr>
<tr>
 <td>paid</td>
 <td><%=(Recordset1.Fields.Item("paid").Value)%></td>
</tr>
<tr>
 <td>bookingform</td>
 <td><%=(Recordset1.Fields.Item("bookingform").Value)%></td>
</tr>
<tr>
 <td>availability</td>
 <td><%=(Recordset1.Fields.Item("availability").Value)%></td>
</tr>
<tr>
 <td>reciprocalurl</td>
 <td><%=(Recordset1.Fields.Item("reciprocalurl").Value)%></td>
</tr>
<tr>
 <td>namefree</td>
 <td><%=(Recordset1.Fields.Item("namefree").Value)%></td>
</tr>
<tr>
 <td>roomsfree</td>
 <td><%=(Recordset1.Fields.Item("roomsfree").Value)%></td>
</tr>
<tr>
 <td>paxfree</td>
 <td><%=(Recordset1.Fields.Item("paxfree").Value)%></td>
</tr>
<tr>
 <td>lowfree</td>
 <td><%=(Recordset1.Fields.Item("lowfree").Value)%></td>
</tr>
<tr>
 <td>highfree</td>
 <td><%=(Recordset1.Fields.Item("highfree").Value)%></td>
</tr>
<tr>
 <td>emailfree</td>
 <td><%=(Recordset1.Fields.Item("emailfree").Value)%></td>
</tr>
<tr>
 <td>checkfree</td>
 <td><%=(Recordset1.Fields.Item("checkfree").Value)%></td>
</tr>
<tr>
 <td>hyperlinkfree</td>
 <td><%=(Recordset1.Fields.Item("hyperlinkfree").Value)%></td>
</tr>
<tr>
 <td>symbol</td>
 <td><%=(Recordset1.Fields.Item("symbol").Value)%></td>
</tr>
<tr>
 <td>symbol2</td>
 <td><%=(Recordset1.Fields.Item("symbol2").Value)%></td>
</tr>
<tr>
 <td>Bizcatfree</td>
 <td><%=(Recordset1.Fields.Item("Bizcatfree").Value)%></td>
</tr>
<tr>
 <td>shortdescriptionfree_backup</td>
 <td><%=(Recordset1.Fields.Item("shortdescriptionfree_backup").Value)%></td>
</tr>
<tr>
 <td>shortdescriptionspanish</td>
 <td><%=(Recordset1.Fields.Item("shortdescriptionspanish").Value)%></td>
</tr>
<tr>
 <td>longdescriptionspanish</td>
 <td><%=(Recordset1.Fields.Item("longdescriptionspanish").Value)%></td>
</tr>
<tr>
 <td>check</td>
 <td>For some reason cannot list this field</td>
</tr>
<tr>
 <td>check2</td>
 <td><%=(Recordset1.Fields.Item("check2").Value)%></td>
</tr>
<tr>
 <td>shortdescriptionfree</td>
 <td><%=(Recordset1.Fields.Item("shortdescriptionfree").Value)%></td>
</tr>
<tr>
 <td>pphour</td>
 <td><%=(Recordset1.Fields.Item("pphour").Value)%></td>
</tr>
<tr>
 <td>Offertext</td>
 <td><%=(Recordset1.Fields.Item("Offertext").Value)%></td>
</tr>
<tr>
 <td>Offerimage</td>
 <td><%=(Recordset1.Fields.Item("Offerimage").Value)%></td>
</tr>
<tr>
 <td>Offerurl</td>
 <td><%=(Recordset1.Fields.Item("Offerurl").Value)%></td>
</tr>
<tr>
 <td>offer</td>
 <td><%=(Recordset1.Fields.Item("offer").Value)%></td>
</tr>
<tr>
 <td>offerterms</td>
 <td><%=(Recordset1.Fields.Item("offerterms").Value)%></td>
</tr>
<tr>
 <td>zoomfree</td>
 <td><%=(Recordset1.Fields.Item("zoomfree").Value)%></td>
</tr>
<tr>
 <td>maptypefree</td>
 <td><%=(Recordset1.Fields.Item("maptypefree").Value)%></td>
</tr>
<tr>
 <td>maptypefreevisible</td>
 <td><%=(Recordset1.Fields.Item("maptypefreevisible").Value)%></td>
</tr>
<tr>
 <td>masterorslave</td>
 <td><%=(Recordset1.Fields.Item("masterorslave").Value)%></td>
</tr>
<tr>
 <td>Expiry</td>
 <td><%=(Recordset1.Fields.Item("Expiry").Value)%></td>
</tr>
<tr>
 <td>multiplecategories</td>
 <td><%=(Recordset1.Fields.Item("multiplecategories").Value)%></td>
</tr>
<tr>
 <td>bannerurlsrc</td>
 <td><%=(Recordset1.Fields.Item("bannerurlsrc").Value)%></td>
</tr>
<tr>
 <td>authorphotographer</td>
 <td><%=(Recordset1.Fields.Item("authorphotographer").Value)%></td>
</tr>
<tr>
 <td>Latitude</td>
 <td><%=(Recordset1.Fields.Item("Latitude").Value)%></td>
</tr>
<tr>
 <td>Longitude</td>
 <td><%=(Recordset1.Fields.Item("Longitude").Value)%></td>
</tr>
<tr>
 <td>Super</td>
 <td><%=(Recordset1.Fields.Item("Super").Value)%></td>
</tr>
<tr>
 <td>Freebie</td>
 <td><%=(Recordset1.Fields.Item("Freebie").Value)%></td>
</tr>
<tr>
 <td>Hours</td>
 <td><%=(Recordset1.Fields.Item("Hours").Value)%></td>
</tr>
<tr>
 <td> </td>
 <td> </td>
</tr>
<tr>
 <td> </td>
 <td> </td>
</tr>
<tr>
 <td> </td>
 <td> </td>
</tr>
</table>
</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>

A

Anonymous

I can't see anything wrong. How many records do you have in the database? Is it possible that these are pulling different records? Can you post the code for the page that is working correctly?

Yup, thats whats confusing me. This is how the database looks in SQL manager - http://www.fuerteventura.com/scrap/db.jpg

There are 70 fields in total. The page that works is pulling the correct data. I just can't change the order on the page!?

The code for the page that works as follows:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>

<%
Dim Recordset1
Dim Recordset1_cmd
Dim Recordset1_numRows

Set Recordset1_cmd = Server.CreateObject ("ADODB.Command")
Recordset1_cmd.ActiveConnection = MM_sql_STRING
Recordset1_cmd.CommandText = "SELECT * FROM dbo.test"
Recordset1_cmd.Prepared = true

Set Recordset1 = Recordset1_cmd.Execute
Recordset1_numRows = 0
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<table width="100%" border="1">
<tr>
 <td width="21%">ID</td>
 <td width="79%"><%=(Recordset1.Fields.Item("ID").Value)%></td>
</tr>
<tr>
 <td>name</td>
 <td><%=(Recordset1.Fields.Item("name").Value)%></td>
</tr>
<tr>
 <td>Area</td>
 <td><%=(Recordset1.Fields.Item("Area").Value)%></td>
</tr>
<tr>
 <td>filename</td>
 <td><%=(Recordset1.Fields.Item("filename").Value)%></td>
</tr>
<tr>
 <td>rooms</td>
 <td><%=(Recordset1.Fields.Item("rooms").Value)%></td>
</tr>
<tr>
 <td>Description</td>
 <td><%=(Recordset1.Fields.Item("Description").Value)%></td>
</tr>
<tr>
 <td>pax</td>
 <td><%=(Recordset1.Fields.Item("pax").Value)%></td>
</tr>
<tr>
 <td>high</td>
 <td><%=(Recordset1.Fields.Item("high").Value)%></td>
</tr>
<tr>
 <td>hyperlink</td>
 <td><%=(Recordset1.Fields.Item("hyperlink").Value)%></td>
</tr>
<tr>
 <td>low</td>
 <td><%=(Recordset1.Fields.Item("low").Value)%></td>
</tr>
<tr>
 <td>Date</td>
 <td><%=(Recordset1.Fields.Item("Date").Value)%></td>
</tr>
<tr>
 <td>Email</td>
 <td><%=(Recordset1.Fields.Item("Email").Value)%></td>
</tr>
<tr>
 <td>password</td>
 <td><%=(Recordset1.Fields.Item("password").Value)%></td>
</tr>
<tr>
 <td>username</td>
 <td><%=(Recordset1.Fields.Item("username").Value)%></td>
</tr>
<tr>
 <td>bizcategory</td>
 <td><%=(Recordset1.Fields.Item("bizcategory").Value)%></td>
</tr>
<tr>
 <td>address</td>
 <td><%=(Recordset1.Fields.Item("address").Value)%></td>
</tr>
<tr>
 <td>tel</td>
 <td><%=(Recordset1.Fields.Item("tel").Value)%></td>
</tr>
<tr>
 <td>webtext</td>
 <td><%=(Recordset1.Fields.Item("webtext").Value)%></td>
</tr>
<tr>
 <td>emailtext</td>
 <td><%=(Recordset1.Fields.Item("emailtext").Value)%></td>
</tr>
<tr>
 <td>shortdescription</td>
 <td><%=(Recordset1.Fields.Item("shortdescription").Value)%></td>
</tr>
<tr>
 <td>addressfree</td>
 <td><%=(Recordset1.Fields.Item("addressfree").Value)%></td>
</tr>
<tr>
 <td>telfree</td>
 <td><%=(Recordset1.Fields.Item("telfree").Value)%></td>
</tr>
<tr>
 <td>bizcategoryfree</td>
 <td><%=(Recordset1.Fields.Item("bizcategoryfree").Value)%></td>
</tr>
<tr>
 <td>bizcattext</td>
 <td><%=(Recordset1.Fields.Item("bizcattext").Value)%></td>
</tr>
<tr>
 <td>teltext</td>
 <td><%=(Recordset1.Fields.Item("teltext").Value)%></td>
</tr>
<tr>
 <td>addresstext</td>
 <td><%=(Recordset1.Fields.Item("addresstext").Value)%></td>
</tr>
<tr>
 <td>dash</td>
 <td><%=(Recordset1.Fields.Item("dash").Value)%></td>
</tr>
<tr>
 <td>paid</td>
 <td><%=(Recordset1.Fields.Item("paid").Value)%></td>
</tr>
<tr>
 <td>bookingform</td>
 <td><%=(Recordset1.Fields.Item("bookingform").Value)%></td>
</tr>
<tr>
 <td>availability</td>
 <td><%=(Recordset1.Fields.Item("availability").Value)%></td>
</tr>
<tr>
 <td>reciprocalurl</td>
 <td><%=(Recordset1.Fields.Item("reciprocalurl").Value)%></td>
</tr>
<tr>
 <td>namefree</td>
 <td><%=(Recordset1.Fields.Item("namefree").Value)%></td>
</tr>
<tr>
 <td>roomsfree</td>
 <td><%=(Recordset1.Fields.Item("roomsfree").Value)%></td>
</tr>
<tr>
 <td>paxfree</td>
 <td><%=(Recordset1.Fields.Item("paxfree").Value)%></td>
</tr>
<tr>
 <td>lowfree</td>
 <td><%=(Recordset1.Fields.Item("lowfree").Value)%></td>
</tr>
<tr>
 <td>highfree</td>
 <td><%=(Recordset1.Fields.Item("highfree").Value)%></td>
</tr>
<tr>
 <td>emailfree</td>
 <td><%=(Recordset1.Fields.Item("emailfree").Value)%></td>
</tr>
<tr>
 <td>checkfree</td>
 <td><%=(Recordset1.Fields.Item("checkfree").Value)%></td>
</tr>
<tr>
 <td>hyperlinkfree</td>
 <td><%=(Recordset1.Fields.Item("hyperlinkfree").Value)%></td>
</tr>
<tr>
 <td>symbol</td>
 <td><%=(Recordset1.Fields.Item("symbol").Value)%></td>
</tr>
<tr>
 <td>symbol2</td>
 <td><%=(Recordset1.Fields.Item("symbol2").Value)%></td>
</tr>
<tr>
 <td>Bizcatfree</td>
 <td><%=(Recordset1.Fields.Item("Bizcatfree").Value)%></td>
</tr>
<tr>
 <td>shortdescriptionfree_backup</td>
 <td><%=(Recordset1.Fields.Item("shortdescriptionfree_backup").Value)%></td>
</tr>
<tr>
 <td>shortdescriptionspanish</td>
 <td><%=(Recordset1.Fields.Item("shortdescriptionspanish").Value)%></td>
</tr>
<tr>
 <td>longdescriptionspanish</td>
 <td><%=(Recordset1.Fields.Item("longdescriptionspanish").Value)%></td>
</tr>
<tr>
 <td>check</td>
 <td>For some reason cannot list this field</td>
</tr>
<tr>
 <td>check2</td>
 <td><%=(Recordset1.Fields.Item("check2").Value)%></td>
</tr>
<tr>
 <td>Hours</td>
 <td><%=(Recordset1.Fields.Item("Hours").Value)%></td>
</tr>
<tr>
 <td>pphour</td>
 <td><%=(Recordset1.Fields.Item("pphour").Value)%></td>
</tr>
<tr>
 <td>Offertext</td>
 <td><%=(Recordset1.Fields.Item("Offertext").Value)%></td>
</tr>
<tr>
 <td>Offerimage</td>
 <td><%=(Recordset1.Fields.Item("Offerimage").Value)%></td>
</tr>
<tr>
 <td>Offerurl</td>
 <td><%=(Recordset1.Fields.Item("Offerurl").Value)%></td>
</tr>
<tr>
 <td>offer</td>
 <td><%=(Recordset1.Fields.Item("offer").Value)%></td>
</tr>
<tr>
 <td>offerterms</td>
 <td><%=(Recordset1.Fields.Item("offerterms").Value)%></td>
</tr>
<tr>
 <td>zoomfree</td>
 <td><%=(Recordset1.Fields.Item("zoomfree").Value)%></td>
</tr>
<tr>
 <td>maptypefree</td>
 <td><%=(Recordset1.Fields.Item("maptypefree").Value)%></td>
</tr>
<tr>
 <td>maptypefreevisible</td>
 <td><%=(Recordset1.Fields.Item("maptypefreevisible").Value)%></td>
</tr>
<tr>
 <td>masterorslave</td>
 <td><%=(Recordset1.Fields.Item("masterorslave").Value)%></td>
</tr>
<tr>
 <td>Expiry</td>
 <td><%=(Recordset1.Fields.Item("Expiry").Value)%></td>
</tr>
<tr>
 <td>multiplecategories</td>
 <td><%=(Recordset1.Fields.Item("multiplecategories").Value)%></td>
</tr>
<tr>
 <td>bannerurlsrc</td>
 <td><%=(Recordset1.Fields.Item("bannerurlsrc").Value)%></td>
</tr>
<tr>
 <td>authorphotographer</td>
 <td><%=(Recordset1.Fields.Item("authorphotographer").Value)%></td>
</tr>
<tr>
 <td>Latitude</td>
 <td><%=(Recordset1.Fields.Item("Latitude").Value)%></td>
</tr>
<tr>
 <td>Longitude</td>
 <td><%=(Recordset1.Fields.Item("Longitude").Value)%></td>
</tr>
<tr>
 <td>Super</td>
 <td><%=(Recordset1.Fields.Item("Super").Value)%></td>
</tr>
<tr>
 <td>Freebie</td>
 <td><%=(Recordset1.Fields.Item("Freebie").Value)%></td>
</tr>
<tr>
 <td>shortdescriptionfree</td>
 <td><%=(Recordset1.Fields.Item("shortdescriptionfree").Value)%></td>
</tr>
<tr>
 <td> </td>
 <td> </td>
</tr>
<tr>
 <td> </td>
 <td> </td>
</tr>
<tr>
 <td> </td>
 <td> </td>
</tr>
</table>
</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded