[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Adding an extra column to a datagrid view

Posted on 2008-11-04
17
Medium Priority
?
572 Views
Last Modified: 2009-01-18
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

0
Comment
Question by:drews1f
  • 9
  • 4
  • 4
17 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 22883583
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 22884233
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
 

Author Comment

by:drews1f
ID: 22884722
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 53

Expert Comment

by:Dhaest
ID: 22884739
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
 

Author Comment

by:drews1f
ID: 22884810
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
 
LVL 53

Expert Comment

by:Dhaest
ID: 22884823
Do you want to get it in one row or in 2 rows (so a row for each person) ?
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 22884874
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
 

Author Comment

by:drews1f
ID: 22884933
in one cell which is multiline preferably
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 22884971
Like CodeCruiser said: I would reconsider your table-design !
0
 

Author Comment

by:drews1f
ID: 22885070
how would i do this using the table design codecruiser suggested?
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 22885166
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
 

Author Comment

by:drews1f
ID: 22885314
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 22885370
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
 

Author Comment

by:drews1f
ID: 22885470
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
 

Author Comment

by:drews1f
ID: 22886014
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
 

Author Comment

by:drews1f
ID: 22886526
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
 

Accepted Solution

by:
drews1f earned 0 total points
ID: 23361586
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month19 days, 21 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question