Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2008
    Posts
    8

    Unanswered: record set returning some field as empty?

    I been having this problem for a long time.

    This is totally confusing for me.
    The case is this.
    I use IIS 5, MS SQL, ASP

    Some of table field looses its contents.
    Better to expalin it with example fields.

    Table: Products
    Fields: idProduct, chrName, chrProductImage ...


    If I check first chrProductImage I get its value.

    But if I check first chrName and then chrProductImage, I will get empty value for chrProductImage. It just don't make any sence to me.


    marksu

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    First things first; what are the datatypes of each field?

    Secondly, what method are you using to access the data?
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2008
    Posts
    8
    Here is table information

    Table: ProductPropertyVal
    idPropertyVal int
    idProduct int
    idPropertyCategory int
    chrValue1 text
    chrValue2 text
    chrValue3 text
    chrValue4 text
    chrImageUrl text

    Table: ProductPropertyCategory
    idPropertyCategory int
    idDepartment int
    chrCategoryName varchar (50)
    intUsedColumns int
    chrPropertyType vaschar (255)

    Table: ProductPropertyShow
    idPropertyShow int
    idPropertyCategory int
    chrShow varchar (100)
    Last edited by marksu; 01-17-08 at 16:30.

  4. #4
    Join Date
    Jan 2008
    Posts
    8
    Here is the code in simplified form.

    <%
    ' ************************************************** **
    ' Lists a product's all properties
    ' ************************************************** **

    ' Create an ADO database connection
    set dbProperties = server.createobject("adodb.connection")

    ' Create the record set
    set rsProperties = server.CreateObject("adodb.recordset")

    ' Open the connection using our ODBC file DSN
    dbProperties.open("filedsn=mySite")

    ' Execute the sp_InsertProduct stored procedure
    ' to add the product into the database.
    sql = "execute sp_RetrieveProductPropertiesByIdProd_X " & idProduct & "," & chrShowFilter

    ' Execute the statement
    set rsProperties = dbProperties.Execute(sql)
    dim idPropertyVal, chrCategoryName, chrValue1, chrValue2, chrValue3, chrValue4, chrShow, intUsedColumns
    dim OLDidPropertyVal, OLDchrCategoryName, OLDchrValue1, OLDchrValue2, OLDchrValue3, OLDchrValue4, OLDchrShow


    IF NOT rsProperties.EOF THEN

    Do while NOT rsProperties.EOF


    OLDidPropertyVal = idPropertyVal
    OLDchrCategoryName = chrCategoryName
    OLDchrValue1 = chrValue1

    OLDchrValue2 = chrValue2
    OLDchrValue3 = chrValue3
    OLDchrValue4 = chrValue4
    OLDchrShow = chrShow
    OLDintUsedColumns = intUsedColumns

    response.write "a " + rsProperties("chrValue2") + " a <BR>" ' Return a value
    idPropertyVal = rsProperties("idPropertyVal")
    chrCategoryName = rsProperties("chrCategoryName")
    chrValue1 = rsProperties("chrValue1")
    chrValue2 = rsProperties("chrValue2")
    response.write "b " + chrValue2 + " b <BR>" ' returns NULL
    response.write "c " + rsProperties("chrValue2") + " c <BR>" ' returns NULL

    rsProperties.MoveNext
    LOOP



    END IF

    %>

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Errr...
    Try
    Code:
    chrValue2 = rsProperties.Fields("chrValue2") & ""
    Response.Write(chrValue2)
    George
    Home | Blog

  6. #6
    Join Date
    Jan 2008
    Posts
    8
    I tested that but the problem still remains.

    Feels like after getting one field value the recordset gets empty?

    marksu

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Well, stick the value in a variable and re-use..?
    George
    Home | Blog

  8. #8
    Join Date
    Jan 2008
    Posts
    8
    It results to same problem.
    This problem is so wery unlogical as it get.
    Could maybe me MS virus... you know MS Microsoft.
    Just a joke, well kind of anyways. I still remember my frustration in Visual Basic 6 world many yeas bag with all the bug in the VB6 tool.
    I even updated the MDAC and JET well MDAC was updated allready.

    This kind of problems I have had many of and is frustrating.

    marksu

    Quote Originally Posted by georgev
    Well, stick the value in a variable and re-use..?

  9. #9
    Join Date
    Jan 2008
    Posts
    8
    So now I tried same thing using php and my mssql server.


    $db = @mssql_connect("xxx","xxx","xxx") or die("Unable to connect to server");
    mssql_select_db("yy");
    $result = mssql_query("SELECT * FROM Products WHERE idProduct = 27");
    $row_DetailRS1 = mssql_fetch_assoc($result);

    print_r($row_DetailRS1); //prints the one record content correctly
    echo "<BR><BR><BR><BR><BR>";
    echo 'test: ' . $result['idProduct']; //give no field value
    echo "<BR><BR><BR><BR><BR>";
    echo 'test: ' . $row_DetailRS1['idProduct']; //using array give a correct field value.

    So seems like a mssql database server problem maybe?

    Well I can manage using php which I need to do for now.

    I still hope this gives enaf info for some mssql server professional to tell me what the cause of my problems are in this case?

    marksu

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Personally I've never come across this issue and am unable to recreate. My only other thougt would be that your recordsetproperty is set to forward only...

    Capturing the values in variables is still my preered method here...
    George
    Home | Blog

  11. #11
    Join Date
    Jan 2008
    Posts
    8
    Thank your for your reply.
    Forward only should not matter as problem accures in one record scope.

    I stated before capturing to variable is not solution as it gives empty field to variable as well.

    Thank you for trying to help though.
    marksu

    Quote Originally Posted by georgev
    Personally I've never come across this issue and am unable to recreate. My only other thougt would be that your recordsetproperty is set to forward only...

    Capturing the values in variables is still my preered method here...

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Aha, I think I've noticed the msitake here...
    Use the following
    Code:
    someVariable = rsProperties.Fields("chrValue2").Value
    Remember to check for nulls.
    George
    Home | Blog

  13. #13
    Join Date
    Jan 2008
    Posts
    8
    Quote Originally Posted by georgev
    Aha, I think I've noticed the msitake here...
    Use the following
    Code:
    someVariable = rsProperties.Fields("chrValue2").Value
    Remember to check for nulls.
    I tried that before and that did not help.
    Problem is propaply at database end (mssql server) as I have similar trouble with asp as well as php.
    It so unlogical that it is frustrating.

    Not sure could it be a system memory thing?


    marksu

  14. #14
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    What happens if ....

    What happens if you put your sql in a datatable
    are you able to see everything then ???
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

Posting Permissions

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