data not being displayed

goingmad · September 28th, 2004, 09:30 PM

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">List:</td>
 </tr>
<tr>
<td valign="top" bgcolor="#cccccc" class="small">ID</td>
<td valign="top" bgcolor="#cccccc" class="small">Company</td>
<td valign="top" bgcolor="#cccccc" class="small">Name</td>
<td valign="top" bgcolor="#cccccc" class="small">type</td>
<td valign="top" bgcolor="#cccccc" class="small">days</td>
<td valign="top" bgcolor="#cccccc" class="small">Phone</td>
<td valign="top" bgcolor="#cccccc" class="small">Fax</td>
<td valign="top" bgcolor="#cccccc" class="small">Email</td>
<td valign="top" bgcolor="#cccccc" class="small">Domain</td>
<td valign="top" bgcolor="#cccccc" class="small">Password</td>
<td valign="top" bgcolor="#cccccc" class="small">Notes</td>
<td valign="top" bgcolor="#cccccc" class="small">number</td>
<td valign="top" bgcolor="#cccccc" class="small">Block</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.

madhukp · September 29th, 2004, 12:27 AM

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]

joefawcett · September 29th, 2004, 01:55 AM

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

goingmad · September 29th, 2004, 03:01 AM

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

madhukp · September 29th, 2004, 03:20 AM

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.

happygv · September 29th, 2004, 10:12 AM

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

Migueberia · November 22nd, 2005, 09:59 AM

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

Migueberia · November 22nd, 2005, 10:00 AM

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

mhallin · March 27th, 2006, 03:36 AM

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