SELECT Question

[Penhall]

This is something that's been bothering me for awhile and I figured I'd get off my butt and ask if anyone can shed some light.

When I'm doing a SELECT * from TABLE statement (on MS SQL at JH using ASP) sometimes fields won't return their value when I do an rs("FIELDNAME") - what I end up doing usually is adding the field name directly to the the SELECT query... eg Select *, MissingField from TABLE

This works sometimes but other times it doesn't unless I juggle the order of the field names in the query when there are multiple gremlin FIELDS.

Has anyone run across this before and is there any advise around this issue that you would share. Suppose I should select each field individually in table-order but I would have thought SQL/ASP would have been smarter than this.

Anyway, I'm an admitted self-taught web DB hack who is trying to properly edjumukate myself so any thoughts/insites would be appreciated.

Cheers

 

[wotech]

i use asp and sql pretty extensively and haven't run into this before.

You're just doing a simple "SELECT * from tblName;" ? No joins or anything?

 

[Penhall]

Yup, simple select * from a single table will cause it

Have run into the same problem using Joins though however.

I have also found that reassigning variables prior to a response.write will SOMETIMES work
eg:
Name=rs("Name")
...
<% response.write(Name) %>
' instead of <% response.write(rs("Name")) %>
...

shrug

 

[hafa]

This is a known issue with mssql/classic asp when using fields of datatype “text”. These fields and those subsequent to them in the database column order will not display when using response.write(recordset.fields.item(“name”).value).

A workaround I’ve successfully employed:

1. Put all text fields last in the column order of the database
2. Use Active cursor, i.e.: recordset.CursorType = 2

Hope this helps!

 

[Penhall]

This is a known issue with mssql/classic asp when using fields of datatype ?text?. These fields and those subsequent to them in the database column order will not display when using response.write(recordset.fields.item(?name?).value).

A workaround I?ve successfully employed:

1. Put all text fields last in the column order of the database
2. Use Active cursor, i.e.: recordset.CursorType = 2

Hope this helps!

That helps a lot - thanks! I'm in the process of moving towards ASP.net anyway (slowly be surely).

Cheers