ASP + SQL, please help!

Ayac · Jan 6, 2004

Hi All!

I am just trying to move our existing Access datatables to SQL and make everything work as it did before. The tables are in SQL now, but I have problems reading them out.
I can not see some of the columns, however I did not change anything. When I do this:

strQuery ="SELECT * FROM Discuss;"
Set objRS = objConn.Execute(strQuery)
While not objRS.EOF
Response.Write " " & objRS("AuthorName&quot

& " "
Response.Write " " & objRS("ID&quot

& " "
Response.Write " " & objRS("Title&quot

& " "
Response.Write " " & objRS("body&quot

& " "
Response.Write " " & objRS("AuthorCode&quot

& " "
objRS.Movenext
Wend

The body field does not show up, but if I do this

strQuery ="SELECT * FROM Discuss;"
Set objRS = objConn.Execute(strQuery)
While not objRS.EOF
Response.Write " " & objRS("body&quot

& " "
Response.Write " " & objRS("AuthorName&quot

& " "
Response.Write " " & objRS("AuthorCode&quot

& " "
objRS.Movenext
Wend

What do I do wrong?

Chopstik · Jan 6, 2004

Just a couple of things to look at. Did you test your SELECT statement in Query Analyser? If so, do you see all of the fields that you are seeking in the first version (with five fields that you are looking for)? For that matter, are you receiving results at all? Ok, I guess the short version is first test in Query Analyser and see if you are getting everything that you're expecting. Then post back with your results and we can further troubleshoot from there.

-----------------------------------------------------------------------------------------------------
"If you can't explain something to a six-year-old, you really don't understand it yourself."
-- Albert Einstein

Ayac · Jan 6, 2004

With this oreder I got all results:

Response.Write " " & objRS("body&quot

& " "
Response.Write " " & objRS("AuthorName&quot

& " "
Response.Write " " & objRS("AuthorCode&quot

& " "

With this order I do not see the "Body" field

Response.Write " " & objRS("AuthorCode&quot

& " "
Response.Write " " & objRS("body&quot

& " "
Response.Write " " & objRS("AuthorName&quot

& " "

Does order matter? It would be strange...

Chopstik · Jan 6, 2004

I am unaware of any reason why order should matter when showing your data. Based on your two posts, it appears that objRS("body&quot

does not show if it comes after objRS("AuthorName&quot or objRS("AuthorCode&quot. Are you certain that there is data in the objRS("body&quot

field? (I am guessing that there is since you say that you get all results if you order it a certain way, but want to be sure that I am understanding what you are saying.) If so, is there something special about the data? Also, if you list objRS("body&quot

first in your While/Wend statement, do you see the data in the remaining fields that follow? Otherwise, as best as I can determine, your code (as shown) appears fine.

-----------------------------------------------------------------------------------------------------
"If you can't explain something to a six-year-old, you really don't understand it yourself."
-- Albert Einstein

Ayac · Jan 6, 2004

That what freaks me out. Even worse, I did the following what "fixed" problem but still left me in a deep dark hole:

strQuery ="SELECT ID,Title,Body FROM Discuss;"
Set objRS = objConn.Execute(strQuery)
While not objRS.EOF
Response.Write " " & objRS("ID&quot

& " "
Response.Write " " & objRS("Title&quot

& " "
Response.Write " " & objRS("Body&quot

& " "
objRS.Movenext
Wend

If I varied the order in the query and in the list of reading but kept the same order for both, the program worked fine. If I changed the order between those two, some variables (usually the body) disappeared. I already restarted the server, so I have absolutely no idea what is going on...

Chopstik · Jan 6, 2004

You are right - very freaky.

I do not know of anything that would require a certain order so that you could view the fields. I am fairly certain that I have not always kept the same order that the fields were requested when viewing and yet have never come across the problem you are describing. It would seem that something else is the culprit here, but difficult to say for certain. Just a thought, but what is the remainder of your code for this page?

Perhaps someone else has come across this problem. If I have a chance, I'll see if I can research this a little more.

-----------------------------------------------------------------------------------------------------
"If you can't explain something to a six-year-old, you really don't understand it yourself."
-- Albert Einstein

stfarm · Jan 6, 2004

I have the feeling that you might have single or double quotes in those preceding columns. But I could be wrong.

Steve

HoppingMadRabbit · Jan 6, 2004

Just a suggestion...
Try outputting each column separatly and see if there are characters or character combinations in those columns that might prevent displaying the others.
In case of doubt, try seeing document source code after output, if as stfarm sugests, there are quotes in the fields, they might not show up and still be there messing up all the rest.

João.

foxbox · Jan 6, 2004

What is the field type of "body"?

br
Gerard

Ayac · Jan 6, 2004

Thanks you all!

The body is a memo field while the author is just a Varchar(50). This is how the full page looks like, there is nothing really fancy here:

<% @Language = VBScript %>
<%
Option Explicit
Response.Expires=0
Dim objConn, strConnection, strQuery, objRS
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "

http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-2" />
<title>DB test</title>
</head>

<body>
<%
Set objConn = Server.CreateObject("ADODB.Connection&quot

strConnection = "DSN=DiscussionData;"
strConnection = strConnection & "UID=Admin;PWD=password;"
objConn.Open strConnection

strQuery ="SELECT * FROM Discuss;"
Set objRS = objConn.Execute(strQuery)

While not objRS.EOF
Response.Write " " & objRS("AuthorName&quot

& " "
Response.Write " " & objRS("AuthorCode&quot

& " "
Response.Write " " & objRS("body&quot

& " "
objRS.Movenext
Wend

Set objRS = nothing
objConn.close
Set objConn = Nothing

%>
</body>
</html>

If quots or double quots would matter in the db, then why would it display or not display the content when I switch the order?

foxbox · Jan 6, 2004

Not only quotes matter but also charcters like % and especially > and < !
So i suggest you try this:
Response.Write " " & Server.HTMLencode( objRS("body&quot

) &_
" "

but i must agree your basic problems seems to be the order in which you response.write the fields....

br
Gerard

Ayac · Jan 7, 2004

Yep, it is just pretty odd. I actually tried that HTMLEncoding before but that is a different issue. In the bulletin' board where I use this code I display everything with HTMLEncode but the problem is still there.

It looks like if I determine a certain order of fields in the SELECT statement and when I just simply read them out in the same sequence while the loop is running, I got results. If I change the order then some of the fields are missing.

Ayac · Jan 7, 2004

Could that be a permission issue?

Chopstik · Jan 7, 2004

If you're able to view the fields in a certain order but unable to view when the order is changed, not sure that permissions would seem to be a problem. What prompted you to this idea, if I may ask? Are you having other issues related to permissions?

-----------------------------------------------------------------------------------------------------
"If you can't explain something to a six-year-old, you really don't understand it yourself."
-- Albert Einstein

Ayac · Jan 7, 2004

It is just because we moved to Windows 2003 from windows 2000, and on the new servers the default for everything is disabled. So we have to enable services, give rights to certain folders/users in order to run the system properly.

Chopstik · Jan 7, 2004

Am not familiar with Windows 2003 (Windows Server 2003?) and thus cannot offer an informed opinion here. However, it does not seem that it would cause a problem. What version of SQL Server are you using? (Not sure that this is a problem, but worth asking anyway.) For that matter, what else has changed since this was last working?

-----------------------------------------------------------------------------------------------------
"If you can't explain something to a six-year-old, you really don't understand it yourself."
-- Albert Einstein

shaddow · Jan 8, 2004

I'm working on W2003 Server with no problems. MSDE and SQL Server tested.

________
George, M

Ayac · Jan 9, 2004

I did some further testing and it looks like it does this madness only if memo fields exists in the table. If I have other than memos imported to SQL they work just fine. Does anyone know if there is any special thing to do when you import memo fields from Access to make it work in SQL? Is it really possible?

Ayac · Jan 13, 2004

I do not think that the content of the fields should even matter, should it?

Ayac · Jan 13, 2004

WOHOOOOOOOO!!! The ice broke!!! Thanks to Eli Robillard!! Before I become completely insane, I found this guy's website and asked my tricky question. Well the resolution is realatively very simple and I wanted to write it down, so all of us would learn from it.

There is one important rule we have to keep in mind when reading SQL tables with a memo (ntext) field in them with ASP. When you create the SELECT statement, you MUST list the memo fields at the very last position of your query!! I guess if you position your memo columns as last and then do a "SELECT *" would also do the job, but the main thing is to list the columns in the correct order, memos to the end...

I still do not know why it has to be this way, but at least I am happy now...

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

ASP + SQL, please help!

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Technical User

Programmer

Technical User

Programmer

Programmer

Programmer

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor