Advice on a good strategy

I have a general question and a specific question.

1.) I'm doing some reporting using the Crystal Reports software my company has installed; however, I see threads of people using all kinds of more powerful software (VB, C, etc) to work with Crystal (web development, etc). Crystal Reports is on a terminal server here, and my audience will use that software to view the reports. Am I then limited to the Crystal (or Basic) syntax found in the software, or can I somehow use something like Perl or C to do my programming? I'm not sure how all of this goes together.

2.) More specifically, I'm trying to build a parts list tree (parent/child). The database is organized such that it makes this correlation difficult. In one Table (Part Master), is a list of all unique parts. In another table is a list of all unique jobs (times we've made the parts) (Jobs) that would show the part being made (one part shown) (link to Part Master). In the third table (Materials), the Jobs are listed with all of the sub components used for the main part for that job. Here, I'd want to capture the sub-components, but then loop back through the original Part Master to capture THEIR subcomponets, and so on. There could be up to 5 levels of this.

Basically, I want a user to enter a top-level assembly number, then I want to show every part used for that assembly, all the way down. My problem is that I really need to loop through the original list of returned records (I think). If I find the first part, and the first level of sub-parts, I may have passed those records already, so I need to re-read the list. I attempted build an array of the entire list of parts and do the logic at the end, but there are 82,000 of them (limit is 1000 and I don't plan to do Case with 82 levels). I'm inclined to believe that Crystal Reports (v.10) does not support multi-dimensional arrays, making this even tricker.

Can someone help me on a strategy to attack this one?Well, first you probably need to realize, that even if they are using VB or C-whatever or anything else in conjunction with Crystal, that Crystal is still a data-analysis/reporting engine. All it does is spit out the data in whatever format you design the report to do so. You can connect directly to your database (I am guessing it is a relatively standard engine, although you don't mention it), and then use crystal to select certain sets of data, group that data, and summarize that data, no matter what the general GUI interface to your data is. So just use crystal, point it to your database through ODBC or OLEDB (or whichever method you want), and go. Now, how you allow your users to view those reports is another matter, you might put shortcuts on the desktop (if the full version of CR is accessible through the terminal server), or use or develop a CR viewer for them to open the reports in. For that, you would need another tool, such as VB or C-whatever.

It does sound as if you have several layers of one-to many relationships to account for in the description of your project. My presonal experience leads me to usually start for the smallest partion, and work up. If you want a jobs report, that will show the detail down to the smallest sub-part, you would probably know that you need to start from an AssembyID, display each individual part, and below each part, a list of sub-parts. So you would add each of those tables to your report and link them by their primary keys (I usually use a left join if there is any possibility of missing links, i.e., a sub-part entry that is not in the table, otherwise an inner join would be more efficient). Then group by jobid, partid, and sub-part id. Add all the fields you want for each job (be sure to keep the field boxes in the correct group).

Obviously, this is hugely simplified and requires a lot more, but that is a basic approach. More detail could be given, if you give me a list of tables and their relationships.

ScottJ

Advanced SQL tutorials in Problem/Answer format

I'd like some challenging practice with SQL; general SQL-92 or MS specific (sql server or access).
There's a million sources on the Web, but most are like reference manuals. I already have MSDN with my VS 6.0 and .Net, as well as help files for 3 different versions of Access. I'm looking for something set up more like assignments or challenges. I found a couple sites like this:
SqlZoo.net and SQLCourse.com

SqlZoo has too many mistakes and vague questions. SQLCourse is pretty good, but no advanced stuff (some multiple Joins, subqueries, calculated fields and such). I like the idea of having the same related tables that you keep solving different problems with. This is more like a real-world situation. I would think that there would be a lot of question/answer tutorials for the MS supplied sample db's (Northwind, Pubs, etc.), but I haven't found any.

These forums are full of good questions posed by people, but they always concern unfamiliar data sources and I have to slog through the posts with my slow dial-up connection. I would rather do one big download and work at the problems offline.
Any suggestions?if you want the same related tables that you keep solving different problems with, your best bet is to search for the more common databases like northwind and sakila, perhaps by throwing several of their tables names into a search

if you want advanced question/answers, i humbly offer my own (the actual articles are on the techtarget.com site)

see http://r937.com/sqlate.cfm|||Thanks. There's a lot of good questions in one spot; I can easily download a bunch of those pages. Since the questions are all at the top of the pages, I can read them without seeing the answers. You helped save me a lot of searching time. If I do find some Northwind-based question tutes, I'll post the link.
Thanks again.|||Ahh, I find I learn more than enough from here :p
Just subscribe to SQL/Access threads that you are interested in.
I've knocked up a test database purely for solving problems on here -
trying to recreate peoples problems and see if I can solve them.

It's often a really good way to learn when you're given an unfamiliar problem, because you try and break it down into a generic solution (which means you can use it again later!).

There's my 2 cents. :cool:

Advance SQL question

Hello everybody,

i have a advance question about a specific sql problem:

My table A have for example 3 columns.
in the third column are words seperated by ~.

ID COL2 COL3
-----
1 ab test~dummy~ddd
2 cd testdata2~sjhfdg~sdf
3 ef sd~test
4 gh sd~cv

Now i want two lists:

1.) used Values for column 3:

Values
--
test
dummy
ddd
testdata2
sjhfdg
sdf
sd
cv

2.) used values plus ID
Value ID
----
test 1
test 3
sd 3
sd 4
cv 4
dummy 1
...

Is it posible to produce such a list with nearly one SQL -Statement or with
temporaly tables ?

Thanks in advance

T.Kindermann
Database Administrator

--
-----------------------
Thomas Kindermann
E-MAIL: Reply to TKINDER<x>@.GMX.DE without <x>Thomas Kindermann wrote:
> Is it posible to produce such a list with nearly one SQL -Statement ?

Yes, it is possible:

SELECT DISTINCT substring('~' + COL3 + '~', Number + 1,
charindex('~', '~' + COL3 + '~', Number + 1) - Number - 1) AS Value
FROM (
SELECT TOP 250 number
FROM master..spt_values WHERE number>0
GROUP BY number ORDER BY number
) Numbers, TheTable
WHERE Number <= len('~' + COL3 + '~') - 1
AND substring('~' + COL3 + '~', Number, 1) = '~'

SELECT ID, substring('~' + COL3 + '~', Number + 1,
charindex('~', '~' + COL3 + '~', Number + 1) - Number - 1) AS Value
FROM (
SELECT TOP 250 number
FROM master..spt_values WHERE number>0
GROUP BY number ORDER BY number
) Numbers, TheTable
WHERE Number <= len('~' + COL3 + '~') - 1
AND substring('~' + COL3 + '~', Number, 1) = '~'

This queries work with up to 250 words in each row.

However, it may be better to use other ways. For more informations, see
this excellent article by Erland Sommarskog, SQL Server MVP:
http://www.sommarskog.se/arrays-in-sql.html#tblnum-core

Razvan|||Am 23 Jun 2005 02:39:18 -0700 schrieb Razvan Socol:

> Thomas Kindermann wrote:
>> [1 zitierte Zeile ausgeblendet]
> Yes, it is possible:
> SELECT DISTINCT substring('~' + COL3 + '~', Number + 1,
> charindex('~', '~' + COL3 + '~', Number + 1) - Number - 1) AS Value
> FROM (
> SELECT TOP 250 number
> FROM master..spt_values WHERE number>0
> GROUP BY number ORDER BY number
> ) Numbers, TheTable
> WHERE Number <= len('~' + COL3 + '~') - 1
> AND substring('~' + COL3 + '~', Number, 1) = '~'
> SELECT ID, substring('~' + COL3 + '~', Number + 1,
> charindex('~', '~' + COL3 + '~', Number + 1) - Number - 1) AS Value
> FROM (
> SELECT TOP 250 number
> FROM master..spt_values WHERE number>0
> GROUP BY number ORDER BY number
> ) Numbers, TheTable
> WHERE Number <= len('~' + COL3 + '~') - 1
> AND substring('~' + COL3 + '~', Number, 1) = '~'
> This queries work with up to 250 words in each row.
> However, it may be better to use other ways. For more informations, see
> this excellent article by Erland Sommarskog, SQL Server MVP:
> http://www.sommarskog.se/arrays-in-sql.html#tblnum-core
> Razvan

GENIAL SUPER,

you are my good ;-))))))))

Thanks

Thomas
--
-----------------------
Thomas Kindermann
E-MAIL: Reply to TKINDER<x>@.GMX.DE without <x>

ADO.NET OleDb SET ROWCOUNT or DBPROP_MAXROWS

I am writing a data access tool that needs to be non provider specific. I have used System.Data.OleDb to access a variety of data source types (MSSQLServer, MSAccess, MSExcel, CSV, Oracle, XML). This works beautifully. Thankyou Microsoft for giving us ADO.NET2.0.

However, I now need to restrict the number of rows returned from a SELECT statement. Is there any way I can I achieve this in a non provider specific fashion?

So far searches have yielded DBPROP_MAXROWS however I can find no way of setting the DBProperties using ADO.NET2.0. Is this possible?

Documentation for DBPROP_MAXROWS states that it uses "SET ROWCOUNT n" as part of the command text. I tried this and it works but not for the JET4 provider (this is a problem since I use JET4 to access .mdb, .xls and .csv files)!

Any tips here would be greatly appreciated. Even if somebody were to tell me that it's just not possible, at least that would put me out of my misery :)

Thanks.

Because of the way JET accesses data, bringing all of the data to the client before processing the criteria, I don't think that there is a way to make that work.|||

Thanks for the reply Arnie,

since posting I have done some more research and discovered that using TOP works for Jet. This is good for me as it also works for SQL Server. The approach I have taken is as follows:

int maxRows = 100;

string someQry = "SELECT * FROM table1";

string topQry = "SELECT TOP " + maxRows.ToString() + " * FROM (" + someQry + ") as topSubQry";

However it doesn't work for XML files. To get data from XML files I use a connection string of "Provider=MSDAOSP.1;Data Source=MSXML2.DSOControl.2.6;" and then the full path to the XML file as the command text. I am guessing there will just be no way to limit the rows returned when using this provider.

ADO find method too slow, how should I do this

I need an efficient way to get the absolute position of a record in a query matching a specific key value. The Find method is a serial search, too slow for big data sets. I am using both SQL Server Express and Jet 4 via ADO.

One example of why I need this .....

I have a list control with a subset of a table (controlled by where clause in query). I want to save the current state of the list control and later restore it when the app restarts. I want to preserve and restore the current line selection in the list control.

So, I save the key value for the current line, upon restart use the find method to locate the key, and set the list control current record index to the current absolute position.

This is too slow for big data sets since the find does a record by record search.

I cannot just save and restore the list control offset, the table may have changed.

The list control has owner data so the data is not all read into the control, so I can't just search through the controls image.

Any ideas. I did search for this answer and failed. Feel free to flame me as long as an answer is included too :-)

Z

Hi,

Here's a quick idea: instead of doing the Find, why don't you simply run a query like "SELECT COUNT(*) FROM mytable WHERE tablekey <= mycurrentkey", this would give you the desired "absolute position" in the recordset and you could simply manipulate the selection in the listbox.

I understand this may go out of sync with the original table, but you could mitigate that - for instance, enclosing both the listbox population and the query above into a transaction (probably with higher isolation level).

And one more (simpler) idea - many of the list and combo-box controls have their own Seek methods. You can use that and position into the control instead of going through the recordset. The search will be local into the already populated data.

HTH,

Jivko Dobrev - MSFT

--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Another idea that might work is manually do a binary search on the keys in the listbox (if the items in the listbox are sorted by key this will work). Should be faster than find.

Likewise if your key is ordered you could do what Jivko mentions but something like this:

select count(*) from table where <conditions that restrict items> and key=<saved pkey from last time>

Count should give you your absolute position or zero if the record is deleted.

AdjustTokenPrivileges Error?

Hi,

I have created two packages.. a child package and a main package that is responsable by executing all his childs in a specific order...

What start happening is that the child when executed alone is ok, but the the main package executes it... then i get a DOS Windows saying "AdjustTokenPrivileges () Failed (00000514)"

What can be causing this? I haven't changed anything and the package was executing right some days ago...

Best Regards,

Luis Sim?es

I have one package that is causing this error as well. Anyone know why? It only generates the error when I try to insert into a database. otherwise it does not generate this error and runs fine.

AdjustTokenPrivileges Error?

Hi,

I have created two packages.. a child package and a main package that is responsable by executing all his childs in a specific order...

What start happening is that the child when executed alone is ok, but the the main package executes it... then i get a DOS Windows saying "AdjustTokenPrivileges () Failed (00000514)"

What can be causing this? I haven't changed anything and the package was executing right some days ago...

Best Regards,

Luis Sim?es

I have one package that is causing this error as well. Anyone know why? It only generates the error when I try to insert into a database. otherwise it does not generate this error and runs fine.