Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server ASP
|
SQL Server ASP Discussions about ASP programming with Microsoft's SQL Server. For more ASP forums, see the ASP forum category.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server ASP section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 28th, 2004, 09:30 PM
Registered User
 
Join Date: Sep 2004
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default data not being displayed

Hi,

newbie here, I have done a fair bit of programming with ASP and Access and I am just starting to get my head round SQL server. I have WinXPPro and am running SQL server on my computer and I also have access to an online webserver with Shared SQL space. I have set up a SQL database with a number of tables one of which is a list of companies and I am having the following problem both on my computer and online.

If I run SQL Query analyser with the following query:

select * from consultants

I get all the records with all the corresponding data, but when I run it using the following page:


_____________________________________________



<%@ LANGUAGE="VBSCRIPT" %>

<html>
<head>
<title></title>
</head>

<body bgcolor="#ffffff">
  <table border="0" cellspacing="2" cellpadding="0">
    <tr>
      <td colspan="2">
        <table cellpadding="2" cellspacing="1">
          <tr>
            <td bgcolor="#cccccc" colspan="15" class="small"><b>List:</b></td>
          </tr>
<tr>
<td valign="top" bgcolor="#cccccc" class="small"><b>ID</b></td>
<td valign="top" bgcolor="#cccccc" class="small"><b>Company</b></td>
<td valign="top" bgcolor="#cccccc" class="small"><b>Name</b></td>
<td valign="top" bgcolor="#cccccc" class="small"><b>type</b></td>
<td valign="top" bgcolor="#cccccc" class="small"><b>days</b></td>
<td valign="top" bgcolor="#cccccc" class="small"><b>Phone</b></td>
<td valign="top" bgcolor="#cccccc" class="small"><b>Fax</b></td>
<td valign="top" bgcolor="#cccccc" class="small"><b>Email</b></td>
<td valign="top" bgcolor="#cccccc" class="small"><b>Domain</b></td>
<td valign="top" bgcolor="#cccccc" class="small"><b>Password</b></td>
<td valign="top" bgcolor="#cccccc" class="small"><b>Notes</b></td>
<td valign="top" bgcolor="#cccccc" class="small"><b>number</b></td>
<td valign="top" bgcolor="#cccccc" class="small"><b>Block</b></td>
</tr>
<%



Set conn = Server.CreateObject("ADODB.Connection")
strconn = "DATABASE=company;DSN=whatever;User ID=sa;Password=admin;"
conn.Open strconn



  strSQL = "SELECT * FROM consultants ORDER BY company"
  Set rs = Server.CreateObject("ADODB.Recordset")


  rs.Open strSQL, conn
  While Not rs.EOF

Response.write "<tr>" & VbCrLf
Response.write "<td valign=""top"" bgcolor=""#e5e5e5"" class=""small"">" & rs("companyID") & "</td>" & VbCrLf
Response.write "<td valign=""top"" bgcolor=""#e5e5e5"" class=""small"">" & rs("company") & "</td>" & VbCrLf
Response.write "<td valign=""top"" bgcolor=""#e5e5e5"" class=""small"">" & rs("name") & "</td>" & VbCrLf
Response.write "<td valign=""top"" bgcolor=""#e5e5e5"" class=""small"">" & rs("type") & "</td>" & VbCrLf
Response.write "<td valign=""top"" bgcolor=""#e5e5e5"" class=""small"">" & rs("days") & "</td>" & VbCrLf
Response.write "<td valign=""top"" bgcolor=""#e5e5e5"" class=""small"">" & rs("phone") & "</td>" & VbCrLf
Response.write "<td valign=""top"" bgcolor=""#e5e5e5"" class=""small"">" & rs("fax") & "</td>" & VbCrLf
Response.write "<td valign=""top"" bgcolor=""#e5e5e5"" class=""small"">" & rs("email") & "</td>" & VbCrLf
Response.write "<td valign=""top"" bgcolor=""#e5e5e5"" class=""small"">" & rs("domain") & "</td>" & VbCrLf
Response.write "<td valign=""top"" bgcolor=""#e5e5e5"" class=""small"">" & rs("password") & "</td>" & VbCrLf
Response.write "<td valign=""top"" bgcolor=""#e5e5e5"" class=""small"">" & rs("notes") & "</td>" & VbCrLf
Response.write "<td valign=""top"" bgcolor=""#e5e5e5"" class=""small"">" & rs("number") & "</td>" & VbCrLf
Response.write "<td valign=""top"" bgcolor=""#e5e5e5"" class=""small"">" & rs("block") & "</td>" & VbCrLf
Response.write "</tr>" & VbCrLf
    rs.MoveNext
  Wend
  rs.Close
  Set rs = Nothing
%>

        </table>
      </td>
    </tr>
  </table>


<%
  conn.Close
  Set conn = Nothing
%>
</BODY>
</HTML>

_________________________________


When this page runs I get all the data but not the information in the Password, Domain or notes fields. These fields are of a similar format to the other fields and they are uniquely named.
I just come up with blanks. I have been fiddling for ages and its probably something very simple.

Help help.

Look forward to your responses.

Thanks.

 
Old September 29th, 2004, 12:27 AM
Friend of Wrox
 
Join Date: Oct 2003
Posts: 463
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to madhukp
Default

I assume you have done the following checks.

1) Are there any data in the domain, password and notes fields ? (Some error in the insert statement might have caused these fields to be empty).

2) You are not using some dummy column names. Means, you might have changed database fields many a time and some old fields may be remaining there. They may not contain any data.

If yes, please check whether there are any fields of type "text". If yes, select * will show some unexpected results. Try using explicit field names in select statement. ie. use

select [companyID], [company], [name], [type], [days], [phone], [fax], [email], [domain], [password], [notes], [number], [block] from consultants order by [company]
 
Old September 29th, 2004, 01:55 AM
joefawcett's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

If you have any text or ntext fields make sure they are referenced last in the query, or store the value in a variable and use that.

--

Joe
 
Old September 29th, 2004, 03:01 AM
Registered User
 
Join Date: Sep 2004
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi madhukp and joefawcett.

Thanks for your help, I double checked the field names - all correct. There is data in the fields (some but not all, some are nulls). There are no dummy column names.

So i wrote the select statement complety as you suggested:

select [companyID], [company], [name], [type], [days], [phone], [fax], [email], [domain], [password], [notes], [number], [block] from consultants order by [company]

 and it worked?! - great by why? Not sure why as I thought that the select * should get all the information.

Why does text or ntext throw up unexpected results?

I know that this goes beyond the initial posting, but I thought that it would be interesting to find out - particularly if there is a short answer.

Thanks,

slightly more sane coder


 
Old September 29th, 2004, 03:20 AM
Friend of Wrox
 
Join Date: Oct 2003
Posts: 463
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to madhukp
Default

I don't know the exact reason. I think data is stored in binary format in these type fields (text, ntext, image). This may be the reason. To know the complete reason, we should know the internals of SQL Server. May be somebody else can tell us the exact reason for the strange behaviour when some fields are of this type.

Another interesting and similar behaviour is shown by MS access which is relevant here. If you have a table with a memo field and you write a select * query (or select query with this memo field), we can access it only once through ADO record set. i.e. When we first access rs("memofield"), it will return correct value. But a second call will yield null value.

The following check will fail.

if(not(isnull(rs("memofield")))) then
    strDescription=replace(rs("memofield"), vbcrlf, "<br")
end if

In this piece of script, the second call of memo field (rs("memofield")) will always yield null value and it will create a null parameter error to replace function.
 
Old September 29th, 2004, 10:12 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

I posted a link here on another topic that explains about that. But I spent long time searching for that in vain. If you are lucky enough you can search that using search utility. If I find that in anyways let me post that for you here.

Cheers!

_________________________
- Vijay G
Strive for Perfection
 
Old November 22nd, 2005, 09:59 AM
Registered User
 
Join Date: Nov 2005
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by joefawcett
 If you have any text or ntext fields make sure they are referenced last in the query, or store the value in a variable and use that.

--

Joe
 
Old November 22nd, 2005, 10:00 AM
Registered User
 
Join Date: Nov 2005
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,

I'm a newbie in SQL Server, but I've read that with these data types (ntext, text...LOBs -Large Objects-) what gets really stored in the fields is a 16 bytes reference to a page where the real data is stored, which might be the cause for this problem...

Anyway, thanks joefawcett, you were pretty helpful to me ;-D

saludos

 
Old March 27th, 2006, 03:36 AM
Registered User
 
Join Date: Mar 2006
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi i was in the same scenario as goingmad but with SQL Server 2005 Express Edition and ASP VBScript (SBS 2003 SP1 and IIS 6.0).

I converted my Access .mdb file to SQL .mdf and the columns i had problems with had been converted to datatype nVarChar(255). I started by changing the datatype to VarCar and Char but didn't help.

I changed the connection back to access and all values displayed properly. So i thought it must be my connection. I used a DNS connection and then thought about changing to DNS-less connection.

I started to search the web for ways to connect SQL server and found a good website http://www.connectionstrings.com/ I tried to connect with the SQL Native Client ODBC driver. Still same display problem.

I then tried SQL Native Client OLE DB Provider and BINGO!!!






Similar Threads
Thread Thread Starter Forum Replies Last Post
Image Not Being Displayed Manoj Bisht ASP.NET 2.0 Professional 1 March 19th, 2008 01:38 AM
The page cannot be displayed malachany Dreamweaver (all versions) 10 December 12th, 2004 02:36 PM
Not getting "time" displayed in the first example dotnetabhi VS.NET 2002/2003 6 March 27th, 2004 11:53 AM
Program works, but no data being displayed bebe XML 0 October 14th, 2003 01:44 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.