×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

General Help with table required Please

General Help with table required Please

General Help with table required Please

(OP)
Hi all,

I have a SQL Server Table called Sample_Table which contains 70,000 records which are separated in 100 different areas by a field called District

What i have been trying to do is to extract into a separate table called Sample_Data_Table this i have to work as below one of each of the districts.


SqlDataAdapter da = new SqlDataAdapter("SELECT Distinct District FROM Sample_Table ORDER by District", cs);

// Create the Data Table
DataTable Sample_Data_Table = new DataTable();

// Fill Data Table with Records
da.Fill(Sample_Data_Table);


What i then want to do is to somehow loop around this Sample_Data_Table and then to extract out into 100 different tables / files all of the records that match the relevant field called district.

foreach (DataRow row in Sample_Data_Table.Rows)
{
SqlDataAdapter OutPut_Table = new SqlDataAdapter("SELECT * FROM Sample_Table WHERE Sample_Table.DISTRICT = Sample_Data_Table.Rows", cs);

// Create the Data Table
DataTable Export_Table = new DataTable();

// Fill Data Table with Records
OutPut_Table.Fill(Export_Table);

// when i get to this stage i need to be able to copy the table to a fox pro free standing table
// any help with this command string would also be welcome as i have not done this before
}


Currently this is not quite working and i am at a loss as to how to get it to work

thank you in advance

rdrunner40

RE: General Help with table required Please

If I undertand you correctly, you want to create one DataTable, in memory, for all the records that match your distinct districts? Is that correct?

If so the sql in your loop is incorrect, it should look like this

CODE

foreach (DataRow row in Sample_Data_Table.Rows)
{
SqlDataAdapter OutPut_Table = new SqlDataAdapter("SELECT * FROM Sample_Table WHERE Sample_Table.DISTRICT = '" + Convert.ToString(rows["DISTRICT"])+"'", cs);
.
.
.
}
Thats assuming the DISTRICT datatype is a string. But I have to ask, why do you want to do this? Do you need to create a separate SQL table for each district type?

RE: General Help with table required Please

(OP)
Hi

Yes what i have to do is to create 100 Print Files as the job is to to be sent to a laser printer for printing hence why i need all the seperate files.

The District type is a string and consists of the letters BA1, BA2, BA3 and so on ....

Once i have all the data that applies to the BA1 district ( there is approxiametly around 85 fields .... i then need to be able to do some special sorting and updating of this table so i would like to do this as well..

As this job is being printed 2up on a A4 Sheet of paper i have to do a spilt sort on this OutPut Table as well...... so assuming there is 1000 records in district BA1 i have to have the table in the following print order .....

Record No 1
Record No 501
Record No 2
Record No 502

This is meaning that i have to have a unique record NUmber before i do the sort part to generate the output file in this particular format.

Then once i have done all of that then i want to send the file to a folder with a name like BA1_N as a Visual Fox Pro Stand alone table if possible.

Is this making sense ?

regards

Rdrunner40

RE: General Help with table required Please

based on your original post, I was under the impression that you were unable to split the data by district, is that still the case?

RE: General Help with table required Please

(OP)
Hi

I have pasted in the answer that you forwarded to me in your first reply and i now are able to split the Main Table into the various districts. That is so easy when I have seen what you forwarded to me to use .....

The data adapter that i have created called Export_Table am i able to do any updating of it in this state ? ie adding a unique number to each record ?

I thank you for that answer so quickly ....

Are you able to assist with the next part of what i am trying to do with the sorting of the file and then copying out to a fox table ? I am totally new to c# yet i am a competent visual fox pro programmer and this is all new here.

Regards,

rdrunner40

RE: General Help with table required Please

Glad to hear that helped.

Now to confirm you want to add another "column" to your eport table? If so then will need to do something like this.

CODE

DataTable Export_Table = new DataTable();
Export_Table.Columns.Add("YourNewColumnName", typeof(Int32));

/// fill your data table

/// now you will need to loop through each
/// record to value your new column
int uniqueInt =0;
foreach(DataRow dr in Export_Table.Rows())
{
dr["YourNewColumnName"] = uniqueInt;
uniqueInt++;
}

I am not familiar with fox pro so I cannot offer much help there but I'd you need more assistance with the above code let me know.

RE: General Help with table required Please

(OP)
Hi

That is exactlty what i am wanting to do is to add another field to the table.

At this point i would then like to add text into this field in each of the output tables "sample text"

Then i would like to either export this table out as a FoxPro or DBase table or if not like that then as pipe delimited export file called "District" what ever the value of the district is for the particular District that is being processed.

Boy this is so different to fox but at least i am learning at the same time

Regards

rdrunner40

RE: General Help with table required Please

If what your really need is a file of the data for each district, I would do the following

This will create one file for each district. Each row will be separated by a |.

CODE

foreach(DataRow dr in Sample_Data_Table.Rows())
{
string district = Convert.ToString(dr["District"]);
using(StreamWriter swNewFile = new StreamWriter(@"c:\temp\"+district+".txt"))
{
/// dr.ItemArray will creat an array of each column in the current row
foreach(object o in dr.ItemArray())
{
swNewFile.Write(o + "|")
}
/// now you can append your additional data
swNewFile.WriteLine("custom data");
swNewLine.Flush();
}
}

I am typing this from my phone so of something does not compile correctly don't let me know.

RE: General Help with table required Please

(OP)
Hi,

I will try tommorrow what you have sent to me today I was unwell.

Thank you for your help

Regards

rdrunner40

RE: General Help with table required Please

sounds good, feel better. Let me know how it goes.

RE: General Help with table required Please

Hey rdrunner400, Just checking in to see how this went.

RE: General Help with table required Please

(OP)
Hi,

I am going to be attacking this over the weekend ... so will let you know as i am working all weekend.

Currently i am working on another project that is taking all my time :(

Regards

rdrunner400

RE: General Help with table required Please

Hi, just to give a slightly different opinion. From what I gather, you basically want to:
1) Get records per district
2) Perform some updating
3) Sort table
4) Transform sorted table to DBF

For the printing part, I assume your report generator is bound to the DBF.

I'm not sure what sort of updating you need to do, but normally when preparing reports, using computed columns most of the time will suffice. Of course, it depends on the complexity of what you need to update.
But, is it not possible to use SQL computed columns? For example,

CODE

select
upper([Field1]) AS [A],
case
when [Field2]
then [Field3]
else [Field4]
end AS [B],
Field5
from Sample_table
where District = @district;

Then sort the table based on the column(s) you need in SQL. If you have SQL 2005, you can utilize the row_number() function to generate the row index and common table expression (CTE) to return a custom view.

CODE

with t(A, B, C) AS (
select
upper([Field1]) AS [A],
case
when [Field2]
then [Field3]
else [Field4]
end AS [B],
Field5
from Sample_table
where District = @district
)
select row_number() over(group by A, C) as rowIndex, A, B, C
from t;

Then using an SqlDataAdapter, get the table and transform it to whatever data format, as already suggested.

So far, from this point of view, I don't see the need to create that many dataTables.

In addition, if you want to sort the table based on this manner (1, 501, 2, 502, 3, 503...), you can do this:
1) Make sure the RowIndex column to decimal type. (from SQL, just cast the RowIndex to decimal)
2) Starting on the next index from the middle record, update the value by adding the iterator and 0.5

CODE

int midpoint = tbl.Rows.Count / 2;
for(int i = midpoint + 1; i < tbl.Rows.Count; i++)
{
tbl.Rows[i]["RowIndex"] = (i + 1.5); // need to add 1 because iterator is zero-based.
}

Hope this helps. If not, can be a useful C# reference :)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close