Access Database - Field Order - ASP Page Record Order

Please post some of your script, are you calling and SP or passing running a string of sql.
Or, heaven forbid, using a front page wizard to access the data.
:)

Nope.
I use a lot of the times "CuteHTML" Which is like NotePad, but beefed up a little.
-----------------------------------------------------------------------------------------------
This "Should" give you what you need? I hope anyway.
Also, If I comment out     <%=rs("CatDescription")%>
The page will load, but will only load 1/2 of the page not the entire page?
(I hate how pasting code in here sometimes makes it look messy)
------------------------CODE-----------------------------------------------------------------
sub db_select_view_Sierra
      if do_search = "1" then
      request_view_Sierra
      view_Sierra_sql = "SELECT " & _
      "Cats.Cat, " & _
      "Sierra.CatID, " & _
      "Sierra.ContentID, " & _
      "Sierra.Product_Title, " & _
    "Sierra.Aff_Name, " & _
      "Cats.CatDescription, " & _
      "Sierra.Product_Th_Image, " & _
      "Sierra.Our_Price, " & _
    "Sierra.Retail_Price, " &_
    "Sierra.Brand_Name, " & _
      "Sierra.Percent_Savings, " & _
      "ContentTypes.ContentType, " & _
      "Sierra.Product_Description FROM ((ContentTypes RIGHT JOIN Sierra ON ContentTypes.contenttypeid = Sierra.contenttypeid)" & _
      " LEFT JOIN Cats ON Sierra.catid = Cats.catid)  WHERE Display=1 AND (Sierra.ContentId LIKE '%" & replace(keywords, "'", "''") & "%' OR Sierra.Product_Title LIKE '%" & replace(keywords, "'", "''") & "%' OR Sierra.Product_Description LIKE '%" & replace(keywords, "'", "''") & "%') ORDER BY Priority, Product_Title"
       else
      view_Sierra_sql = "SELECT " & _
      "Cats.Cat, " & _
      "Sierra.CatID, " & _
      "Sierra.ContentID, " & _
      "Sierra.Product_Title, " & _
    "Sierra.Aff_Name, " & _
      "Cats.CatDescription, " & _
      "Sierra.Product_Th_Image, " & _
      "Sierra.Our_Price, " & _
    "Sierra.Retail_Price, " &_
    "Sierra.Brand_Name, " & _
      "Sierra.Percent_Savings, " & _
      "ContentTypes.ContentType, " & _
      "Sierra.Product_Description FROM ((ContentTypes RIGHT JOIN Sierra ON ContentTypes.contenttypeid = Sierra.contenttypeid)" & _
      " LEFT JOIN Cats ON Sierra.catid = Cats.catid) WHERE Display=1 ORDER BY Priority, Product_Title"
      end if
      if request("sortby") <> "" AND inStr(lcase(view_Sierra_sql),"order by") = 0 then view_Sierra_sql = view_Sierra_sql + " ORDER BY " & request("sortby")
end sub

here is the list that is in the [Sierra] Table

dim ContentID
dim ContentTypeID
dim CatID
dim FKeywords
dim FMetaDesc
dim CatDescription
Dim Unique_ID
Dim Product_Title
Dim Unique_Product_ID
Dim Main_Cat
Dim Sub_Cat
Dim Product_Link
Dim Product_Th_Image
Dim Product_Description
Dim Ex_Product_Description
Dim Amount
Dim Our_Price
Dim Retail_Price
Dim Brand_Name
Dim Cat_ID
Dim CatIDs
Dim Category
Dim Add_Number_Field
Dim FCurrency
Dim Commission_Level
Dim Tracking_ID
Dim Percent_Savings
Dim Brand_Image_URL
Dim Brand_ID
Dim Dept_ID_Primary
Dim Dept_ID_Secondary
Dim Dept_ID_Tertiary
Dim Dept_Name
Dim Large_Product_Image
Dim Aff_Name
Dim Y


Here is the Table.
http://www.carrz-fox-fire.com/1/db1.rar

please post the section of code where you are initiating and opening the connection,
and the code surrounding "<%=rs("CatDescription")%>".

This "<%=rs("CatDescription")%>" looks to be the problem.
I need to see more of the asp code surrounding this!
Thanks

Just going to list what I can think of, hopefully it will be enough:
================================================
<%
do while not rs.EOF
'Read DB Record
On Error resume next
CatDescription = rs("CatDescription")
%>

<%
':: reset vars
CatId = request("CatId")
ContentId = request("ContentId")
CatDescription = request("CatDescription")
num_recs = 0
%>

<% =rs("CatDescription") %>

---------------------------------------------------
Hopefully that will be enough.
There is more in the main Record file, but that is just too much aggrivation to strip out everything
TO post on here.

Let me know?

I can imagine any time you would simply code
"<%=rs("CatDescription")%>"

I mean you may code
"<% Some variable =rs("CatDescription")%>"
or
"<% response.write rs("CatDescription")%>"
or somthing similar but not just <%=rs("CatDescription")%>

Everything in your code post make sence except the last line.
QUESTION  - "What are you trying to achive with <% =rs("CatDescription") %>"

sorry i meant CANT.
I CANT IMAGINE ANY TIME YOU WOULD SIMPLY CODE
"<%=rs("CatDescription")%>"

It has been like that for about 3yrs in the code for about 4 other sites.
I will test the "response.write" and see if that will help it out any and let you know.

Same error with the:
"<% response.write rs("CatDescription")%>"

response write will print it to the screeen!
is that what you are trying to do with that line of code?
or are you trying to assign cat description to a variable?
because "<%=rs("CatDescription")%>" if it is indeed on its own like you suggest, should have been casing the same error since day 1.

you simply cannot say "= somthing" you must say "first thing = second thing"

=======================================
ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

/CFF_Results.asp, line 16
=======================================

please post the preceeding 15 lines of code before this error strikes.
Because while "<%=rs("CatDescription")%> makes no sence to me, it should give you different errors.
Are you sure that line 16 of your code is "<%=rs("CatDescription")%>

Excuse the pictures not displaying, I had to shut this site down and am getting ready to re-open it,
Just have not moved the [Image] Folder over yet.

http://www.cff-music.com/Store/Publishing_Art.asp?CatID=89&Cat=Hal%20Leonard (3)

Look at the top and you will see an
<img> Hal Leonard
Hal Leonard is the largest music print publisher in the world

This is using the code line
<%=rs("CatDescription")%>
And it works like a charm.

Click on each of the Categories on the [Left]
And each one will display their own information.
And each is feeding to the <%=rs("CatDescription")%>

not to say that it isnt working, sorry.
Just that i need to see the few lines before and after this line so that I can get into your head to see what you are doing exactly.

Is the value of field catdestcription an image tag, image location, image name?
is the value of catdestcription  as chunk of html code?

Please post actual html/asp surrounding this line of code.
Thanks

this is the only thing that is surrounding the code.

        <td class='datatd' > 
          <table class='datatd' >
            <tr>
              <td class='label1TD'>
                <table >
                  <tr>
                    <td > 
                      <div align="center">
                        <% =rs("CatDescription") %>
                      </div>
                    </td>
                  </tr>
                </table>

=====================
The [CatDescription] Field is a [Memo]
Excepting HTML coding.

AHH so your catdescription note field contains"

"<b><img src="Distrib-Logos/HalLeonard.gif" width="192" height="98"><br>
Hal Leonard </b>is the largest music print publisher in the world.<br>
For more than 50 years, <b>Hal Leonard</b> has been publishing and <br>
distributing publications and products for virtually <br>
every instrument and performance group.<br>
<br>
With detailed information on how to read the publication.<br>
And also Artist Facts are available as well.<br>
<br>
<b>Hal Leonard Corporation<br>
Milwaukee, WI</b>"

Interesting, in my time I have not come accros being able to simply say = some html text and it be included in the html..
I would always have response written it. (although i try not to include chunks of html, only tags from outside sources
But if this works then I have learnt somthing.

Either way, it looks like your error is stating that the catdescription field is not being returned as part of your recordset.
Are you sure the recordset is not closed at this point?
can you access the notefield value anywhere else in your code, i can see that you have dimensioned the variable catdescription somewhere in there, are you ever getting the html note data into this variable, if so can you use the data in this var instead of recalling it from your recordset.

I try not to add too much data into the field, just something small like that above is alright.
Just basically as a <Header> for the [Category]
If it is going to be too big, then I will use a external file and attach to it's path, like you mentioned above.

I was told that I could not do it, I proved them wrong.
Never say never, Right? Always go for it, I Say.
--------------
As for the code, I am going to recreate it again, Start over from scratch
And watch it progress as I go, and see if I can find out exactly were the problem lies.

See the problem with is it [Closed] somewhere? It should not be.
As I did not mess with any of the asp code except to change out the [Field-Value's]
For where I needed them at. Other then that, just connected onto a New
Content Table called: Sierra
And connected Sierra to the Cats table and then that is were the problem started.
I have gone through the code "After the fact" to see if anything was still trying
To connect onto the original [Content] Table, and everything is clear and fine there.
So I really do not know what to say at this point, except that i have been working on this
Problem for 2-days now, and I would really LOVE to started inputting Data tomorrow at the latest.

So, I am going to start over with a clean template of my work again, and I will let you know
What I find.
By the way:
ASP - I can manipulate the heck out of it.
         I can almost make it do what I want it to do.
         I love to tear apart other people's script and manipulate it into something I like.
BUT!!!  I really honestly, have not go a clue on a lot of the ASP Technical Words like you stated here:
========
can you access the notefield value anywhere else in your code, i can see that you have dimensioned the variable catdescription somewhere in there, are you ever getting the html note data into this variable, if so can you use the data in this var instead of recalling it from your recordset.
========
Recordset - Yes
variables - I understand very little. In [Delphi Programming] I understand a "Variable" But in ASP
Not really. But I am learning more and more everyday.
So please hang in there with me, and try to explain a little bit as we go.
-----------
I will get back with you within the next few hours or sometime tomorrow morning
It is: 10:41pm here is NC USA.

Wayne

oh by the way.

Create your HTML Page. like you would normally do it.
Make your ASP Calls and have some HTML Code held within a Record MEMO Field
And call it like so:

<%= ("Your_Record_Name")%>

Talk to you in a few.

sorry bour the geek vocab.
I know this sounds simple but,
Try getting your hands on the value you are puttin into "view_Sierra_sql" and pasting it into an access query.
DO YOU SEE THE catdescription field?. I suspect you will not.

Let me know how you go,
You know it will probrably all work fine in the morning after a coffee and the paper!

Good luck.

simply use <% rs("CatDescription") %> and see the result.


<td class='datatd' > 
          <table class='datatd' >
            <tr>
              <td class='label1TD'>
                <table >
                  <tr>
                    <td > 
                      <div align="center">
                        <% rs("CatDescription") %>
                      </div>
                    </td>
                  </tr>
                </table>

gawai  --  Tried it and this is what I get:

Microsoft VBScript runtime error '800a01c2'

Wrong number of arguments or invalid property assignment: 'rs'

/CFF_Results.asp, line 16

if u use <% =rs("CatDescription") %>
wat is the result or error mesg ?

oh ok i read it on top
ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

/CFF_Results.asp, line 16

but i did not find any table "ContentTypes" in your db1.rar file. make sure ContentTypes table exist in your Access DB

This error is specific to you having requested information from a field that is not included in your recordset/query.

You need to:

1) comment out the part that is making the error so that your page will run.
2) and somewhere before the point of error, do a  <% response.write view_Sierra_sql %>
3) run the page and grab the sql string that has been shown as text on the page somwhere.
3) past that sql string into the query builder in access or a view in MSSQl (which ever you are using
4) run that query/view and see if there is a field CALLED "CatDescription".
If there is not, then the problem is that when you run that string as yu are doing, it is not brining back this field name which means it is not in the recordset which means you cannot access that chunk of html you believe tbe in there as it isnt.
If the field is there, is the data in that field what you are expecting?

Thanks
Ben

I ran this code
 <%response.write view_Content_sql %>
(Changed the name from "Sierra -to- Content" when I redid the page)
The:  Cats.CatDescription
Is showing.

I have to do some more testing, but I 'think' that I might have it fixed.
But will not know until possibly tomorrow.
I will keep everyone envolved here notified of my findings.
---------
gawai
The [ContentTypes] Table and Records does exist.
Did not think about sending that along with the sample database.
But it is there, all records are accurate and where they suppose to be at.
That is what has some so baffeled as to why the error?

That is the reason why I made this post here at EE, as some of the Error's that you receive
Are not always as they appear to be, and this I have proven time and time again.
And even have some of the information up on our support site as well.

Anyway, you guys have a good evening, it is 2:42AM, time to knock it off for tonight.
Luckily it is the weekend.

Wayne

Well, still no luck.
I re-did the entire ASP Page(s), and still get the same error?
I looked back at one of my other Database's that uses this Script.
And noticed that the Tables: Content & Cats have a Relationship with [CatID]
So I did this to the Sierra & Cats Tables and created the relationship between the 2 [CatID] Still nothing.

As mentioned in the previous post, I did run the

<%response.write view_Content_sql %>
And the:   Cats.CatDescription
Does show in the list.
So if it shows in the List, why would it be giving me the Error?
=============================================
ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

/CFF_Results.asp, line 16

=============================================
(Placed the code right above the line #16 of the Error page)
This is the list that I get when I run the code: <%response.write view_Content_sql %> [#6 on the list]
=============================================
SELECT Cats.Cat, Sierra.CatID, Sierra.ContentID, Sierra.Product_Title, Sierra.Aff_Name, Cats.CatDescription, Sierra.Product_Th_Image, Sierra.Our_Price, Sierra.Retail_Price, Sierra.Brand_Name, Sierra.Percent_Savings, ContentTypes.ContentType, Sierra.Product_Description FROM ((ContentTypes RIGHT JOIN Sierra ON ContentTypes.contenttypeid = Sierra.contenttypeid) LEFT JOIN Cats ON Sierra.catid = Cats.catid) WHERE Display=1 ORDER BY Priority, Product_Title
=============================================

Any more ideas (or) Suggestions?

Thanks All
Wayne

OK.

I cannot read the Table - Sierra  ??

I created a sample page just to call that Table itself, and I get this:

===========================================
ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

/data/Test.asp, line 52
===========================================
I do not get it?
I recreated the DataBase from Scratch.
Is there something that I am missing? Does the Table need to be [Formatted] or what is the deal?
I was been dealing with ASP & Access for several Years, and I have never run-up onto nothing like this.

Cannot someone please let me know what is going on with the Table itself?

Here is the test Page
http://shop.carrz-fox-fire.com/data/Test.asp

Someone please let me know what I have done wrong with my Table?

Here is a few lines, the rest of the lines is just calling the rest of the [Fields] So no big deal on all 37-Fields.
===========================================
<%
dim accessdb, cn, rs, sql
' Name of the Accessdb being read
'accessdb="CFF_Affiliates"

' Connect to the db with a DSN-less connection
'cn="DRIVER={Microsoft Access Driver (*.mdb)};"
'cn=cn & "DBQ=" & server.mappath(accessdb)
set cn = Server.CreateObject("ADODB.Connection")
cn.Open "provider=microsoft.jet.oledb.4.0;data source=" &  server.MapPath("CFF_Affiliates.mdb") & ""
' Create a server recordset object
Set rs = Server.CreateObject("ADODB.Recordset")

' Select all data from the table the_bird
sql = "select 'Sierra' " 

' Execute the sql
rs.Open sql, cn
%>
<table BORDER="1" align="center" width="100%">
  <caption>Testing</caption>
  <%
' Move to the first record
rs.movefirst

' Start a loop that will end with the last record
do while not rs.eof
%>
  <tr> <!-- Here is the Error, If I delete this line, the next will get the same error???-->
    <td width="100%"> <%= rs("ContentID") %> </td>
  </tr>
  <tr>
    <td width="100%"><%= rs("user_id") %></td>
  </tr>
  <tr>
    <td width="100%"><%= rs("ContentTypeID") %></td>
  </tr>

===========================================

Here you go guys.
http://www.carrz-fox-fire.com/a/data.rar

This has the Test.asp & Test.mdb

If someone can find out what I have done wrong in my Database Table = Sierra
Please let me know.

I will up-the-Points to: 500 for a correcting information on this stupid crazy issue that is making me crazy.

Thanks All
Wayne

ALSO, as i mentioned before, if you were to have taken the sql statement

"SELECT Cats.Cat, Sierra.CatID, Sierra.ContentID, Sierra.Product_Title, Sierra.Aff_Name, Cats.CatDescription, Sierra.Product_Th_Image, Sierra.Our_Price, Sierra.Retail_Price, Sierra.Brand_Name, Sierra.Percent_Savings, ContentTypes.ContentType, Sierra.Product_Description FROM ((ContentTypes RIGHT JOIN Sierra ON ContentTypes.contenttypeid = Sierra.contenttypeid) LEFT JOIN Cats ON Sierra.catid = Cats.catid) WHERE Display=1 ORDER BY Priority, Product_Title "

and pasted it into the SQL section of the microsoft access query builder and run the query, you will find that
"IT DOES NOT WORK, it cant find cats.catdescriton or priority.
I think you will find that ALL your problems to date stem from the fact that your field is actualy named
catsdescription   'notice the 's' plural after cat, and in your code and sql you are refering to it as catdescription, with no s after cat.

You also have an ORDER BY PRIORITY in your sql, but no priority field in any of your tables

it look like you are building these sql statements out of your head, which while is cool if you can do it, i would always build a query in msaccess, click on sql view, copy it and then use that sqk as your statment, cause it will save you hours of trouble like this

I want to show you a really cool way to access and display data.
It was shown to me by another EE member and it rocked my world.
So simple yet so handy, reduces amount of code and load on DB.

I am only showing you this as it looks like your basically recodeing your entire site and it may come in handy.

<html>
<title>Get this damn Database-Table to frigging Read</title>
<body bgcolor="#FFFFFF">
<%

'Dimension stuff
Dim Cnn   ' Connection
Dim RstGetData   ' recordset
Dim ArrayofData    ' array data type
Dim Counter1, Counter2,FirstRecord, LastRecord,FirstField, iFieldLast,SQLSelect

'Set SQL statement
SqlSelect = "select * FROM Sierra " 

'Set Connection
Set Cnn = Server.CreateObject("ADODB.Connection")
'open Connection
Cnn.Open "provider=microsoft.jet.oledb.4.0;data source=" &  server.MapPath("Test.mdb") & ""
'execute and get back recordset
Set RstGetData = Cnn.Execute(SqlSelect)
'Read recordset into our array
ArrayofData = RstGetData.GetRows()

'Close the recordset and connection straight away, we can get all the data out of our array now and no longer
'need to hold open db connection
RstGetData.Close
Set RstGetData = Nothing
Cnn.Close
Set Cnn = Nothing

'Find out how many records we got back and how many fields in the records
FirstRecord   = LBound(ArrayofData, 2)
LastRecord    = UBound(ArrayofData, 2)
FirstField = LBound(ArrayofData, 1)
iFieldLast  = UBound(ArrayofData, 1)

'Display this info to the screen so you can see whats up
response.write "Recs First: " & FirstRecord
response.write "Recs: Last " & LastRecord
response.write "FieldsFirst:  " & FirstField
response.write "FieldsLast:  " & iFieldlast
%>


<%'And finaly use this data in a tabel%>
<table border="1" width="100%" cellspacing="4" cellpadding="3">
<%       For Counter1 = FirstRecord To LastRecord %>
      <tr>
            <%For Counter2 = FirstField to iFieldLast%>
            <td><%Response.write ArrayofData(Counter2,Counter1) %></td>
            <% NEXT 'J%>
      </tr>

<% NEXT 'I%>
</table>  

</body>
</html>

The Above idea will optimise the way you use asp to get and display data
When using access (limited conections = limited end users of your site) you need to be especialy carefull of how you get data and how long you hold open connections.

You are processing and displaying quite allot of data from your database and you should consider using this method, or even better, use and MSSQL back end.

Let me know how you go with it all
Best Of Luck
Ben

So i got way of track here sorry bout that.
I believe the answer to your actual problem is 3 comments back.

That is fine Ben.

The Field in the Test.mdb [CatsDescription] I missed spelled the word in the Test.mdb
The actually spelling of the Field in the actual Database is: [CatDescription]
(Sorry about the confusing part of that, was tired and agrivated)

--------------------
Can you please tell me how to do:
[Quote]
and pasted it into the SQL section of the microsoft access query builder and run the query
[/Quote]

--------------------
I am doing some research on building [Queries] in Access, so see if this is part of my problem.
--------------------

I will check-out the code that you have provided here and see if it will work in my environment?
I have so much sections in my original code, though I might be able to strip some of it out
And test with your code.
Right now I am nearly up to anything.

I just want to know "why" the Table.Sierra
Cannot be read from my pages, I am going to see if I can change up my current code and see if it will fire up
And start working.
I know that the Table in the Test.mdb is readable and working as the Test.asp page proves that.

Thanks Ben for your assistance thus far.
Wayne

OK. I have finally got it working.
It was a simple over-site on my part of which I could kick myself in the Butt over and over again.

<%= rs("ContentID")%>   and everything else so far needed to be changed to: <%= rsSierra("ContentID")%>
As that is my RecordSet call is: rsSierra.
OK.
Let me do some more testing, and make sure that everything else is like it should be, and I will post back.
X <-- Crossong my fingers on this one.

(I do have it showing the products from the Database in the Search Query on the Page. So that is a Start)

Had to create another RecordSet for the [Cats] Table (Of which I shouldn't of had too? Which is very strang)
But all errors are gone now.
I just have to work on my [Search.asp] page and get it to show properly, and test the rest out.
But as for this issue.




'<----------------------------->;

Always check to make sure that you have enough RecordSets for all the different calls made.
Make sure that they are correctly named for the different ones.
In my case:

rsSierra - For the Sierra Table
rsCat - For the Cats Table.
And so for and so on.

So I am going to close this one and Award points to everyone that has assisted.
Since I fixed my own problem and that is great, I would not have gotten where I needed to be
Without [Ben]'s assistance.

Take Care All;
Wayne (Carrzkiss)

Wayne,

"pasted it into the SQL section of the microsoft access query builder and run the query"

Means take the sql statement, as in "Select * From Sierra", copy it,
Open msaccess, open a new query, when it asks you which tables or queries you want to base this query on click cancel and you will notice that the button up the top left, that normally enables you to switch between design and run mode of the query, will now include an SQL option.
When you go into this sql option, you can simply past your Select * From Sierra or what ever other sql you want into it and run it.

But the real reason i said to use this is so you can just make a query in access test it and then move the the sql tab and steal the sql from there to use in your asp page. Saves you from having to come up with the sql in your head and then get it wrong and get almost no help from the errors IIS will give you about the syntax of your SQL.

Good luck with your site.
Ben