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?
 
drews1fConnect With a Mentor Author 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
 
DhaestCommented:
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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
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
 
DhaestCommented:
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
 
DhaestCommented:
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
 
DhaestCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.