Help with Linking ID's to show in Listview below

[sundog1]

Hi guys, I hope someone can help. I have a list view being populated by a Table (cashCustomers) in an Access DB which has customers Information.

• CashAccRef
• CashName
• CashAddress1
• CashAddress2
• CashTown
• CashAddress3
• CashAddress4
• CashAddress5

and the following Orders table (cashOrders)

• ID
• QTY
• Description
• Supplier
• Date
• Cost
• Sell
• cashAccountRef_FKID

I am basically try set it so when 'listview1_MouseClick' is activated by selecting an account in the 1st list view, the second listview 'listview2' populates with the correct order lines per customer.

Currently I believe I have to use a INNER JOIN Querey which I had done (Code Below):

 cashOrders = new DataSet();
            cashOrderTable = new DataTable();
            cashOrders.Tables.Add(cashOrderTable);

            string sql2 = "SELECT ID,QTY,Description,Supplier,Date,Cost,Sell from cashOrders INNER JOIN cashCustomers ON cashOrders.cashAccountRef_FKID=cashCustomers.CashAccRef;";
            cashDA = new System.Data.OleDb.OleDbDataAdapter(sql2, cashCustom);
            cashDA.Fill(cashOrderTable);

            foreach (DataRow myRow2 in cashOrderTable.Rows)
            {
                listView2.Items.Add(myRow2[0].ToString());
                listView2.Items[listView2.Items.Count - 1].SubItems.Add(myRow2[1].ToString());
                listView2.Items[listView2.Items.Count - 1].SubItems.Add(myRow2[2].ToString());
                listView2.Items[listView2.Items.Count - 1].SubItems.Add(myRow2[3].ToString());
                listView2.Items[listView2.Items.Count - 1].SubItems.Add(myRow2[4].ToString());
                listView2.Items[listView2.Items.Count - 1].SubItems.Add(myRow2[5].ToString());
                listView2.Items[listView2.Items.Count - 1].SubItems.Add(myRow2[6].ToString()); 
            }

However, I can guess it need to know the selection I made in 'Listview1'. How do I go about amending the String to pickup the selection in 'listview1'?

also currently when running this I do get an error on the cashDA.fill(cashOrderTable stating 'Type Mismatch in expression'.

Thanks for any help in advance.

Mark.

[Mike Askew]

Hi Mark,

You would need to set up the first list so that the .SelectedItem in list box (What you can physically see) is as you state above, but the .SelectedValue contains the actual ID value.

Then the SQL can adjust too:
SELECT ID,QTY,Description,Supplier,Date,Cost,Sell from cashOrders INNER JOIN cashCustomers ON cashOrders.cashAccountRef_FKID=cashCustomers.CashAccRef WHERE cashCustomers.CashAccRef = " + listview1.SelectedValue;

Havent tested this so the SQL might not work but thats the jist of it. It basically selects all the required information from the cashOrders table based on the Id linked to the cashCustomers table where we compare that to the passed ID value from the .SelectedValue of the listbox1.

In terms of binding the listbox to get the information working correctly, if I remember rightly, it is done by binding the listbox to the datasource and then specifying which columns are the .DisplayMember (is visually seen) and .DataSource.

That probably isnt explained clearly as its been about a year since I've done such xD

This might help explain it also.

[sundog1]

Hi Mikey,

Many thanks for your reply on this.

I'm struggling a little bit with setting up the first list where it collects all the particular information including the ID.

and DataBinding is still a learning curve to me with listviews as I'm only really starting out in programming.

So any advice you could give me would be great!

Regards
Mark.

[Mike Askew]

I will try to get back to you this afternoon, work is busy.

Else I'll write up an example tomorrow :)

Sorry for the inconvienience.

[sundog1]

Hi Mikey,

Many thanks for your response to help me on that. That would be great! I would love to get dug in and play about with this.

I look forward to any help you can give a newbie!

[Mike Askew]

Ok sorry for this not being actual code but its quicker for me to write to the code in a descriptive process and answer questions than mess around with setting up fake datasets and then try to SQL them (been trying for the last 20 mins and failing).

To bind a listbox to a data source.

Dataset: DS

For example we have a table called CustomerTable with two rows CustomerID and CustomerName, will assume we have already run the SQL to select this information and it is in the dataset DS

And a second called OrderTable with rows OrderID, CustomerIDFK and ItemID which we will be running SQL off.

Our listboxes are List1 and List2.

To setup our first list with the customer names and their ID's as the selected value:

List1.DataSource = DS.Tables["CustomerTable"];
List1.DisplayMember = "CustomerName"; //The column name as a string
List1.ValueMember = "CustomerID";

So now in the SelectedIndexChanged action of the listbox List1 we will run an SQL query to the second table.

string SQLToRun = string.Format("Select * From OrderTable Where CustomerIDFK = {0}", List1.SelectedValue); //The string.Format allows use of {0} and then specifying the value after the end of the string, makes it a bit tidier than cutting off the string at '=' and then concat'ing the .SelectedValue in

This would return all the rows matching the CustomerID selected in List1 (it probably wouldnt as im rusty with SQL but oh well!)

And then following the same steps as with List1 we could bind the returned table from the dataset used to query showing the information we like from the second table.

Which coming to look at the imaginery second table I described, is absolutely nothing useful as its all foreign keys for other tables... DOH!

Hope that helps a bit and give me a shout if stuck on anything.

With work how it is at the moment (Development in progress and pressured to deliver), I thought best to reply this way first and let you learn through practical experience.

[sundog1]

Hi Mikey,

Many thanks for taking the time to write this out. The only issue is i'm using Listview and not ListBoxes.

ListView gives me differnet options and I cannot use 'Datasource'.

[Mike Askew]

Well thats the best blunder I've pulled off on this site... Skimread the question and never re-read it!

Gimme a while and I'll try figure it out :)

[sundog1]

Ha-ha.. no probs Mikey,

Thank you for your help on this :)

[Mike Askew]

Ok so I achieved a similar effect using the ListView's tag property.

This is how I added some fake data, its an adaption to your code shown in post #1

edit: by which I mean the for-each loop, the table structure is identical to my above post, I continued using all that code to do this :)

foreach (DataRow row in DS.Tables["NameTable"].Rows)
{
    ListViewItem temp = new ListViewItem();
    temp.Text = row.ItemArray[1].ToString();
    temp.Tag = row.ItemArray[0].ToString();
    listView1.Items.Add(temp);
}

This hides the ID in the tag property of the individual item on the listview.

You can then adapt my code in the ListBox post to instead look at the tag property of the selected item to view the ID and carry out the SQL.

Used this line to check the tag worked etc on ListView selected index changed

foreach (ListViewItem item in listView1.SelectedItems)
{
    MessageBox.Show("Tag: " + item.Tag + " | Text: " + item.Text);
}

Hopefully that might work :)

On a seperate note - your welcome Mark :) Good ol' waiting on email replies giving me time to write this

[sundog1]

Hi Mikey,

Many thanks for your help on this once again.. Below is the current connection to the Access DB and populaton of this list view:-

       private void radioButton3_CheckedChanged(object sender, EventArgs e)
        {
            cashCustom.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/Orders.mdb";
            cashCustomerDS = new DataSet();
            cashcTable = new DataTable();
            cashCustomerDS.Tables.Add(cashcTable);


            cashCustom.Open();
            //cashCustom.Close();

            string sql = "SELECT * FROM cashCustomers";
            cashDA = new System.Data.OleDb.OleDbDataAdapter(sql, cashCustom);
            cashDA.Fill(cashcTable);


            foreach (DataRow myRow in cashcTable.Rows)
            {
                listView1.Items.Add(myRow[0].ToString());
                listView1.Items[listView1.Items.Count - 1].SubItems.Add(myRow[1].ToString());
                listView1.Items[listView1.Items.Count - 1].SubItems.Add(myRow[4].ToString());

                listView1.Items[listView1.Items.Count - 1].SubItems.Add(myRow[2].ToString());
                listView1.Items[listView1.Items.Count - 1].SubItems.Add(myRow[3].ToString());
                listView1.Items[listView1.Items.Count - 1].SubItems.Add(myRow[5].ToString());
                listView1.Items[listView1.Items.Count - 1].SubItems.Add(myRow[6].ToString());

Where abouts would your refer to adding the tag option? Sorry I'm a little lost as if to add another foreach statment or modify the above.

Then I would add the foreach statement to check the ID was pulled in by placeing in into the index code snippet.

[Mike Askew]

Ummm.. listView1.Items[listView1.Items.Count - 1].Tag = x

That line would be added into the current for-each loop.

[sundog1]

Hi Mikey,

Sorry its getting near the end of the day and my brains fried!

would the x in '.Tag =' be something like "customerID";

for Example:

listView1.Items[listView1.Items.Count - 1].Tag = "CustomerID";

(CustomerID refers to the CustomerAccNumber in the Database).

[Mike Askew]

It would be the a reference to the column holding the customerID.

and yeah I know the brain fried feeling..

[sundog1]

Hi Mikey,

As its a new day, fresh start 'n all that.. but I'm still having trouble getting this to work. I'm surely typing the wrong command but Have added them below for you to look at:

           foreach (DataRow myRow in cashcTable.Rows)
            {
                listView1.Items.Add(myRow[0].ToString());
                listView1.Items[listView1.Items.Count - 1].SubItems.Add(myRow[1].ToString());
                listView1.Items[listView1.Items.Count - 1].SubItems.Add(myRow[4].ToString());
                *listView1.Items[listView1.Items.Count - 1].Tag = myRow[0].ToString();*

                listView1.Items[listView1.Items.Count - 1].SubItems.Add(myRow[2].ToString());
                listView1.Items[listView1.Items.Count - 1].SubItems.Add(myRow[3].ToString());
                listView1.Items[listView1.Items.Count - 1].SubItems.Add(myRow[5].ToString());
                listView1.Items[listView1.Items.Count - 1].SubItems.Add(myRow[6].ToString());

            }

and this is my 'Onselect' snippet of code:

       private void listView1_SelectedIndexChanged_1(object sender, EventArgs e)
        {
            foreach (ListViewItem item in listView1.SelectedItems)
            {
                MessageBox.Show("Tag: " + item.Tag + " | Text: " + item.Text);
            }
        }

I'm being fuelled by Coffee at the moment and think I need another..

[Mike Askew]

And what happens when running this? It should just show a message when you select something new stating the tag and name.

[sundog1]

Hi Mikey,

nothing happens. There is no pop-up box or nothing. I thought I might have mis-understood your explanation of the code?

[sundog1]

Hi Mikey,

Ok.. For some reason I had 2

private void listView1_SelectedIndexChanged(object sender, EventArgs e)

I pasted it into the other 1 and it worked fine! I now have the comment box appearing telling me the tag number! Wh00!

Thats another step closer! :)

God knows what I have 2 Private voices exactly the same! :S

[sundog1]

Hi Mikey,

Managed to get the Tag's appearing as you can see from the added Screenshots.

I amended my code to try and fill the other table with information picked up from the Tag ID but I can an error. See other screenshot.

The code I'm currently using is below:

       private void listView1_MouseClick(object sender, MouseEventArgs e)
        {
            tblNames.DataSource = cashCustomerDS.Tables["cashCustomers"];

            if (listView1.SelectedItems.Count > 0)
            {

                txtAccRef.Text = listView1.SelectedItems[0].Text;
                txtName.Text = listView1.SelectedItems[0].SubItems[1].Text;
                txtTown.Text = listView1.SelectedItems[0].SubItems[2].Text;
                txtAddr1.Text = listView1.SelectedItems[0].SubItems[3].Text;
                txtAddr2.Text = listView1.SelectedItems[0].SubItems[4].Text;
                txtCounty.Text = listView1.SelectedItems[0].SubItems[5].Text;
                txtPostCode.Text = listView1.SelectedItems[0].SubItems[6].Text;
         }
            foreach (ListViewItem item in listView1.SelectedItems)
            {
                MessageBox.Show("Tag: " + item.Tag + " | Text: " + item.Text);
            }

            string SQLTORun = string.Format("Select * FROM cashOrders WHERE cashAccountRef_FKID = {0}", listView1.SelectedItems);

            cashDA = new System.Data.OleDb.OleDbDataAdapter(SQLTORun, cashCustom);
            cashDA.Fill(cashOrderTable);

            foreach (DataRow myRow2 in cashOrderTable.Rows)
            {
                listView2.Items.Add(myRow2[0].ToString());
                listView2.Items[listView2.Items.Count - 1].SubItems.Add(myRow2[1].ToString());
                listView2.Items[listView2.Items.Count - 1].SubItems.Add(myRow2[2].ToString());
                listView2.Items[listView2.Items.Count - 1].SubItems.Add(myRow2[3].ToString());
                listView2.Items[listView2.Items.Count - 1].SubItems.Add(myRow2[4].ToString());
                listView2.Items[listView2.Items.Count - 1].SubItems.Add(myRow2[5].ToString());
                listView2.Items[listView2.Items.Count - 1].SubItems.Add(myRow2[6].ToString());
            }
        }

[Mike Askew]

cashOrderTable is not declared as a new datatable prior to use. Im guessing that might be it? Unless that is declared and instanciated elsewhere?

[sundog1]

Hi Mikey,

Yes I beleive it has been declared before use. See Code below:

namespace CardIndex
{
    public partial class Form1 : Form
    {
        private ADODB.Connection adoConn = new ADODB.Connection();
        private ADODB.Recordset adoRS = new ADODB.Recordset();

       //THIS IS THE MDB DATA ADAPTER INFORMATION//
        System.Data.OleDb.OleDbConnection cashCustom = new System.Data.OleDb.OleDbConnection();
        System.Data.OleDb.OleDbDataAdapter cashDA = new System.Data.OleDb.OleDbDataAdapter();
        DataSet cashCustomerDS;
        DataTable cashcTable;
       //-------------------------------------//
        DataSet cashOrders;
        DataTable cashOrderTable;
        BindingSource tblNames = new BindingSource();

[Mike Askew]

Its declared but isnt instanciated (I could be completely wrong here, and it wont make a difference) but try: DataTable cashOrderTable = new DataTable(); on line 15.

[sundog1]

Hi Mikey,

Well I did as was requested and the error has moved onto the follow screenshot attached.

[Mike Askew]

Right im fast running out of ideas at the moment :D

Have you checked the SQL string at run time is correct? Ie. the .tag is correctly being passed into the sql.

[sundog1]

Hi Mike,

Sorry for the late reply to this I have been busy doing other jobs.

I've checked the SQL Querey and I amended it only to run the following:

string SQLTORun = string.Format("Select * FROM cashOrders");

just to check if it works which it does.

So... I must not be reading the other part of the SQL Correctly. I'm not to sure what
this part means.. ={0} ?? what is this trying to reference?

thanks again for your help so far.

[sundog1]

Hi Mikey,

On further investigation to this I can see the 'Type Count' Showing as 4. Which there are 4 Accounts in the table. Please see screeshot attached.

[Mike Askew]

So... I must not be reading the other part of the SQL Correctly. I'm not to sure what
this part means.. ={0} ?? what is this trying to reference?

Right to explain this. The following two lines of code do the same thing.

Console.WriteLine("The cat sat on" + " the " + "mat.");
Console.WriteLine(string.Format("The cat sat on {0} mat.", "the");

Basically the second line allows you to insert the values after the actual text string, so in the above case, the string "the" is added where {0} is. For longer strings with multiple entries it is simply comma-seperated values after the string like so:

Console.WriteLine("The {0} sat on the {1} and the {2} ate it.", "cat", "mat", "dog");

Obviously the strings can be replaced with any sort of variable, in your case the .Tag property.

Basically it keeps strings cleaner that concatenating everything with +'

[sundog1]

Good morning Mike, I hope you had a good weekend?

Thank you for the quick lesson on the {0}. As I am a total newbie it was explained well and I get how to use it now :)

I did some research on how to use the watch window and as I watch each section go through it seems to be populating correctly until I get to the 'cashDA.Fill' section. Please see screenshot attached.

It seems to think that no value has been given to one or more required parameters. but everything gets populated as needs be?

[sundog1]

Hi Mikey,

I've changed tact here and using the 'datagridview' to try and get around the issue so have start the project again. Currently the coding I have is the following:

        private void radioBtnCash_CheckedChanged(object sender, EventArgs e)
        {

            cashCustom.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/Orders.mdb";
            cashCustomersDS = new DataSet();
            cashCustomersTable = new DataTable();
            cashCustomersDS.Tables.Add(cashCustomersTable);

            cashCustom.Open();
            cashCustom.Close();

            String cashCustomerSQL = "SELECT * FROM cashCustomers";
            cashDA = new System.Data.OleDb.OleDbDataAdapter(cashCustomerSQL, cashCustom);
            cashDA.Fill(cashCustomersTable);

            dataGridView1.DataSource = cashCustomersTable;
            dataGridView1.Columns.Remove("CashAddress5");
            dataGridView1.Columns.Remove("CashAddress3");
            dataGridView1.Columns.Remove("CashAddress2");
            dataGridView1.Columns.Remove("CashAddress1");

This pulls through when selecting the radio button. Now I need 'datagridview2' to load the data from cashorders and somehow link them?

       private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
        {

        }

I've been told on many other forums using the 'datagridview' would be a lot easier so if anyone can help point me in the right direction that would be great.

Regards
Mark.

[Mike Askew]

Im pretty sure it will be similar to the ListView approach, using a SQL query with a variable added in passing a primary key of some sort.