Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2003
    Posts
    5

    Question Unanswered: inconsistent retrieval of data

    I have created a sql 2000 db and imported my tables from an access database. My web site is using asp. I am getting inconsistent results when I retrieve data from the database. Even though I can see the data in the database some fields will not retrieve to the web site. When I go to the database and erase the field and then create a new field and then put data in, I will then be able to retrieve the data at the web site. The problem is some pages will be able to retrieve while others won't. Also some fields will return data while others won't. The web site worked fine with access.

    Does anyone know what may be causing this?

    Thank you in advance.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Betcha got spaces in the column names...

    or at least unique chars like ? in them

    Can you show us some code?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Dec 2003
    Posts
    5
    There are no spaces that I can see or unique chars.

    RS.Open "Select * FROM tProjects where playCode = '" & playCode & "'", Conn

    filePath = RS("filePath")
    myVideo = Split(filePath, ",", -1, 1)

    RS4.Open "Select * FROM tVideosViewed WHERE hitID="&hitID&" AND contentID="&myVideo(0), Conn


    When the RS4.Open fires then I will receive an error and when I investigate it is because the array is blank and filePath is blank even though there is a value in the database.


    Originally posted by Brett Kaiser
    Betcha got spaces in the column names...

    or at least unique chars like ? in them

    Can you show us some code?

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why not create and call stored procedures?

    It'll be more effecient as well

    Just curious though, do you have sql server client tools?

    Can you type that same select in a query analyzer window?

    What would it look like?

    Also you can look at profiler to see what's being passed to the server..

    my guess is the statement is malformed when it gets passed...

    do you know how to start a trace?

    I still thinks sprocs are the way to go in any case...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Dec 2003
    Posts
    5

    Talking

    We found the problem. On certain pages we did not have all the locktypes specified. So SQL server gave us random records back. Once That we specified the locktypes it worked fine.

    Thanks for suggestions. Luckily it was an easy fix for us


    Originally posted by Brett Kaiser
    Why not create and call stored procedures?

    It'll be more effecient as well

    Just curious though, do you have sql server client tools?

    Can you type that same select in a query analyzer window?

    What would it look like?

    Also you can look at profiler to see what's being passed to the server..

    my guess is the statement is malformed when it gets passed...

    do you know how to start a trace?

    I still thinks sprocs are the way to go in any case...

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Help me out here...what did you do exactly?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Dec 2003
    Posts
    5

    Cool

    Originally posted by Brett Kaiser
    Help me out here...what did you do exactly?
    We didn't have the locktype and cursor type set for all the recordsets that we had open. There was multiple recordsets on each page. When I added:

    RS.CursorType = adOpenDynamic
    RS.LockType = adLockOptimistic

    RS2.CursorType = adOpenDynamic
    RS2.LockType = adLockOptimistic

    For each recordset then we received consistent results from SQL server. It was a small thing but access let us get away with it but SQL wouldn't.

    Looks like we have found most of the differences from access to SQL but we need to test it for about a week.

Posting Permissions

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