SQL recordset values are not returning!

[donishere]

I made a recordset from a query. I'm trying to make an HTML form with a field pre-populated with the value of a field from my query. However, it's not returning the value. The funny this is, though, I can get the value to print at the top of the web page but the exact same field will not pre-populate the text field of the form that's in the middle of the page.

<%=(recordsetDealerInfo.Fields.Item(&quot;dlComments&quot.Value)%>
- Value will print at the top of the page
- Value will NOT pre-populate web form text field OR anyone else on the page for that matter.

Anyone know what's going on?

 

[hithere]

As you don't have all the code here, I'll take a stab at it.
My guess is that you have and issue with the &quot;&quot;
you have this:
<%=(recordsetDealerInfo.Fields.Item(&quot;dlComments&quot.Value)%>
and I presume that this is in a text box so, your text box should be something like this:

<input type = &quot;text&quot; name = &quot;mytextbox&quot; value = &quot;<%=(recordsetDealerInfo.Fields.Item('dlComments').Value)&quot;%>

try that and see if that fixes the problem

hth
mb &quot;Where's the Ka-Boom? There's supposed to be an Earth-shattering Ka-Boom!&quot;
Marvin the Martian

 

[hithere]

As you don't have all the code here, I'll take a stab at it.
My guess is that you have and issue with the &quot;&quot;
you have this:
<%=(recordsetDealerInfo.Fields.Item(&quot;dlComments&quot.Value)%>
and I presume that this is in a text box so, your text box should be something like this:

<input type = &quot;text&quot; name = &quot;mytextbox&quot; value = &quot;<%=(recordsetDealerInfo.Fields.Item('dlComments').Value)%>&quot;>

try that and see if that fixes the problem

hth
mb &quot;Where's the Ka-Boom? There's supposed to be an Earth-shattering Ka-Boom!&quot;
Marvin the Martian

 

[hithere]

my bad, use the second one &quot;Where's the Ka-Boom? There's supposed to be an Earth-shattering Ka-Boom!&quot;
Marvin the Martian

 

[donishere]

hithere,

Nope, that wasn't it. Syntax error on your suggestion.

Anyone else got it?

 

[sunila7]

Hi,

It will be helpful, if u put the code which u r using to print the value in the page in the forum.

Sunil

 

[FengShui1998]

donishere,

How about this:

<input type = &quot;text&quot; name = &quot;mytextbox&quot; value = &quot;<%=recordsetDealerInfo.Fields.Item('dlComments').Value%>&quot;>

fengshui_1998

 

[donishere]

I know I'm referencing the recordset correctly. I don't think it has anything to do with the syntax itself. I think this is a server memory issue. I'm referencing other values in the same recordset on the same ASP page. After about the 5th reference of that recordset, any future reference to any value in that recordset will just return a blank string. I know this because I experimented by setting up a variable to hold the value of a recordset value in the beginning of the web page and referencing that variable later in the page would actually return the correct value. I think this problem has something to do with having big recordsets of considerable size and the server just deciding to unload the recordsets after a certain number of calls.

Has anyone else run into this problem before? I need

 

[donishere]

I know I'm referencing the recordset correctly. I don't think it has anything to do with the syntax itself. I think this is a server memory issue. I'm referencing other values in the same recordset on the same ASP page. After about the 5th reference of that recordset, any future reference to any value in that recordset will just return a blank string. I know this because I experimented by setting up a variable to hold the value of a recordset value in the beginning of the web page and referencing that variable later in the page would actually return the correct value. I think this problem has something to do with having big recordsets of considerable size and the server just deciding to unload the recordsets after a certain number of calls.

Has anyone else run into this problem before?

 

[BarkingFrog]

Well, I think you have one of two problems:

Either, you are somehow closing the recordset before hitting the end of the page

or

You are at the end of the recordset by the time you hit the end of the page.

Either way you should be getting an error telling you the problem.

At the risk of sounding rude, unless you are willing to post all of your code, where it can be analyzed, then you really can't expect people to help you solve your problem.

I have never seen a server just decide at some point to unload a recordset.

 

[onpnt]

BarkingFrog is correct
Post the code!!!

I may not get it the 1st or 2nd time,
but how sweet that 15th time can be.
admin@onpntwebdesigns.com

 

[Dynapen]

It can't really be a memory error, becuase the server already has all of the recordset in memory before it starts to write any of it. So if you weren't getting anything at all in the RS, then I might agree with you. But if you get even one value, then you have all of the recodset.


By the way.

Rs.fields.item(&quot;name&quot.value

is jsut the long way of doing

rs(&quot;name&quot

If you don't explicitly tell it to use the value, it just assumes that on it's own. Only need to fully qualify if you need a different pice of it. (Name, Type, etc....)

Try this one

<input type = &quot;text&quot; name = &quot;mytextbox&quot;
value = &quot;<%=recordsetDealerInfo(&quot;dlComments&quot%>&quot;>

That won't give you a syntax error, and it saves you some typing.

My question on the recordset is this. Do you know that there are values in that part of the recordset. If you pull a null value into a recordset, it will display as a empty string. Can you take the SQL statment and run it against the DB directly, and ensure that there are results?


The money's gone, the brain is shot.....but the liquor we still got.

 

[donishere]

As per everyone's request, I have pasted the code below. It's a stipped-down version of the real page because there is lots of HTML in between each SQL call.

<%
	strAccountNumber = CStr(Request.Form(&quot;dealerNumber&quot;))
	
	' CONNECTION STRING HERE
	
	strSQLStatement = &quot;SELECT * &quot; _
				& &quot;FROM	intlDealers, &quot; _
				& &quot;	intlTerritoryManagers &quot; _
				& &quot;WHERE	intlDealers.dlDealerAcctNum = '&quot; & strAccountNumber & &quot;' &quot; _
				& &quot;AND		intlDealers.dlTerritoryManager = intlTerritoryManagers.tmID&quot;
	set RecordsetDealerInfo = connect.Execute (strSQLStatement)

	dlAirTermsOfPayment = (RecordsetDealerInfo.Fields.Item(&quot;dlAirTermsOfPayment&quot;).Value)
	dlOceanTermsOfPayment = (RecordsetDealerInfo.Fields.Item(&quot;dlOceanTermsOfPayment&quot;).Value)
	
%>
<% ''' SOME HTML HERE ''' %>
<table>
  <tr>
    <td width=&quot;20%&quot;><font face=&quot;Verdana, Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;> 
      <%
        	If recordsetDealerInfo.Fields.Item(&quot;dlMailing&quot;).Value = True Then
        		Response.Write &quot;Yes&quot;
        	Else
        		Response.Write &quot;No&quot;
        	End If
        %>
      </font></td>
  </tr>
</table>
<% ''' SOME HTML HERE ''' %>
<table width=&quot;700&quot; border=&quot;0&quot; cellspacing=&quot;2&quot; cellpadding=&quot;0&quot;>
  <tr> 
    <td><font face=&quot;Verdana, Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;><%=(RecordsetDealerInfo.Fields.Item(&quot;dlAccountType&quot;).Value)%></font></td>
    <td colspan=&quot;2&quot;><font face=&quot;Verdana, Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;><%=(RecordsetDealerInfo.Fields.Item(&quot;dlAgreementType&quot;).Value)%></font></td>
  </tr>
  <tr> 
    <td><font face=&quot;Verdana, Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;><%=(RecordsetDealerInfo.Fields.Item(&quot;dlAccountStatus&quot;).Value)%></font></td>
    <td colspan=&quot;2&quot;><font face=&quot;Verdana, Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;><%=(RecordsetDealerInfo.Fields.Item(&quot;dlAccountStatusReason&quot;).Value)%></font></td>
  </tr>
  <tr> 
    <td colspan=&quot;5&quot;><font face=&quot;Verdana, Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;><%=(RecordsetDealerInfo.Fields.Item(&quot;dlAccountStatusNotes&quot;).Value)%></font><font face=&quot;Verdana, Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;></font><font face=&quot;Verdana, Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;></font></td>
  </tr>
  <tr> 
    <td><font face=&quot;Verdana, Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;><%=(RecordsetDealerInfo.Fields.Item(&quot;dlDiscount&quot;).Value)%></font></td>
    <td colspan=&quot;2&quot;><font face=&quot;Verdana, Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;><%=(RecordsetDealerInfo.Fields.Item(&quot;dlPerformerDiscount&quot;).Value)%></font></td>
  </tr>
  <tr> 
    <td><font face=&quot;Verdana, Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;>$<%=(RecordsetDealerInfo.Fields.Item(&quot;dlOpenCreditLimit&quot;).Value)%></font></td>
    <td colspan=&quot;2&quot;><font face=&quot;Verdana, Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;>$<%=(RecordsetDealerInfo.Fields.Item(&quot;dlPartsCreditLimit&quot;).Value)%></font></td>
  </tr>
  <tr> 
    <td><font face=&quot;Verdana, Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;> 
      <%=(RecordsetDealerInfo.Fields.Item(&quot;dlAirTermsOfPayment&quot;).Value)%>
      </font></td>
    <td colspan=&quot;2&quot;><font face=&quot;Verdana, Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;> 
      <%=(RecordsetDealerInfo.Fields.Item(&quot;dlOceanTermsOfPayment&quot;).Value)%>
      </font></td>
  </tr>
</table>
</body>
</html>
<%
	RecordSetDealerInfo.Close()
%>

The recordset values that came back blank (but weren't supposed to) are dlAccountStatusNotes, dlAirTermsOfPayment, and dlOceanTermsOfPayment.

Then, I decided to use the variables dlAirTermsOfPayment and dlOceanTermsOfPayment and use those in the code. So instead of using

<%=(RecordsetDealerInfo.Fields.Item(&quot;dlAirTermsOfPayment&quot;).Value)%>

I used:

<%=dlAirTermsOfPayment%>

After this modification, the value was now being returned. I figured I could just use a variable for each field in the recordset just like I did above, but that didn't work because it would return bogus blank field values just as before.

 

[hithere]

Okay, so you've verified that the data exists in the table for these fields. You've assigned each of the values to a variable at the top of the page using the same statement and had them print out and it worked fine but when you do the same in the table they don't print? Then you assigned the variables again and they print fine for a few but not the others?

I'd check the table and make sure that the columns are set correctly (i.e. are they are text or numeric as appropriate). I'd also run the one's that are coming back empty through a check for ISNULL. I'd also verify that the data in the table is good by manually creating an entry as opposed to submitting to the db by a form.

Also, I'd be curious that you confirm that each record is unique based on your SQL statement. Could this be causing the problem if there's some sort of duplication? Maybe try looping through the record(s) until rs.eof and see if that's the cause (see if returns more than one table).

just some thoughts, hope they might help.
mb
&quot;Where's the Ka-Boom? There's supposed to be an Earth-shattering Ka-Boom!&quot;
Marvin the Martian

 

[donishere]

hithere,

Everything checks out. Still no dice.

However, I did find a workaround, although it doesn't really fix the original issue. What I did was just duplicate recordsets.

strSQLStatement = "SELECT blah..."
set Recordset1 = connect.Execute (strSQLStatement)
set Recordset2 = connect.Execute (strSQLStatement)
...

I would be interested to see someone continue to find out why having just one recordset mysteriously stops returning data. Having the answer to that would really benefit everyone.

don

 

[hithere]

I wondered if that might be an issue. The way it was explained to me is that using conn.execute is for things like UPDATE or INSERT (one shot operations). I would be interested to know if you use the same set up but used conn.open instead whether or not you have the same problem.
mb &quot;Where's the Ka-Boom? There's supposed to be an Earth-shattering Ka-Boom!&quot;
Marvin the Martian

 

[donishere]

Here's what I've got:

set connect = Server.CreateObject("ADODB.Connection")
connect.open "dsn=dsnName;uid=username;pwd=password;"

strSQLStatement = "SELECT blah..."
set Recordset1 = connect.Execute (strSQLStatement)
set Recordset2 = connect.Execute (strSQLStatement)
...

Hope that code snippet was a little clearer.

don

 

[hithere]

Sorry I wasn't really clear, I meant something like this:

Set conn = Server.CreateObject(&quot;ADODB.Connection&quot
Set rs = Server.CreateObject(&quot;ADODB.RecordSet&quot
conn.Open conn_string()

SQL=&quot;SELECT blah..&quot;

rs.Open SQL, Conn

mb &quot;Where's the Ka-Boom? There's supposed to be an Earth-shattering Ka-Boom!&quot;
Marvin the Martian

 

[donishere]

Ok, I tried that. Same results: missing values.

This is just too confusing.

don

 

[codestorm]

I haven't bothered to totally read your code, so correct me if I'm totally off the mark.

Are you returning a recordset with a few numeric or small text fields, then a text or ntext field or so, and then some more numeric or small text (varchar, char, nchar, nvarchar) fields?

If so, try moving all the text and ntext fields to be the last returned.

(ie Instead of doing select *, do select table1ortablealias1.field1, ...)



codestorm
Fire bad. Tree pretty. - Buffy
select * from population where talent > 'average'
You're not a complete programmer unless you know how to guess.
<insert witticism here>