 |
 |
|
|
|
|
 |
|
|
Samples and other shared info.
| You are not authorized to post a reply.
|
|
| Author |
Messages |
|
mlarsen
Posts:49
 |
|
Domain-0
Posts:31
|
| 10/19/2006 2:48 PM |
 Alert
|
Excellent! Much thanks!
-Brian |
|
|
|
|
AlexK
Posts:123
|
| 05/09/2008 4:21 AM |
Alert
|
Hi,
Would it be possible to custom page an Excel file as well? I used both examples as a starting point, everything I do results in an SQL syntax error. Maybe my statement is wrong, but it might also be that such a thing is not possible.
Best regards,
- Alex. |
|
|
|
|
robert_chumley
Posts:595
|
| 05/09/2008 9:38 AM |
Alert
|
Hello Alex,
When you say page an Excel file, are you referring to content that is being returned with an application/vnd.ms-excel content type and loaded in the browser? Or are you referring to a file that is loaded based on a link?
Thanks, |
|
Robert Chumley
r2integrated (formally bi4ce) |
|
|
AlexK
Posts:123
|
| 05/09/2008 11:04 AM |
Alert
|
Hi Robert,
It's an Excel file that gets uploaded by a user. This file gets verified by checking a column against a SQL 2005 column. When it gets the green light, a masive form is populated by the Excel contents. To be more precise; the Excel file states 78 columns for an item. There can be several hundreds of items in one Excel file. The default paging helped in getting a workable solution. However, since all fields are also validated on the client, things are not very fast. I'm trying everything to gain seconds, and custom paging can be hugely profitable. I think it's the AJAX population of the page part that takes most of the time. However, I want to try if building custom paging for the Excel uploads gains anything. Can it be done?
Best regards,
- Alex. |
|
|
|
|
robert_chumley
Posts:595
|
| 05/09/2008 4:49 PM |
Alert
|
Hello Alex,
This sounds like a pretty interesting exercise. I would like to see exactly how you are loading the data first. Are you storing the information in SQL Server first, then doing the validation? How long does it take to just load the data without the validation? Is there any way you can cache the excel file client side? (using google gears) ;-) Gears has become my new best friend. I have a demo setup to use it in an environment. You should be able to find the example here....
http://www.bi4ce.com/Support/Forums/tabid/106/forumid/7/tpage/1/view/topic/postid/10171/Default.aspx#10184
Let me know if you need any help.
Thanks,
|
|
Robert Chumley
r2integrated (formally bi4ce) |
|
|
AlexK
Posts:123
|
| 05/13/2008 3:03 AM |
Alert
|
Hi Robert,
It is indeed challenging, though ListX seems to cope quite well with implementing explicitly wild ideas. Storing the data in a temporary SQL column through CSV was my initial idea, but the powers that be decided it was far more easy to just upload Excel files, because all managers understand Excel, which is not the case for CSV. I tried to explain to no avail that CSV can easily be exported from any Excel file. So, Excel is now "my" Google Gears, as it is the off line database. I did look at the Google Gears example that is uploaded elsewhere. Very nice indeed! And very interesting to play with. However, since everybody is supposed to have Excel, users are not supposed to want to login first before they can enter data, nor are they supposed to want to use any off line application besides Excel to enter data. So far my specs.
The file gets uploaded fine, I'm giving it a random name. With that, I create the connection string, which needs to be done in two steps for ListX to correctly build it. Then I can read it, and start checking if all required data is there, and if the data is in fact theirs. If all is well, I redirect to the same page, which then shows the data as I'm using {IFF...} in my query section. The data is shown by building a huge form, of over 70 form fields per item. There can be hundreds of items, maybe thousands in the future. Therefor I'm using the Prototype library in order to build an accordionesque form that only shows the unique item ID, which can be expanded by clicking on it. Also, I use the (altered) ListX validation on each and every form field. This visually marks all items, and within the accordion each field, that is not correctly filled. All goes well. One problem is that certain Excel files refuse to be read by the Jet driver for some reason. But that's not my main issue. The main issue is speed.
When I pick a pagesize of 50, it still works, but a pagesize of 100 results in a ListX XML error. When I strip the client side validation, I don't have these errors. I get around this by altering the drop down with JavaScript, as I described elsewhere in this forum. However, I wonder if general speed can be improved by using custom paging. I'm using the default paging system now, but I read everywhere that custom paging accelerates considerably. I just can't seem to get that Excel to page in a customized way.
Wow, that got to be an elaborate story ... pardon the lengthiness.
Best regards,
- Alex. |
|
|
|
|
wlevy
Posts:66
|
| 05/15/2008 1:52 PM |
Alert
|
| Can you send a sample of the SELECT statements you're trying to use to retrieve subsets of data from Excel? |
|
|
|
|
AlexK
Posts:123
|
| 05/16/2008 4:12 AM |
Alert
|
Certainly. The last query I ran was:
WITH orderedResults AS (SELECT *, ROW_NUMBER() OVER (ORDER BY [qtid]) AS RowNumber FROM [Blad1$] WHERE ([qtid]>0)) SELECT * FROM orderedResults WHERE RowNumber BETWEEN ((1-1)*5)+1 and (1*5);
And the error that came with it is:
System.Data.OleDb.OleDbException: Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
The above query is built on a SQL2005 example. I also tried the 2000 example as well as the Oracle example, which the documentation states. No luck with either one of these, so far. Both the "Jet4.0" and the "ACE" OLEDB drivers give the same result. |
|
|
|
|
bgates
Posts:197
|
| 05/19/2008 10:45 AM |
Alert
|
The JET drivers you're using are not setup with SQL 2005 syntax. I don't know if any of them are. You may be able to use the SQL 2000 style paging. But I wonder about another approach here.
What if when the file was uploaded, you imported into a table with a random name. Then, you're working with the data as though (and because it is) it was just SQL data. If you keep working with Excel directly, then you'll be bound by the JET drivers, which are way behind the normal SQL ones. |
|
Bob Gates
Business Intelligence Force, Inc. (bi4ce) |
|
|
|
| You are not authorized to post a reply. |
|
|
|
ActiveForums 3.6
|
|
| Latest Post
|
 |
|
At R2integrated (formerly Bi4ce), we take support seriously. That's why we support our customers and DNN community with daily monitoring from our experienced engineering team. We ask that the first step taken is to read the relevant documentation and support forums prior to submitting any questions that may already be available or have been answered. We ask that you review the documentation that we provide for our products before posting a question.
The Forums are for our customers to chat, exchange ideas and strategies, and submit feedback. Please be sure to perform keyword searches for previous related forum responses.
To be helpful when submitting a new item, please include the following:
- DNN Version
- Module Version
- Admin Log Viewer Information
- Environment detail: Operating system, .NET framework version, database and version, IIS version, Browser version (if appropriate)
We always try to respond quickly and monitor the forums daily during business hours (EST). Occasionally, requests for a specific project requirement may not apply for the free support offered. For project specific support please submit via our Information Request form.
Thank you for using our Forums.
Click here to register for the Forums
|
|
|
|
| © 2008 by R2integrated (formerly Bi4ce) | DNN® is a registered trademark of DotNetNuke Corporation
|
 |
|
|
|
 |
|