Link to home
Start Free TrialLog in
Avatar of drews1f
drews1f

asked on

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

Avatar of Dirk Haest
Dirk Haest
Flag of Belgium image
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)
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image
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?
Avatar of drews1f
drews1f

ASKER

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!

Avatar of Dirk Haest
Dirk Haest
Flag of Belgium image
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
Avatar of drews1f
drews1f

ASKER

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? :(
Avatar of Dirk Haest
Dirk Haest
Flag of Belgium image
Do you want to get it in one row or in 2 rows (so a row for each person) ?
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image
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...

Avatar of drews1f
drews1f

ASKER

in one cell which is multiline preferably
Avatar of Dirk Haest
Dirk Haest
Flag of Belgium image
Like CodeCruiser said: I would reconsider your table-design !
Avatar of drews1f
drews1f

ASKER

how would i do this using the table design codecruiser suggested?
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image
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.
Avatar of drews1f
drews1f

ASKER

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!
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image
The problem is, if you run a query and join these two tables, there will be two rows returned where there are two clients.
Avatar of drews1f
drews1f

ASKER

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

Avatar of drews1f
drews1f

ASKER

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

Avatar of drews1f
drews1f

ASKER

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

ASKER CERTIFIED SOLUTION
Avatar of drews1f
drews1f
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial