Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Server weirdness (ASP)

    I have a series of forms for preview/submit to SQL server. everything works as expected except, twice now, on querying SQL server to display data on the database, I've had to re-query the server to get all the stored information. It's like there's this strange limit that SQL server hits every once in a while. what's more, it doesn't seem consistent.

    I'm not that knowledgable about SQL Server and I'm wondering if I am doing something dumb.

    THE DETAILS...

    I set up a connection to SQL Server using Global.asa.

    I use the following dbconnect string to open a connection:

    <font face="Georgia">
    Set DataConnection = Server.CreateObject("ADODB.Connection")
    DataConnection.ConnectionString = Application("DBConnection_ConnectionString")
    DataConnection.ConnectionTimeout = Application("DBConnection_ConnectionTimeout")
    DataConnection.CommandTimeout = Application("DBConnection_CommandTimeout")

    DataConnection.Open
    </font face=georgia>
    I pop this into my db pages using good old
    <font face="Georgia">
    < !--#INCLUDE FILE="../dbconnection.asp"-- >
    </font face=georgia>
    And I execute read commands like so:
    <font face="Georgia">
    mySQL = "SELECT * FROM Complainant_Info WHERE ComplainantID=" & Request("ComplainantID")
    Set myRS = DataConnection.Execute(mySQL)
    </font face=georgia>
    All goes well for the most. there's a particular field on all the tables called "Allegations" which is ntext in SQL Server. On ONE page, I can get the data out of the server and display. on all the OTHERS, I have to re-query to pull that one silly data element. Odd, but it works.

    In the course of stress-testing the web app, I noticed a whole block of info doesn't redisplay and I have to requery to grab this information.

    Weird!!

    Is there some issue using Set myRS = DataConnection.Execute(mySQL) that I need to be aware of?? Or anything else obvious? Don't tell me not to use Select * unless you're SURE that's a big problem. I *do* have to pull everything out re-display (except for a few fields) and doing Select * is easy to work with in something like, say, FrontPage, which doesn't word-wrap (not sure why MS decided that this is not a good feature; I'd love to have the option...)

    TIA <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: SQL Server weirdness (ASP)

    Is an ntext field what we used to call a "memo" field in the dBASE world? Here's a stab in the dark: you might research whether that kind of field needs special handling. Try this: drop your code into a VBA host like Word and pump the data into the Word document (obviously this takes a few changes, but it shouldn't be too many). If the ADO code works properly, perhaps there's something in the field that is not HTML-safe or VBScript-safe for some reason?

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Server weirdness (ASP)

    yes ntext corresponds to memo. SQL Server claims to be able to store 2G info in the beast. the way it works is with a pointer to an external file that does the actual storage.

    however, I can pull up the field using standard Response.Write myRS("Allegation") after establishing a connection. and one page will pull it up without having to re-query for that one field; the other pages just refuse to find it. The data displays in the db, so it's being written in. I have to re-query to pull it up. and this doesn't address a bunch of fields that also don't who up on redisplay with yet another page -- and those fields aren't anything special. it's odd...

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: SQL Server weirdness (ASP)

    So... did you try my suggestion?

    > I can pull up the field using standard Response.Write myRS("Allegation") after establishing a connection.
    > and one page will pull it up without having to re-query for that one field; the other pages just refuse to find it.

    What is the difference between the functional page and the dysfunctional page?

    > and this doesn't address a bunch of fields that also don't who up on redisplay with yet another page -- and
    > those fields aren't anything special.

    What do you mean by "redisplay"?

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Server weirdness (ASP)

    <font color=448800>PROBLEM SOLVED. ntext fields in SQL Server should be placed in the last column in a table in order for Execute(Query) to return all records. In some cases Execute(Query) seems to stop when an ntext field is encountered and data after that point is not retrieved. Depending on your setup you may want to avoid Select * on tables with ntext and call the columns explicitly.</font color=448800>

    I didn't try your suggestion because the issue isn't just limited to ntext fields. and ntext fields will get read and displayed sometimes. other fields may not choose to be read as well, so there seems to be something going on outside of a particular data type.

    As far as the difference between the functioning and the non-fuctioning pages... THERE DOESN'T SEEM TO BE A DIFFERENCE. That's what's weird. I use the exact same techniques to establish a connection, execute the query and display the results using Response.Write myRS("<field name>"). I switch the connection method to DSN-less (because I hate Global.asa) and the behaviour persists.

    In the Access world, you can define a connection using ADOVBS to either open a recordset with cursors or do Execute(Query); using SQL I don't mess with cursors (probably a good thing) and everything process with the Execute(QueryString) to access the data. Perhaps there's some kind of limitations ot Execute(QueryString) that I am exposing?

    Again, here's what happens -- it's easy to reproduce.

    1) establish DataConnection with a DSN-less connect string thru an include file
    2) read the recordset (Do While Not myRS.EOF ... Response.Write myRS("<field>") & "< br >") etc. to get an output list
    3) what's this?? Allegations won't display, except on one page that does exactly the same stuff as the others. Research and think about it. Do a page with just QueryString to pull that one field up, output to string, works on all tables.
    4) throw hands up, do a requery and there she blows.
    5) chug along, discover than an entire BLOCK of fields starting with Contractor_ doesn't display. These are text fields
    5) re-query and they show.

    .....

    hey, this is weird! i just noticed that the SQL table with the block of data starting with Contractor_ is at the 'end' of the table. I just also noticed that another block of data doesn't display in another page with that block set at the end. Something's up with how the columns are arranged.... I betcha the ntext field needs to be at the very end... which actually makes some amount of sense..

    I'll look into resetting the tables and see if that fixes things. Will let you know if this works.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •