|
Lets try to sort this out one bit at a time.
In Form1 you have:
private DataSet Sds;
public DataSet GetDocs
{
get
{
return Sds;
}
set
{
Sds = value;
}
Then in Form2 there is:
object GD = GetForm1.GetDocs;
Since the GetDocs property in the first block above is the only public GetDocs on Form1, it must be that one that GD points to. The getter for GetDocs returns Sds. Now I cannot see anywhere on either form where Sds gets assigned to. I could well be wrong, however because of the way you have named the various members, it is very very difficult to follow what is happening.
It is recommended (in the Microsoft Style Guide for C#) that where there is a private field with a public accessor (your Sds and its property GetDocs, from the first code block above), the names should be the same but differentiated by case.
So can I suggest that you rename those to match that style. First, just to make it easier to work through please use Sds as the name for now, you can change it to whatever you want later.
1. Hover the mouse pointer over the Sds in the line private DataSet Sds;
2. Right-click
3. From the context menu, hover on 'Refactor' and from the sub-menu select 'Rename'
4. A Dialog will pop up with Sds in a textbox, change that to sds (all lower case) and click OK.
If all has gone well the code should look like this
private DataSet sds;
public DataSet GetDocs
{
get
{
return sds;
}
set
{
sds = value;
}
Repeat the above 4 steps for the GetDocs from the line public DataSet GetDocs and change it in the textbox to Sds and click OK.
Again if all has gone well you should have
private DataSet sds;
public DataSet Sds
{
get
{
return sds;
}
set
{
sds = value;
}
With any luck at all the Refactoring will have taken care of Form2 as well, and the line object GD = GetForm1.GetDocs; on Form2 will have become object GD = GetForm1.Sds;.
I am sorry if I have over simplified anything in this post, or explained something you already know how to do. I am not intending to be insulting, just trying to make sure we are on the same wavelength. 
Come back when you are there.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
Hi Henry,
I have made the recommended changes. Also, I don't think using DataSet for the GetDocs property was correct so I changed it to DataTable. Here is what I have:
Form1
string Query = ("SELECT * FROM [" + dataGridView1 + "]");
DataTable Sds = new DataTable(Query);
DataRow myNewRow;
myNewRow = Sds.NewRow();
Sds.Rows.Add(myNewRow);
private DataTable sds;
public DataTable Sds
{
get
{
return sds;
}
set
{
sds = value;
}
}
Form2
GetForm1 = new Form1();
object GD = GetForm1.Sds;
this.dataGridView1.DataSource = GD;
modified on Monday, May 18, 2009 1:02 PM
|
|
|
|
|
Hi, have a good weekend?
The problem is, that there are still two members with the same name, which could get confusing. However if the compiler doesn't complain, why should I.
The next problem to tackle is the line string Query = ("SELECT * FROM [" + dataGridView1 + "]");
I don't know if you know how to use breakpoints, the debugger and single stepping, but if you do you will be able to determine that
it evaluates to
SELECT * FROM [System.Windows.Forms.DataGridView]
at runtime.
Whereas I suspect that you want it to get the data from the table whose name is selected in dataGridView1.
Is that correct?
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
I had a very productive weekend cutting down dead trees in my backyard. Not something I like spending time on but at least it saves me a few bucks. How about you?
I didn't use breakpoints to see if the query was working properly but I did throw in a MessageBox.Show(Query) to evaluate the string and it does say "SELECT * FROM [System.Windows.Forms.DataGridView] so I assumed it was correct.
What I would like to do is run a query on the dgv in From 1 to populate the dgv on form 2. From there it would populate a new data table and would be used as the data source for the dgv on Form 2. Right now I have it selecting all just to get the dgv on Form 2 to populate. Later the query will include a Select Distinct. Do you think this is a good way to do this or is there a more efficient method to code this?
Thank you,
Brenton
|
|
|
|
|
bwood2020 wrote: Right now I have it selecting all just to get the dgv on Form 2 to populate. Later the query will include a Select Distinct. Do you think this is a good way to do this or is there a more efficient method to code this?
This is a valid approach as 'proof of concept', and for now I would leave it like that, then, as you say, refine it later.
The problem with SELECT * FROM [System.Windows.Forms.DataGridView] is that System.Windows.Forms.DataGridView is not a table in a database and therefore there is nothing there to SELECT. However even if this worked, the data retrieved is not being assigned to private DataTable sds;, which is where Form2 is looking for its data. This problem can be solved with one simple change
Your code
string Query = ("SELECT * FROM [" + dataGridView1 + "]");
DataTable Sds = new DataTable(Query);
DataRow myNewRow;
myNewRow = Sds.NewRow();
Sds.Rows.Add(myNewRow);
Changed code
string Query = ("SELECT * FROM [" + dataGridView1 + "]");
sds = new DataTable(Query); <============= Here is the change, get rid of 'DataTable Sds' and replace it with just 'sds'
DataRow myNewRow;
myNewRow = Sds.NewRow();
Sds.Rows.Add(myNewRow);
That way you would be assigning the results of the query to the field that Form2 is using.
However, I do not believe that change will cause Form2s dataGridView1 to populate as there is no data being returned from your query.
All that
sds = new DataTable(Query);
does is create a new DataTable with its Name set to "SELECT * FROM [System.Windows.Forms.DataGridView]". That DataTable with the very long name is not attached to anything, it is not part of your database, it is an orphan.
Overall the problem is that Query is not being executed. So we need to get a correct string into Query and then get your query to execute and return a result.
Can I suggest that you look at look at Executing a Query that returns Rows[^], scroll down till you get to the section Executing SQL Statements that Return Rows Using a Command Object, which is what I think will be the best way for you to do it. For now, where it shows 'SELECT * FROM customer' replace customer with the name of one of your tables (I believe you mentioned one called Products previously, that would do nicely). This example leaves you with a SqlDataReader, called reader. You need to turn that into a DataTable. Luckily, someone has done that already so look here[^], remove the 'static' modifier and copy the method into Form1 and use that routine like this.
replace
string Query = ("SELECT * FROM [" + dataGridView1 + "]");
DataTable Sds = new DataTable(Query);
DataRow myNewRow;
myNewRow = Sds.NewRow();
Sds.Rows.Add(myNewRow);
with
the code from the first link (modified for your tables)
sds = ReaderToDataTable(reader);
If that gives a result that is something approaching what you want, we can then resolve the problem of getting the right stuff from dataGridView1 on Form1 into Query
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
I am caught up on the connection string and query for this. I should have made this clear in the beginning so I don't waste your time. The data source/s for this application comes in a file (.xls, .txt, and .csv). There is no SQL Server connection to import data (All the data will be pushed to a staging environment once the data has gone through the app). What I would like to do is connect to the original dataTable that is the source for my dgv on Form 1 and use it as the source for the dgv on Form 2. Is the connection possible? If I connect to the file then the query will fail once I add Distinct because not all headers will be the same and could come in all different ways. I take care of this by allowing the user to select a new header. The addition of the dgv on Form 2 is kicked off once a button has been clicked and that is where all of this comes into play. I went to &lt;a href="http://www.connectionstrings.com/"&gt;&lt;/a&gt; and I could not find anything that connects to a DataTable. It might be because I'm not looking in the correct place or may be overlooking something.
Also, I used product as an example and now I see I should not have done that because it does look like I'm trying to query a table in the database.
I hope I have not confused you. If I have I apologize in advance. If you need more information about what I'm doing then let me know.
|
|
|
|
|
OK.
Theoretically you can access the the DataSource from dgv1 on Form1 simply by using dataGridView1.DataSource, in which case putting this into your original Query, instead of just dataGridView1, might just work. Suck it and see.
If not, then if you could post the code that loads the data into dgv1 on Form1, so that I can advise you further.
BTW If you are dealing with .csv and .txt files, you might find FileHelpers v2.0 - Delimited (CSV) or Fixed Data Import/Export Framework[^] from here on CP useful. It won't help with the .xls files though.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
Good afternoon Henry,
I have tried to run the query using datagridview1.DataSource but doesn't seem to be working. Here is the code I run for csv files. Note that this doesn't show the entire method. In this method there are connections for the other file types.
<pre>
else if (comboBox1.Text == ".csv")
{
//Get file path from textbox
String FilePath;
FilePath = textBox1.Text;
//Connect to csv file
String comString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path.GetDirectoryName(FilePath) + @";Extended Properties=""Text;HDR=YES;FMT=Delimited;IMEX=1\""";
String strSql = "SELECT * FROM [" + Path.GetFileName(FilePath) + "]";
OleDbConnection conCSV = new OleDbConnection(comString);
conCSV.Open();
OleDbCommand dbCommand = new OleDbCommand(strSql, conCSV);
OleDbDataAdapter dAdapter = new OleDbDataAdapter(dbCommand);
DataTable dFill = new DataTable();
dAdapter.Fill(dFill);
conCSV.Close();
//create new dataTable and add new top row for new headers
DataTable newDataTable = new DataTable();
foreach (DataColumn col in dFill.Columns)
{
try
{
newDataTable.Columns.Add(new DataColumn(col.ColumnName));
}
catch (Exception)
{
}
}
foreach (DataRow row in dFill.Rows)
{
string[] rowarr = new string[dFill.Columns.Count];
for (int k = 0; k < dFill.Columns.Count; k++)
{
rowarr[k] = row[k].ToString();
}
newDataTable.Rows.Add(rowarr);
}
DataRow NewRow;
NewRow = newDataTable.NewRow();
newDataTable.Rows.InsertAt(NewRow, 0);
// Method to alternate row color
GetColors();
bindingSource1.DataSource = newDataTable;
dataGridView1.DataSource = bindingSource1;
//Method to get combobox that holds new headers
GetColumnHeaders();
for (int i = 0; i < dataGridView1.Columns.Count; i++)
{
dataGridView1.Columns[i].SortMode = DataGridViewColumnSortMode.NotSortable;
dataGridView1.Columns[i].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
}
}
</pre>
This may seem elementary to you and the code may not be all that great but it does work. As I stated before I am new to programming. Usually I just work on the database side but I was given this project to create a prototype. Your help and patience is much appreciated.
Thank you,
Brenton
|
|
|
|
|
Thanks for posting some of your code.
From a quick read through, it seems as if there is some redundant code in there. To help in your future development adventures please try this for me.
Remove or comment out the following lines (commenting is probably better till you confirm it is OK):
DataTable newDataTable = new DataTable();
foreach (DataColumn col in dFill.Columns)
{
try
{
newDataTable.Columns.Add(new DataColumn(col.ColumnName));
}
catch (Exception)
{
}
}
foreach (DataRow row in dFill.Rows)
{
string[] rowarr = new string[dFill.Columns.Count];
for (int k = 0; k < dFill.Columns.Count; k++)
{
rowarr[k] = row[k].ToString();
}
newDataTable.Rows.Add(rowarr);
}
DataRow NewRow;
NewRow = newDataTable.NewRow();
newDataTable.Rows.InsertAt(NewRow, 0);
As I am not sure of the purpose of the last two three lines of code, you might need to keep them, just change newDataTable to dFill
then change this row as indicated:
bindingSource1.DataSource = dFill;
This should give the same results as before. Have a look and let me know, since only you can tell if it is different.
What I then need to know is what you need to happen when when the user double-clicks on dataGridView1 (you are still doing it that way aren't you? Tell me if that has changed.).
For example, something like
"dgv1 has columns for itemName, itemCost and discount (no need to list them all, just enough for me to get an idea what it's about). When dgv1 is double-clicked I want form2 to open and display information about the selected item. Those details come from an Excel spreadsheet/text file/whatever."
cheers
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
modified on Tuesday, May 19, 2009 3:16 PM
|
|
|
|
|
You suggestion worked. All seems to be working the same after testing. Thank you for cleaning up the code.
Once the data has been loaded into the dgv on Form 1, the user clicks on the dgv and changes the field headers. This helps standardize the file so the ETL package doesn't break. Once the new field headers have been selected, the user will then click a button. Form 2 is then activated and data has been loaded into different dgv's. dgv1 will be populated with Documentation Type data. Dgv2 will be populated with Purpose Type data and so on for dgv3, dgv4... So what it comes down to is that each dgv on Form 2 will be populated from a single field from the dgv source on Form 1. Each dgv on Form 2 will have three columns (the data from source, a drop down to standardize the data from source, and the count of each distinct value). Once the data has been standardized on Form 2 then the data in the dgv on Form 1 needs to be updated.
I am open to any suggestions you may have if there is an easier way to do this. The requirements however still recommend I use a second form to provide translations of data.
|
|
|
|
|
The second Form method seems to be as good as any other, from what you have told me. So on that basis.
What happens when the user clicks the button, depends on whether there will only ever be one instance of Form2 where the data displayed will change depending on whatever, or whether there will be more than one instance, each one created specifically to display one set of data.
If there will only be one Form2, in Form1 add
private Form2 docsForm = null;
private Form2 DocsForm
{
get
{
if (this.docsForm == null)
{
this.docsForm = new Form2();
}
return docsForm;
}
}
then whenever Form1 needs to refer to the Form2 instance just use DocsForm (the property name) NOT docsForm (the field name). This property uses what is known as 'Lazy Initialization', you can look it up later. It just means that the thing (Form2 in this case) only gets created when it needs to be. The rest of the time the existing instance is used. I have made it private deliberately.
If there will be many instances of Form2 they will be created inside the click handler for the button, so the above will not be needed.
The button click handler, just for illustration purposes I have called it btnShowDocs
private void btnShowDocs_Click(object sender, EventArgs e)
{
Form2 DocsForm = new Form2();
DocsForm.DataKey = this.dataGridView1.CurrentRow.Cells[0].Value.ToString();
DocsForm.Show();
}
There has to be some piece of data in Form1.dgv1, that will enable Form2 to decide the correct data to show, purely for illustration I have said that that data is the value of the first column in the selected row of dgv1.
But you will have to let me know what that piece of data is. It could be the value of a cell in dgv as above, or the whole row, each cell in the row determining the data for one of the dgvs of Form2. If the trigger data is not in dgv1, then what is it?
Then for Form2, something like:
public partial class Form2 : Form
{
private bool dataDisplayed = false;
public Form2()
{
InitializeComponent();
}
private void Form2_Load(object sender, EventArgs e)
{
this.LoadData();
}
private void LoadData()
{
this.dataDisplayed = true;
}
private string dataKey = string.Empty;
public string DataKey
{
get
{
return this.dataKey;
}
set
{
if (this.dataKey != value)
{
this.dataKey = value;
if (this.dataDisplayed)
{
this.LoadData();
}
}
}
}
}
So to help further I need to know what on Form1 enables Form2 to decide what to show. So that I can help you to decide how to pass it to Form2.
The way that you are currently doing it Form1 loads Form2 (via button-click) Form2 then goes to Form1 to get data to load, is going round in circles, never a good idea. Sooner or later it will jump up and bite you in a nasty place.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
Yes, there will only be one instance of Form 2. I have dragged and dropped a TabControl onto Form 2. Each tab contains a dgv which will be populated with a column from the dgv on Form 1.
I have created a method in Form 1 that goes through each cell in row 1 and stores the new headers in an array. if it meets the criteria then it should load the Distinct values of that column into Form 2. Here is the code I have written but don't know if you can use it as it is. But maybe you can...
<pre>public void button12_Click(object sender, EventArgs e)
{
//MappingsForm is the name of Form 2
MappingsForm.Show();
string[] HeaderArray = new string[dataGridView1.ColumnCount];
int n = dataGridView1.ColumnCount;
int c = 0;
for (c = 0; c < n; c++)
{
HeaderArray[c] = dataGridView1.Rows[0].Cells[c].Value.ToString();
if(HeaderArray[c] == "DocType")
{
//pass data to dgv1 on Form2
}
if(HeaderArray[c] == "PurposeType")
{
//pass data to dgv2 on Form2
}
}
}</pre>
This is the last piece to the app. What are your concerns with the way the forms are passing the information? I would really like to hear about them.
|
|
|
|
|
I have modified my suggestions from the previous post in light of this information, using your member names where I noticed them and some of your code, although I have suggested moving it to MappingsForm rather than having it in Form1. The reason is that in OOP programming, as far as is possible, each object (and a Form is an object) should be responsible for handling its own data and controlling access to that data.
For Form1:
private MappingsForm mappingsForm = null;
private MappingsForm MappingsForm
{
get
{
if (this.mappingsForm == null)
{
this.mappingsForm = new MappingsForm();
}
return mappingsForm;
}
}
private void btnShowDocs_Click(object sender, EventArgs e)
{
MappingsForm.DataKey = this.dataGridView1.Rows[0];
MappingsForm.Show();
}
private void Form1_FormClosing(object sender, FormClosingEventArgs e)
{
if (this.mappingsForm != null)
{
this.mappingsForm.Dispose();
this.mappingsForm = null;
}
}
For MappingsForm:
public partial class MappingsForm : Form
{
private bool dataDisplayed = false;
public MappingsForm()
{
InitializeComponent();
}
private void MappingsForm_Load(object sender, EventArgs e)
{
if (this.dataKey != null)
{
this.LoadData();
}
}
private void LoadData()
{
for (int c = 0; c < this.dataKey.Columns.Count; c++)
{
if(this.dataKey.Cells[c].Value.ToString() == "DocType")
{
}
else if (this.dataKey.Cells[c].Value.ToString() == "PurposeType")
{
}
else if (this.dataKey.Cells[c].Value.ToString() == "SomeOtherType")
{
}
switch (this.dataKey.Cells[c].Value.ToString())
{
case "DocType":
break;
case "PurposeType":
break;
case "SomeOtherType":
break;
}
}
this.dataDisplayed = true;
}
private DataGridViewRow dataKey = null;
public DataGridViewRow DataKey
{
get
{
return this.dataKey;
}
set
{
if (this.dataKey != value)
{
this.dataKey = value;
if (this.dataDisplayed)
{
this.LoadData();
}
}
}
}
}
When the data in the dgvs on MappingsForm changes Form1 should be notified by it subscribing to events raised by MappingsForm.
NOT by MappingsForm calling methods on Form1 directly.
The reasons for my concerns are that the current methodology breaks the 'rules' of OOP programming, most notably 'Data Encapsulation' and 'Data Hiding' the explanations of these are too long to go into in a forum like this, but Google them for better explanations than I can give here.
Basically it's like building a Bank with 87 doors v building one with one door. The first is much harder to keep safe.
BTW I noticed that on your Form1 code the button click handler was button12_Click, months down the line you will look at the code and say to yourself, "Now what on earth did button12 do, oh I see (if your lucky), I thought that button8 did that!" Try wherever possible to use descriptive names for your controls, it makes things much easier for maintenance.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
Thanks Henry for the short run down of your concerns. I will look into this... I agree with you about renaming the controls as descriptive as possible. As soon as I get this last part I will go back through and rename them.
I implemented the code you wrote and I get the error 'System.Windows.Forms.DataGridViewRow' does not contain a definition for 'Columns'. This occurs at 'this.dataKey.Columns.Count' in the for loop on Form 2. I have tried looking this up and I get similar hits but what I have tried doesn't work. Any ideas?
|
|
|
|
|
Oops. 
I'm having a root round. I'll get back to you.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
Try changing Columns for Cells. It should work exactly the same. Only The DataGridView has Columns, each of it's rows contains a cell for each of the columns.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
Hi Henry,
I have tried to pass dataKey.Cells[c].Value a couple of ways but I am not having any luck. Here are just three ways I have tried that make sense to me but aren't working:
dataGridView1.DataSource = dataKey.Cells[c].Value;
dataGridView1.Rows.Add(dataKey.Cells[c].Value);
dataGridView1.SelectedRows[c].Cells[c].Value = dataKey.Cells[c].Value;
I have also tried adding ToString() to the end of Value and that isn't working either. I have spent a good amount of time doing research on how to pass the values of the cells to the dgv and I haven't found anything that would work. Do you have a little more time to help me out on this?
Thank you,
Brenton
|
|
|
|
|
Hi Brenton,
None of the options that you have tried will work.
I'll try to explain.
As we left it, MappingForm has a DataViewRow containing a series of cells, each cell holding something like "thisType" or "thatType". To populate each of your DataGridViews with relevant information you need to get it from a file, in the same way that you did for the dgv on Form1. Only you know which file contains data for "thisType" and which file holds data for "thatType".
The if (dataKey.cells[c].Value.ToString == "thisType") lines (or the switch block, if that is what you used), simply enables you differentiate between the various 'Types' so that you know which to use the in the SELECT statement when retrieving the data into a DataTable/DataSet or whatever you decide to use as the datasource for your dgvs.
Something like
SELECT * FROM TheThisTypeFile.csv
WHERE DataType == "thisType"
Obviously the WHERE clause is only required if that file holds data for various 'Types'.
Hope that helps.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
I am still confused because I would still have to know what the field names are called in the file. For instance, I have 2 files. One file may have 32 fields and the other 10 fields which come in different days (say one today and one tomorrow). In these columns we are looking for Loan Purpose Type and Loan Documentation type. In file one these fields are named Doc Type and Purp Type and in the second file they are named Documentation Type and Purpose Type. Also, in the where clause you would need to specify all the doc types. However, each client has there own doc types. One client may have say Full Doc, Limited Doc, and No Doc. Another client may have 1, 2, and 3. so it seems the query would have to change every time. Is this correct?
|
|
|
|
|
Ah-ha!
I am beginning to understand a little more about how this is supposed to work.
If I am right, probably not, but here goes anyway, the purpose of the dgv in Form1 is to allow for translation/modification of what is in the files from client, to a 'standard' wording.
If I have got that right, you have been doing things backwards!  Sorry, I shouldn't laugh, but it is just the sort of thing I do all the time.
I will wait for your response before going further.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
That is correct, with one exception. The only thing I want to accomplish in Form 1 is changing the field headers to a standardized naming convention. In Form 2 I want to standardize the data (but this only needs to be done to certain fields like Doc types). I will explain more to you so we are both on the same page. So we receive files on a daily basis from many different clients. These files contain different information in different columns with different field headers. We use SSIS to get them into our systems however, SSIS breaks every time we run the files through because of the differentiation between field headers and data. So, to get around this I am creating a prototype to standardize all the files which mainly include field headers and data related to some of the fields (i.e. Doc Type, Purpose Type, and so on). So For example, if the field header Doc Type comes in then in form 1 row 1 of the dgv there is a combo dropdown where the user can select the "New" header name. Then the second form needs to include the distinct data for the specific field headers so I can standardize them as well. This second form will activate upon clicking a button. Example:
File 1:
Loan Documentation Type(field name from file) = DocType(new header that will be selected in row 1 of the dgv on Form1)
Low Doc, Full, and No Doc(Doc Types data from file) = L, F, ND(translate doc types to standardized naming convention in dgv on Form 2)
File 2:
Docs(field name from file) = DocType(new header that will be selected in row 1 of the dgv on Form1)
1, 2, and 3 (data from file) = L, F, ND(translate doc types to standardized naming convention in dgv on Form 2)
So the files we get have 100's of loans. For this example, the field Doc Types can have many types (i.e. 50 Full docs, 25 Low Docs, and 25 No docs) which is why I would like to select Distinct values only having to map, in this case, 3 doc types rather then 100.
I am sure I have not explained myself very well in previous posts so please let me know if you need additional information or if you are confused on anything I have said to this point.
|
|
|
|
|
Going back to your previous post, you have said that for documentation type a client might have Full Doc, Limited Doc and No Doc; whilst another might have 1, 2 and 3.
Are the options finite? i.e. are there always 3 possibilities for documentation type, and is that the same for all fields? (not necessarily 3, it could be 10, 7 or whatever) The point is are all options (or at least the number of possible options) known to yourselves prior to recieving the file(s). Also will the same client always use the same headings.
On to this post.
I am not 100% on the reason for the multiple dgvs on the mapping form.
In Mapping form, is the purpose of the different dgvs to collate, based on documentation type (dgv1 = full doc, dgv2 = limited doc etc), or to summarise (dgv1 = doctype, with a row for count of full doc, another for count of limited doc etc) with the other dgvs for the other field types.
Is the final objective to just display the data, to stream it to another application or to write it back out to a file and if so what type of file (.csv, mdf, txt)
The point I made previously (about doing it backwards) is that to do anything with the clients data, whether selecting on a field type, collating on a field type, or whatever, you have at that stage to use the clients wording/coding scheme.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
No, there could be up to 30+ different doc types. I just used three for this example. Same goes for the other fields we need to map data for. Not all possible data is known until we receive the data and the same client doesn't always give us the same headings or file formats. nothing is considered finite between the data or the headers.
The reason for the multiple dgv's is to have a dgv per field where the data needs to be mapped. So, dgv1 is for Doc Types, dgv2 is for Purpose Types, dgv3 is for Occupancy Types and so on. So the second part of the third paragraph you wrote is correct "or to summarize (dgv1 = doctype, with a row for count of full doc, another for count of limited doc etc) with the other dgvs for the other field types". So the all the dgv's have three columns. The first will be the column that gets populated with the data from the file or the dgv on Form 1(example Full Documentation). The second column is a combo dropdown that contains the new code (F for Full Documentation). The third is a count of how many loans have Full Documentation. After all Doc Types have been recoded, then dgv on Form 1 needs to be updated. For example, Full Documentation is will be updated to F.
Once the dgv on Form 1 has been updated with the new codes from form 2, I will output it to excel for our SSIS (this is done and has been tested).
|
|
|
|
|
Hi there.
I have been thinking about this and have made a simple demo of something that I think might work. If you have the time would you give it a quick whirl to see if it might suit.
The demo consists of two forms each having a datagridview and a button, you will be able to work out the names of these controls from the code I will post. I will post the code for the two forms in separate posts, as it would otherwise be a very long post.
This is the test data I have used for the demo and if you could save it as TestData.csv in the directory with the executable for the demo, normally 'application-files-directory\bin\debug'
TestData.csv
Prop, Offer, Docs, Gtee, Figs
house, no, full, none, true
flat, true, part, complete, no
appt, no, no, yes, yes
bungalow, yes, full, no, no
mais, false, yes, no, comp
The code for the first form follows shortly.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
public partial class ListEditorForm : Form
{
List<string> standardList = new List<string>();
Dictionary<string, string> headers = new Dictionary<string, string>();
DataSet headerDataSet = null;
DataTable headerTable = null;
MappingForm mappingForm = null;
readonly string dataFile = "TestData.csv";
FieldMappingDictionary docsDictionary = null;
public ListEditorForm()
{
InitializeComponent();
InitStandardList();
}
private void InitStandardList()
{
standardList.AddRange(new string[] { "Property", "Offer", "Documentation", "Figures", "Guarantee" });
}
private void ProcessMods()
{
StringBuilder selectBuilder = null;
foreach (string key in this.docsDictionary.Keys)
{
foreach (string val in this.docsDictionary[key])
{
selectBuilder = new StringBuilder();
selectBuilder.Append("UPDATE <yourtablenamehere**> SET Documentation = ");
selectBuilder.Append(key);
selectBuilder.Append(" WHERE Documentation = ");
selectBuilder.Append(val);
string updateQuery = selectBuilder.ToString();
}
}
}
private Dictionary<string, string> MakeHeaderDictionary()
{
Dictionary<string, string> result = new Dictionary<string, string>();
foreach (DataRow row in this.headerTable.Rows)
{
result.Add(row[1].ToString(), row[0].ToString());
}
return result;
}
#region ListEditorForm PROPERTIES ...............................
public Dictionary<string, string> Headers
{
get
{
return this.headers;
}
private set
{
this.headers = value;
}
}
private MappingForm MappingForm
{
get
{
if (this.mappingForm == null)
{
this.mappingForm = new MappingForm();
}
return this.mappingForm;
}
}
#endregion ListEditorForm PROPERTIES
private void ListEditorForm_Load(object sender, EventArgs e)
{
string headerLine;
using (StreamReader sr = new StreamReader(dataFile))
{
headerLine = sr.ReadLine();
}
headerDataSet = new DataSet("HeaderDataSet");
headerTable = new DataTable("ClientHeaders");
DataColumn newColumn = new DataColumn("ClientHeader", typeof(string));
headerTable.Columns.Add(newColumn);
newColumn = new DataColumn("StandardHeader", typeof(string));
headerTable.Columns.Add(newColumn);
DataRow newRow = null;
foreach (string s in headerLine.Split(','))
{
newRow = headerTable.NewRow();
newRow[0] = s;
newRow[1] = "Property";
headerTable.Rows.Add(newRow);
}
headerDataSet.Tables.Add(headerTable);
DataGridViewComboBoxColumn cbCol =
this.dataGridView1.Columns["standardColumn"] as DataGridViewComboBoxColumn;
cbCol.DataSource = this.standardList;
this.dataGridView1.AutoGenerateColumns = false;
this.dataGridView1.Columns[0].DataPropertyName = "ClientHeader";
this.dataGridView1.Columns[1].DataPropertyName = "StandardHeader";
this.dataGridView1.DataSource = headerDataSet;
this.dataGridView1.DataMember = "ClientHeaders";
}
private void btnGetValue_Click(object sender, EventArgs e)
{
this.headers = this.MakeHeaderDictionary();
this.MappingForm.HeaderDictionary = this.headers;
this.MappingForm.DataFile = this.dataFile;
this.MappingForm.FormClosing += new FormClosingEventHandler(MappingForm_Closed);
this.MappingForm.Show();
}
void MappingForm_Closed(object sender, FormClosingEventArgs e)
{
this.docsDictionary = this.MappingForm.DocumentsDictionary;
this.ProcessMods();
}
}
Code for second form follows
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
