Adding an extra column to a datagrid view

Hello,

I am trying to display a register of data in a datagridview. Like so:



    Private Sub RetrieveMortgages()

        Dim dbSourceNew = ConfigurationManager.AppSettings("dbSource")
        Dim objConnection As New SqlConnection("Persist Security Info=False;Initial Catalog=" & _
        "myo;Data Source=" + dbSourceNew + ";User Id=***;Password=***")
        Dim mortAdapter As New SqlDataAdapter("(SELECT mort_date_written,mort_type,mort_repay_method," & _
        "mort_deal,mort_company,mort_no,mort_amount,mort_comm,mort_settled,mort_actual_comm," & _
        "mort_comm_recd,mort_comm_paid,mort_introducer,mort_completed FROM mortgage WHERE " & _
        "mort_application_date IS NOT NULL) UNION " & _
        "(SELECT mort_date_written,mort_type,mort_repay_method,mort_deal,mort_company,mort_no," & _
        "mort_amount,mort_comm,mort_settled,mort_actual_comm,mort_comm_recd,mort_comm_paid," & _
        "mort_introducer,mort_completed FROM remortgage WHERE mort_application_date IS NOT NULL) " & _
        "ORDER BY mort_application_date DESC", objConnection)
        Dim mortDT As New DataTable("mort")

        mortAdapter.Fill(mortDT)
        grdMortgageRegister.DataSource = mortDT

    End Sub


I want to insert a column between column(0) - Date Written   and
column (1) - Mortgage Type

which will be Name.

How can i adjust my code below to get each rows: mort_client_id
and then get client_name and client_surname from the client table?

Cheers
' Initialize the button column.
        Dim nameColumn As New DataGridViewTextBoxColumn
 
        With nameColumn
            .HeaderText = "Name"
            .Name = "Name"
            .Text = "View Details"
        End With
 
        ' Add the button column to the control.
        grdMortgageRegister.Columns.Insert(1, nameColumn)

Open in new window

drews1fAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dirk HaestProject managerCommented:
Does the client_name and client_surname also exist in the table mortgage ?
If so, you can easily change your query to add the column and display it in your gird

       Dim mortAdapter As New SqlDataAdapter("(SELECT mort_date_written, client_name & ' ' & Client_surname as ClientName, mort_type,mort_repay_method," & _
        "mort_deal,mort_company,mort_no,mort_amount,mort_comm,mort_settled,mort_actual_comm," & _
        "mort_comm_recd,mort_comm_paid,mort_introducer,mort_completed FROM mortgage WHERE " & _
        "mort_application_date IS NOT NULL) UNION " & _
        "(SELECT mort_date_written, client_name & ' ' & Client_surname as ClientName, mort_type,mort_repay_method,mort_deal,mort_company,mort_no," & _
        "mort_amount,mort_comm,mort_settled,mort_actual_comm,mort_comm_recd,mort_comm_paid," & _
        "mort_introducer,mort_completed FROM remortgage WHERE mort_application_date IS NOT NULL) " & _
        "ORDER BY mort_application_date DESC", objConnection)
0
CodeCruiserCommented:
If the client name and client surname was in the same table then i dont think it was a question to be asked here.
Is there a link between the mortgage table and the client table? A primary and foreign key relationship?
0
drews1fAuthor Commented:
i created a foreign key between

mortgage                                  client

mort_client_id     ----->             client_id

And no client_name and client_surname dont exist in the mortgage table!

0
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

Dirk HaestProject managerCommented:
You can create a query with both tables !

SELECT mort_date_written, client_name & ' ' & client_surname as clientname, mort_type,mort_repay_method,mort_deal,mort_company,mort_no,mort_amount,mort_comm,mort_settled,mort_actual_comm,   mort_comm_recd, mort_comm_paid, mort_introducer, mort_completed
FROM mortgage LEFT JOIN Client on mort_clientID = client_ID
WHERE mort_application_date IS NOT NULL
0
drews1fAuthor Commented:
OK that is exactly what i need!
The only problem is sometimes mortgages are for 2 people in which case there is two clients surnames and forenames i need to get.

mortgage                                  client

mort_client_id1      ------>         client_id
mort_client_id2      ------>         client_id

is there anyway to do this or is it impossible? :(
0
Dirk HaestProject managerCommented:
Do you want to get it in one row or in 2 rows (so a row for each person) ?
0
CodeCruiserCommented:
I think you would be better of using properly normalised tables so that if you have 3 clients in a mortgage then you could deal with that. To do that, you would need to create a third table. For example

Client:
ClientID, Forename, Surname...

Mortgage:
MortgageID, Date, Amount...

ClientMortgage:
ClientID, MortgageID...

0
drews1fAuthor Commented:
in one cell which is multiline preferably
0
Dirk HaestProject managerCommented:
Like CodeCruiser said: I would reconsider your table-design !
0
drews1fAuthor Commented:
how would i do this using the table design codecruiser suggested?
0
CodeCruiserCommented:
That would become a parent child relationship so you would have to deal it differently. You would load the details of a mortgage and then list all the clients. Or you could load the details of a client and list all the mortgages he has.
0
drews1fAuthor Commented:
im trying to create a list of all the mortgages ever sold though.

Date Written  |  CLIENT NAME(S)  |  Type  |  Amount  |  Deal  |  Settled

so each client name that is involved in that mortgages has to appear in the one cell!
0
CodeCruiserCommented:
The problem is, if you run a query and join these two tables, there will be two rows returned where there are two clients.
0
drews1fAuthor Commented:
what about something like this: ie two left joins. can you do that?
i tried this but it returned nothing in the datagridview




        Dim mortAdapter As New SqlDataAdapter("(SELECT mort_date_written,mort_type,mort_repay_method," & _
        "mort_deal,mort_company,mort_no,mort_amount,mort_comm,mort_settled,mort_actual_comm," & _
        "mort_comm_recd,mort_comm_paid,mort_introducer,mort_completed,mort_application_date,mort_client_id1,mort_client_id2 FROM mortgage WHERE " & _
        "mort_application_date IS NOT NULL) UNION " & _
        "(SELECT mort_date_written,mort_type,mort_repay_method,mort_deal,mort_company,mort_no," & _
        "mort_amount,mort_comm,mort_settled,mort_actual_comm,mort_comm_recd,mort_comm_paid," & _
        "mort_introducer,mort_completed,mort_application_date,mort_client_id1,mort_client_id2 FROM remortgage WHERE mort_application_date " & _
        "IS NOT NULL) LEFT JOIN (SELECT client_name & ' ' & client_surname as clientname" & _
        " FROM client WHERE client_id = mort_client_id1)" & _
        " LEFT JOIN (SELECT client_name & ' ' & client_surname as partnername" & _
        " FROM client WHERE client_id = mort_client_id2)" & _
        " ORDER BY mort_application_date DESC", objConnection)

Open in new window

0
drews1fAuthor Commented:
OK this is giving the desired result - NEARLY

The code produces datamembers for c1name and c2name and when i set the column datamember to each it shows the correct names.

So how can i make a new datamember which shows:

c1name c1surname
--
or
--
c1name c1surname
c2name c2surname

?
        Dim mortAdapter As New SqlDataAdapter("(SELECT mort_id,mort_date_written,mort_type,mort_repay_method," & _
        "mort_deal,mort_company,mort_no,mort_amount,mort_comm,mort_settled,mort_actual_comm," & _
        "mort_comm_recd,mort_comm_paid,mort_introducer,mort_completed,mort_application_date,mort_client_id1," & _
        "mort_client_id2, c1.client_name as c1name, c1.client_surname as c1surname, " & _
        "c2.client_name as c2name " & _
        "FROM remortgage " & _
        "LEFT JOIN client c1 ON c1.client_id = mort_client_id1 " & _
        "LEFT JOIN client c2 ON c2.client_id = mort_client_id2 " & _
        "WHERE mort_application_date IS NOT NULL) UNION " & _
        "(SELECT mort_id,mort_date_written,mort_type,mort_repay_method," & _
        "mort_deal,mort_company,mort_no,mort_amount,mort_comm,mort_settled,mort_actual_comm," & _
        "mort_comm_recd,mort_comm_paid,mort_introducer,mort_completed,mort_application_date,mort_client_id1," & _
        "mort_client_id2, c1.client_name as c1name, c1.client_surname as c1surname, " & _
        "c2.client_name as c2name " & _
        "FROM remortgage " & _
        "LEFT JOIN client c1 ON c1.client_id = mort_client_id1 " & _
        "LEFT JOIN client c2 ON c2.client_id = mort_client_id2 " & _
        "WHERE mort_application_date IS NOT NULL)" & _
        " ORDER BY mort_application_date DESC", objConnection)

Open in new window

0
drews1fAuthor Commented:
maybe i can change something in the designer to get the desired result?

if i try to add something other than c1name i get no results in the grid :(
        '
        'cName
        '
        Me.cName.DataPropertyName = "c1name"
        Me.cName.HeaderText = "Client(s) Name"
        Me.cName.Name = "cName"
        Me.cName.ReadOnly = True

Open in new window

0
drews1fAuthor Commented:
I fixed this myself. The code is desired was as follows:


        Dim mortAdapter As New SqlDataAdapter("" & _
        "(SELECT mort_id,mort_date_written,mort_type,mort_repay_method,mort_deal,mort_company,mort_no," & _
        "mort_amount,mort_comm,mort_settled,mort_actual_comm,mort_comm_recd,mort_comm_paid,mort_introducer," & _
        "mort_completed,mort_application_date,mort_client_id1,mort_client_id2, c1.client_name as c1name, " & _
        "c1.client_surname as c1surname,c2.client_name as c2name,c2.client_surname as c2surname FROM mortgage " & _
        "LEFT JOIN client c1 ON c1.client_id = mort_client_id1 " & _
        "LEFT JOIN client c2 ON c2.client_id = mort_client_id2 WHERE mort_application_date IS NOT NULL) " & _
        "UNION " & _
        "(SELECT mort_id,mort_date_written,mort_type,mort_repay_method,mort_deal,mort_company,mort_no," & _
        "mort_amount,mort_comm,mort_settled,mort_actual_comm,mort_comm_recd,mort_comm_paid,mort_introducer," & _
        "mort_completed,mort_application_date,mort_client_id1,mort_client_id2, c1.client_name as c1name," & _
        "c1.client_surname as c1surname,c2.client_name as c2name,c2.client_surname as c2surname FROM remortgage " & _
        "LEFT JOIN client c1 ON c1.client_id = mort_client_id1 " & _
        "LEFT JOIN client c2 ON c2.client_id = mort_client_id2 WHERE mort_application_date IS NOT NULL)" & _
        "", objConnection)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.