Some data not showing in a form

[Joey Martin]

I posted a message earlier, but I have more information to go from so I
wanted to start a new post since the first is not really valid.

I am using SQL 2000 database and MS Server 2003.

I am pulling in data from a sql table. For some reason, when I include
the following line:
<tr><td><b><%if rs("Street_dir")<> "" then response.write
rs("Street_dir") & "&nbsp;" end if%>
<%=rs("Street_num")%>&nbsp;<%=rs("Street_name")%>&nbsp;<%=rs("Street_suf
fix")%></b></td></tr>

then the following line does not display:

<tr><td><%=rs("remarks")%></td></tr>
The REMARKS data is blank. But, if I remove the upper address line, then
the REMARKS appears. The address is 1111 Main so that does not seem to
be the issue. The address information is VARCHAR fields, 255 characters.
This works fine on another server where my files used to be, but I just
moved to a new server.

Note: This is happening in other places within the HTML table. Other
items are not showing too.

 

[Joey Martin]

Ok, so I figured this out. I was using a DSN to connect to the database
versus connecting directly to the server. Any idea why the DSN would
mess this up??

 

[Bob Barrows [MVP]]

Ok, so I figured this out. I was using a DSN to connect to the
database versus connecting directly to the server. Any idea why the
DSN would mess this up??

Sounds like an old ODBC bug with handling TEXT data. Scitch to using the OLE
DB provider:
http://www.aspfaq.com/show.asp?id=2126

 

[Larry Bud]

I posted a message earlier, but I have more information to go from so I
wanted to start a new post since the first is not really valid.

I am using SQL 2000 database and MS Server 2003.

I am pulling in data from a sql table. For some reason, when I include
the following line:
<tr><td><b><%if rs("Street_dir")<> "" then response.write
rs("Street_dir") & "&nbsp;" end if%>
<%=rs("Street_num")%>&nbsp;<%=rs("Street_name")%>&nbsp;<%=rs("Street_suf
fix")%></b></td></tr>

then the following line does not display:

<tr><td><%=rs("remarks")%></td></tr>
The REMARKS data is blank. But, if I remove the upper address line, then
the REMARKS appears. The address is 1111 Main so that does not seem to
be the issue. The address information is VARCHAR fields, 255 characters.
This works fine on another server where my files used to be, but I just
moved to a new server.

Note: This is happening in other places within the HTML table. Other
items are not showing too.

Is "remarks" a text field? In your SQL Select statement, you need to
put any text columns last, otherwise you could end up with blank
fields. If you're using select * and the text column is not listed
last in the table or view, the same thing would happen.

http://support.microsoft.com/kb/175239/en-us

 

[Bob Barrows [MVP]]

Is "remarks" a text field? In your SQL Select statement, you need to
put any text columns last,

.... or switch to the SQLOLEDB provider which does not have this bug.

 

[Larry Bud]

... or switch to the SQLOLEDB provider which does not have this bug.

Bob, I've got the SQLOLEDB.dll file on my system, but how do I create a
DSN using it? It doesn't seem to show up in the list. Windows 2k
server.

 

[Bob Barrows [MVP]]

Bob, I've got the SQLOLEDB.dll file on my system, but how do I create
a DSN using it?

You don't. You use the word "SQLOLEDB" in a connection string to specify the
name of the Provider to use to create the connection.
http://www.aspfaq.com/show.asp?id=2126

It's an OLE DB provider, which replaces the obsolete ODBC and DSNs.