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

First things first; what are the datatypes of each field?

Secondly, what method are you using to access the data?

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)

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

%>

Errr...
Try

I tested that but the problem still remains.

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

marksu

Well, stick the value in a variable and re-use..?

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

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

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...

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

Aha, I think I've noticed the msitake here...
Use the following
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

What happens if you put your sql in a datatable
are you able to see everything then ???