Hello Great Imar and others!

I see you keep your marvellous decision of helping all of us, this is simply great.

I´ve bought yours "ASP.NET 2.0 Instant Results" and although I haven´t had time to start studying it, I realize it will be a very nice experience (yes, the fact that YOU wrote many of the chapters is specially motivating!).

Well, let´s go for my very-strange problem.

I created a MS Access database on the server, as I am already used to, based in some quite complex queries, some based on others, till the "last one" will be used to populate the recordset.

Well, the queries themselves seem ok, I put some "Iff(IsNull(..);0;...)" to garantee there will be no "blank spaces" and it worked well. The problem is that, when I create the recordset at Dreameaver, testing it I realize the problem: some cells from the "visual matrix" of elements from the query, shown by Dreamweaver, are simply missing! There are no errors, no error messages, nothing. It simply seems that Dreamweaver "lost" some of the values of some fields from some records during the "extracting" data from the database. It is strange because the data "lost" is no more complex nor have special characteristics then everything else.

Imar, from the best of your knowledge which was probably already huge since you were 10 ;) , what do you suggest????

Please help. I am really suffering WEEKS now!

Thanks a lot and best regards,
Fábio.

Hi Fábio,

I am not sure I understand the problem. When you say missing, are you referring to parts of your query that get lost or about missing data when you test out the query?

In the first case, I have seen this happen before. When you have a complex query, DW is not always able to successfully parse and display the query again. You are managing the query with the Advanced query dialog, right?

In the latter case, can you give a bit more detail? Maybe post your code as well?

And no, not since I was ten. I got into programming relatively late... ;)

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Hello, Imar!

Thanks for the always gentle and quick reply.

> ..are you referring to parts of your query that get lost... ?

I guess not. I mean, I let Access do the query itself (and, at it, it all seems perfect!) and in Dreamweaver I simply created the recordset that "calls it" (yes, I think by the "Advanced query dialog", i.e, at the Application > Server Behaviors > Recordset (query) tool, ok?!). The code generated is for example:

<%
Dim rsQryRecent
Dim rsQryRecent_numRows

Set rsQryRecent = Server.CreateObject("ADODB.Recordset")
rsQryRecent.ActiveConnection = MM_connNAME_STRING
rsQryRecent.Source = "SELECT * FROM qryNAME ORDER BY Weight DESC"
rsQryRecent.CursorType = 0
rsQryRecent.CursorLocation = 2
rsQryRecent.LockType = 1
rsQryRecent.Open()

rsQryRecent_numRows = 0
%>

Is it what you mean by "When you have a complex query, DW is not always able to successfully parse and display the query again." ? If so, what you suggest, please ?

> ... or about missing data when you test out the query?

I would guess it is exactly this but, well, I guess you have a better idea what is going on (or not going on) here...

Thanks for your help!

Best regards,
Fábio.

In that case, I still don't get exactly what the problem is.

I thought that Dreamweaver messed up your SELECT statement whenever you tried to modify the recordset. Apparently, that's not the case. DW should be able to correctly put your SELECT statement back in the query box.

So, the question is: what exactly is the problem? Are you missing data when you press the Test button? And what happens when you run the page? Do you get all our data? If you do get the data at run-time, does it matter DW doesn't see all the data? And are you missing records or columns? (in the latter case, I can imagine it's problematic if DW doesn't see all your data).

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004

Hello Imar, thanks again.

> Are you missing data when you press the Test button?

Yes, in the sense that the query box Test view shows us a "matrix" with "missing" elements, "as if" the Access query related was with many Null cells. It is not.

> And what happens when you run the page?

I get no errors, the page loads but there are missing fields "as if" the Access query, where this recordset is based on, was with a correspondent Null value. It is not, sure.

This would be a typical Access area from each the recordset extracts data:

 1 2 3 4 5 6
 7 8 9 10 11 12
13 14 15 16 17 18
19 20 21 22 23 24
25 26 27 28 29 30

And that is what the query view Test button shows us (just example):

 1 2 3 4 5
 7 8 10 11 12
        15 16 17 18
19 21 24
25 26 27 28 30

It doesn´t seem to have a "rule" to what to show or not...

And the fields missing don´t appear at the "end-page".

> If you do get the data at run-time, does it matter DW doesn't see all the data?

Nope, it would not matter. But we do not get the data at run-time... :(

> And are you missing records or columns?

There seems to me part of columns, to be precise. Not records for sure because the number of rows are coherent to what I should expect in each situation.

Therefore we are at what you called problematic, I am afraid.

Another thing: I tried to see if there are "circular" reasoning, in the sense of variables named equal or similar in queries, consecutives or not, etc. I took it out every thing "near that" but the problem persists.

Another detail: I tried to by-pass the problem creating "make-table" queries and copying the Access SQL-generated to my ASP-page as I´ve already done before (successfully) but I get errors like

-------------------------
You tried to execute a query that does not include the specified expression 'IIf(IIf(IsNull(tblFIFO!SharesLeft),0,tblFIFO!Shar esLeft)=0,[SgnQtyShares],[SharesLeft_aux])' as part of an aggregate function.
---------

The fact is that I don´t have a IIf(IIf(... structure hand-coded, although this is correctly created by two different query-fields expressions "in chain". Is it the "source of all evil" , perhaps? Do DW has difficulty in dealing with complex queries in the sense that their fields are "sequentially" dependent? Even if (it is the case) Access itself can manage these queries perfectly (and quickly) ?

Well, I guess we have a problem, Imar, don´t we?? :(

I will keep trying to substitute 6 for half-dozen to "get through the passage without opening the gate" but... it would be nicer and easier to be able to "walk normally". Even for future needs...

I´m looking forward to your opinion on this strange ocurrence.

Thanks a lot for your support!
Fábio.

Right, I see now. That *is* indeed a problem.

Unfortunately, I don't know how to fix it. I know there's a difference between how Access parses a query, and how the OleDb provider parses / uses it. There are some Access specific functions that you can use from within Access, but not directly inside a SQL statement that you fire against an OleDb connection.

However, I never would have guessed that would apply to queries, as I assumed they fired inside Access. Apparently, that's not the case as your problem seems to be related to that...

I hardly use Access queries for the applications I design. For many web sites, we use Access, but data access is always relatively simple in such sites. Whenever we need to do complex logic in queries, we use SQL Server and stored procedures instead.

However, SQL Server comes at a (serious) prices, depending on your requirements and the licensing model you need.

Sorry I can't help much with this; this is something I haven't encountered before...

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004

Hi there, Imar!

I am very grateful to hearing from your experience and you don´t have to apologise at all!

I have strongly suspected that the problem was not with my queries nor with my/the DW recordset creation: with many "isolating errors techniques" I am developing ;) , I could see where a constant value worked ok and where a value that came from another query worked wrong... and all this working perfectly inside Access environment but giving trouble at the interface Access-Dreamweaver (that is Access - SQL - OleDb connec as you explained) ... In other words, you validaded my feeling and you put it all in a technical vocabulary that I couldn´t for sure but... that I understand fully after breaking my head on the wall during many many hours.

The good part of all this is that even before your last answer, I started to substitute "each hard expression" at my Access queries with "many very silly" ones and making some of the critical queries necessary for the subsequent ones as tables (through the make-tabe query associated...) and getting the SQL generated automatically as the source for the SQL at my asp page, to run the make-table query through my web page and then use the sequential query dependend now on the newly created table, instead of on the problematic query-query that existed before...(I hope you understand me!) AND IT WORKS! (In short, I by-passed the problem you explained very well: "There are some Access specific functions that you can use from within Access, but not directly inside a SQL statement that you fire against an OleDb connection.")

>> I hardly use Access queries for the applications I design. For many web sites, we use Access, but data access is always relatively simple in such sites. Whenever we need to do complex logic in queries, we use SQL Server and stored procedures instead.

I am happy to realize that I am really coming to the necessity of moving from Access to SQL and from DW to Visual Studio, right?! I am getting stronger! ;)

>> However, SQL Server comes at a (serious) prices, depending on your requirements and the licensing model you need.

I saw "you" opened a specific forum here for your new book and that you strongly suggested someone there to start using the SQL Express Edition before entering the SQL Server "arena", right?! I will respect that (also because of $ restrictions, sure, but the main point is what you recommend, no doubt) , and I will try to start your book TOMORROW, even if only to use at say 30min a day while I am too busy with this Access database etc.

Wait for my questions at your newly created "Instant Results" forum! :)

Well, thanks a lot. You are really great. Your good-will in helping us is simply marvellous. Not to mention your always-secure, clear and gentle answers.

With my best wishes,
Fábio.

Just be prepared for a rough ride; ASP and ASP.NET are not the same thing.... If you have no prior .NET experience, you may also want to take a look at Beginning ASP.NET 2, also by Wrox. Whenever a book is published by Wrox, the authors and the readers get a free forum that belongs to the book. That way, book specific topics can be discussed between the writers and the readers. I didn't open the forum, Wrox did. I just visit and use it to help readers of my book.
You're more than welcome there....

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
While typing this post, I was listening to: Black Angel by The Cult (Track 12 from the album: Love) What's This?

Thanks for the feedback, Imar!


> ... you may also want to take a look at Beginning ASP.NET 2, also by Wrox.

Thanks for the recommendation and the warning. I´ve bought Bill Evjen, Scott Hanselman et all "Professional ASP.NET 2.0" Wrox book together with yours (that is, at the same package)! Yes, I know I should have bought the "Beginning" one to start with but, well, let us see what/where can I do/go from now!

> Whenever a book is published by Wrox, ...

I know that! That´s why I put the quotes at the word "you" :)

> You're more than welcome there....

Thanks a lot. We will "talk" about all this new stuff (to me) in a short-time, I hope.

Best wishes,
Fábio.