none
CellFormatting event to look up a value from another table

    Question

  • Hello,

    I have a bound datagridview in which i need to add an unbound column. The bound datagridview has the following columns from PersonalDataTable:

    Employeed ID,Date, Department, Shift, Start Time, Finish Time,Etc.

    I want to be able to to create an unbound column called "NAME" to display the corresponding name selected from a second datatable called Employees using the employee ID to find it.

    I've been researching but i cannot find applicable examples. The only thing that I was able to find was this link where my question is perfectly described, but the provided example does not show how to do the actual "look up".

    http://www.informit.com/articles/article.aspx?p=446453&seqNum=5

    The code in the link is C#, but i had to use a C# to VB.Net online converter to try to understand the code.

    "In other situations, you may use the CellFormatting event to do something like look up a value from another table, such as using a foreign key, and use the retrieved value as the displayed value in the unbound column."

    Your help is much appreciated.

    Tuesday, July 16, 2013 10:03 PM

Answers

  • David,

    I looked through the data using Access. As much as I don’t like Access, that is a nice thing about it – you can view and edit the data directly in their program.

    Have a look first at the table named “PartePersonal”:

    Those first two rows will trip up anything you try to do.

    What I find perplexing is how value fields (columns) can be blank! A value like an integer or long can’t be blank – so I don’t know how Access works that out, but at any rate, I’ve deleted those two rows.

    Now in the table named “PersonalOB”:

    When I sort them on the column named “cdTrabajado” in ascending order, you can see that two of them have the same value. The one with “Test” in it was removed. This won’t cause a problem per se because I test if it returns any quantity of rows and if so, I take the first one (index 0) but it should only match one, so that row is now gone.

    With that, I put a DataGridView on my form bound to the table “PartePersonal”:

    I’ve scrolled it a bit so you can see both of the two unbound columns that I added called “Name 1” and “Name 2”.

    The code is pretty much what I showed the other day:

    Option Strict On Option Explicit On Public Class Form1 Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles MyBase.Load Me.PartePersonalTableAdapter.Fill(Me._PersonalObra___copiaDataSet.PartePersonal) Me.PersonalObTableAdapter.Fill(Me._PersonalObra___copiaDataSet.PersonalOb) End Sub Private Sub PartePersonalDataGridView_CellFormatting(ByVal sender As Object, _ ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) _ Handles PartePersonalDataGridView.CellFormatting If PartePersonalDataGridView.RowCount > 0 AndAlso e.RowIndex > -1 Then Dim dgvr As DataGridViewRow = PartePersonalDataGridView.Rows(e.RowIndex) Dim empID As Integer = CInt(dgvr.Cells(3).Value) Dim qry = From dr As _PersonalObra___copiaDataSet.PersonalObRow _ In _PersonalObra___copiaDataSet.PersonalOb _ Where dr.cdTrabajador = empID If qry.Count > 0 Then PartePersonalDataGridView.Rows(e.RowIndex).Cells(4).Value = qry.First.Nombre1 PartePersonalDataGridView.Rows(e.RowIndex).Cells(5).Value = qry.First.Nombre2 End If End If End Sub End Class



    I hope this might get you started. :)

     


    Please call me Frank :)

    • Marked as answer by david12king Friday, July 19, 2013 3:16 PM
    Friday, July 19, 2013 1:28 PM

All replies

  • David,

    The comment there is right - it's just lacking in substance.

    The DataGridView.CellFormatting event is a good way to do what you want. Add that event then in the event, just put the word "Stop" in it. That's the same as putting breakpoint in - your code will halt there and you can have a look around.

    Hover your mouse over the event args passed in ("e"). Does that give you an idea of how you can now proceed?

    If you're unsure - and if this isn't suitably answered tomorrow - I'll try to put an example together, but that event is fired anytime the cell is needed to be repainted so before it does, you can then get the information about it and take it from there.

    See how far you get with that first though. Figuring it out yourself with a little help always goes a lot further than simple copy/paste from someone's code posted here. :)


    Please call me Frank :)

    Tuesday, July 16, 2013 11:18 PM
  • Hello,

    I would be cautious with doing this in CellFormatting event especially if there are many records. Have you considered using a DataColumn Expression prepending the field in the secondary table with Child ?

    I have an example of using Child (in this case it's on a numeric field but you can access any field type) with a DataColumn Expression in a project called DataAccess, class file name, CustomerOrders.vb in the following MSDN Master-Detail article.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Wednesday, July 17, 2013 2:39 AM
    Moderator
  • Hello,

    I would be cautious with doing this in CellFormatting event especially if there are many records. Have you considered using a DataColumn Expression prepending the field in the secondary table with Child ?

    I have an example of using Child (in this case it's on a numeric field but you can access any field type) with a DataColumn Expression in a project called DataAccess, class file name, CustomerOrders.vb in the following MSDN Master-Detail article.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.


    I thought he didn't have a relation set - but either way, I'll defer to you on this. You're certainly the one who'd know the best way. :)

    Please call me Frank :)

    Wednesday, July 17, 2013 12:36 PM
  • Hello,

    I would be cautious with doing this in CellFormatting event especially if there are many records. Have you considered using a DataColumn Expression prepending the field in the secondary table with Child ?

    I have an example of using Child (in this case it's on a numeric field but you can access any field type) with a DataColumn Expression in a project called DataAccess, class file name, CustomerOrders.vb in the following MSDN Master-Detail article.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.


    I thought he didn't have a relation set - but either way, I'll defer to you on this. You're certainly the one who'd know the best way. :)

    Please call me Frank :)

    I was only suggesting that a relationship be set which allows access to the other table. In regards to me knowing best, not necessarily true, in theory this is a valid suggestion but may not fit the OP's project.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Wednesday, July 17, 2013 12:51 PM
    Moderator
  • Hello,

    I'll be reviewing the child example to see if i'm able to apply the code to my project WITHOUT using the second grid as that wouldn't fit with my project.

    I've looked through the code like Frank suggested, but i can't see where the look up code would go and i really don't know how to write it.

    Every time i have a question or some doubt i spend days looking through forums and Google to find the answer, because to be honest i really don't like to use forums, because most of the time people try to show you whats the best way to learn and tell you to read about it instead of helping with sample code that you can modify and apply immediately to your program and that way see how it works and learn.

    I'm reading a couple of books to learn (Practical Database Programming, Mastering Microsoft Visual Basic 2010, Clearly Visual Basic 2010), but that obviously is going to take a while and even after i finish reading them i'm still going to need examples and also I like to learn by practicing on the programs i want to build. That's why i like to go through the forums, because most of the time they have more relevant examples.

    Thanks

    Wednesday, July 17, 2013 1:27 PM
  • Okay,

    How about keeping it simple with a full example. There are two functions, each returns a data table representing data from say a backend database. In form load a LINQ statement is used to join these tables on the employee identifier and the select gets the data. Finally I use a language extension method to take the LINQ results and place them into a DataTable which becomes the DataSource for a DataGridView.

    Form code

    Public Class Form1
        Private Function MainTable() As DataTable
            Dim dt As New DataTable With {.TableName = "Main"}
            dt.Columns.Add(New DataColumn With
                           {
                               .ColumnName = "Identifier",
                               .DataType = GetType(Int32),
                               .AutoIncrement = True,
                               .AutoIncrementSeed = 1
                           }
                       )
            dt.Columns.Add(New DataColumn With
                           {
                               .ColumnName = "EmployeeIdentifier",
                               .DataType = GetType(Int32)
                           }
                       )
            dt.Columns.Add(New DataColumn With
                           {
                               .ColumnName = "TheDate",
                               .DataType = GetType(Date)
                           }
                       )
            dt.Rows.Add(New Object() {Nothing, 21, Now})
            dt.Rows.Add(New Object() {Nothing, 33, Now.AddDays(1)})
            Return dt
        End Function
        Private Function EmployeeTable() As DataTable
            Dim dt As New DataTable With {.TableName = "Emp"}
            dt.Columns.Add(New DataColumn With
                           {
                               .ColumnName = "Identifier",
                               .DataType = GetType(Int32)
                           }
                       )
            dt.Columns.Add(New DataColumn With
                           {
                               .ColumnName = "EmployeeIdentifier",
                               .DataType = GetType(Int32)
                           }
                       )
            dt.Columns.Add(New DataColumn With
                           {
                               .ColumnName = "Name",
                               .DataType = GetType(String)
                           }
                       )
            dt.Columns.Add(New DataColumn With
                           {
                               .ColumnName = "Dues",
                               .DataType = GetType(Decimal)
                           }
                       )
            dt.Rows.Add(New Object() {1, 21, "Mary Jones", 100.5})
            dt.Rows.Add(New Object() {2, 33, "Bill Smith", 34.7})
            Return dt
        End Function
        Private Sub Form1_Load(sender As Object, ee As EventArgs) Handles MyBase.Load
            DataGridView1.DataSource =
                    (
                        From T1 In MainTable.AsEnumerable
                        Join T2 In EmployeeTable.AsEnumerable On
                            T1.Field(Of Int32)("EmployeeIdentifier") Equals T2.Field(Of Int32)("EmployeeIdentifier")
                        Select New With
                               {
                                   .Identifier = T1.Field(Of Int32)("IDentifier"),
                                   .EmpId = T1.Field(Of Int32)("EmployeeIdentifier"),
                                   .EmpName = T2.Field(Of String)("Name"),
                                   .TheDate = T1.Field(Of Date)("TheDate").ToString("d"),
                                   .Dues = T2.Field(Of Decimal)("Dues").ToString("c2")
                               }
                    ).ToDataTable
        End Sub
    End Class

    Code Module code (must be a code module)

    <System.Diagnostics.DebuggerStepThrough()> _
    <System.Runtime.CompilerServices.Extension()> _
    Public Function ToDataTable(Of T)(ByVal value As IEnumerable(Of T)) As DataTable
        Dim returnTable As New DataTable
        Dim firstRecord = value.First
        For Each pi In firstRecord.GetType.GetProperties
            returnTable.Columns.Add(pi.Name, pi.GetValue(firstRecord, Nothing).GetType)
        Next
        For Each result In value
            Dim nr = returnTable.NewRow
            For Each pi In result.GetType.GetProperties
                nr(pi.Name) = pi.GetValue(result, Nothing)
            Next
            returnTable.Rows.Add(nr)
        Next
        Return returnTable
    End Function


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Wednesday, July 17, 2013 2:25 PM
    Moderator
  • With this i would have to change the datasource that i already have that fills the datagridview to use this code?

    Also i cannot convert the code VB.net http://www.developerfusion.com gives me this error:

    An error occured converting your code, probably due to a syntax error: -- line 1 col 1: EOF expected

    Thanks


    • Edited by david12king Wednesday, July 17, 2013 4:50 PM Update
    Wednesday, July 17, 2013 4:09 PM
  • Hello,

    First off, why would you want to convert my VB.NET code to C ?

    In regards to how the data is loaded, if you currently loading data into a DataTable the end result is to focus on the DataTable, not how it's loaded unless I am missing something here.

    Since you have not indicated how the data gets loaded i.e. from a Data Context class, Entity Framework or simply via coding by hand using a managed data provider such as SlqlClient or OleDb I will work with OleDb

    The following is taken from a demo project, note that a DataTable is declared followed by a DataColumn added. Next I jump to dt.Load which populates the data from the SQL statement but does not distrub the newly added DataColumn MyId as seen in the for-each loop. With little effort the same can be done with other loading methods of data.

    VB.NET Code (VS2010 syntax or higher)

        Public Function DemoLike(ByVal Text As String, ByVal FileName As String) As DataTable
            Dim dt As New DataTable
            dt.Columns.Add(New DataColumn With {.ColumnName = "MyId", .DataType = GetType(Int32), .AutoIncrement = True})
            Using cn As New OleDb.OleDbConnection With
                {
                    .ConnectionString = CreateConnectionString(FileName)
                }
                Using cmd As New OleDb.OleDbCommand With
                    {
                        .Connection = cn
                    }
                    cmd.CommandText =
                        <SQL>
                        SELECT ContactName, ContactTitle
                        FROM   Table1
                        WHERE ContactName Like @ContactName
                        </SQL>.Value
                    cmd.Parameters.Add(
                        New OleDb.OleDbParameter With
                        {
                            .ParameterName = "@ContactName",
                            .DbType = DbType.String
                        }
                    )
                    cmd.Parameters(0).Value = Text & "%"
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
                    For Each row As DataRow In dt.Rows
                        Console.WriteLine("[{0}] [{1}]",
                                          row.Field(Of Int32)("MyId"), row.Field(Of String)("ContactName"))
                    Next
                End Using
            End Using
            Return dt
        End Function


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Wednesday, July 17, 2013 5:12 PM
    Moderator
  • David,

    I would encourage you to study Kevin's suggested way - he's quite good with databases and all things related, but just to show you what I meant last night, I'll show the following.

    First, you need to see the schema of things so that the code will make sense:

    I set up a DataGridView bound to the People table, then next (in the designer), I added a new unbound column that I'm calling "Unbound_StateInitials":

    The idea here is that I'll take the name of the state from the bound table (People), then reach across and "look it up" in the Zip Codes table and then fill in the state initials based on that:

    Option Strict On Option Explicit On Public Class Form1 Private Sub Form1_Load_1(ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles MyBase.Load ZipCodesTableAdapter.Fill(ZCDataSet.ZipCodes) PeopleTableAdapter.Fill(ContactsDataSet.People) End Sub Private Sub PeopleDataGridView_CellFormatting(ByVal sender As Object, _ ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) _ Handles PeopleDataGridView.CellFormatting If e.RowIndex > -1 Then Dim dgvr As DataGridViewRow = PeopleDataGridView.Rows(e.RowIndex) Dim stateName As String = dgvr.Cells(5).Value.ToString Dim qry = From dr As ZCDataSet.ZipCodesRow In _ ZCDataSet.ZipCodes _ Where dr.StateName = stateName If qry.Count > 0 Then Dim initials As String = qry.First.StateInitials dgvr.Cells(6).Value = initials End If End If End Sub End Class


    With the data that I'm using, the People table has 25,000 records (rows) and the Zip Codes table has 43,191. What Kevin said earlier about "not recommended if there are many" is right! It's sluggish, but it does work:

    So just as an alternate, I thought I'd toss this in also.


    Please call me Frank :)

    Wednesday, July 17, 2013 6:19 PM
  • One more alternative via manual coding. Please note in the screenshot I used a custom DataGridView for grouping. First three columns are one table, last is another table that I cancatenated two fields. Employee identifier is the key.

    EDIT I thought it prudent to explain about

    WHERE Orders.OrderID &lt; 10263

    Which equates to

    WHERE Orders.OrderID < 10263
    In the second example this would faile as the IDE would see the lesser than symbol as invaid XML so we use &le which gets escaped into <

    VS2012 VB.NET working project.

    Full code

    Public Class Form1
        WithEvents bsData As New BindingSource
        ' Stock copy-paste function, used below it adapts to the folder used.
        Public Function CreateConnectionString(ByVal FileName As String) As String
            Dim Builder As New OleDb.OleDbConnectionStringBuilder
            If IO.Path.GetExtension(FileName).ToUpper = ".MDB" Then
                Builder.Add("Provider", "Microsoft.Jet.Oledb.4.0")
            Else
                Builder.Add("Provider", "Microsoft.ACE.OLEDB.12.0")
            End If
            Builder.Add("Data Source", FileName)
            Return Builder.ConnectionString
        End Function
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim dt As New DataTable
            Using cn As New OleDb.OleDbConnection With
                {
                    .ConnectionString = CreateConnectionString(
                        IO.Path.Combine(Application.StartupPath, "Database1.accdb"))
                }
                Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
                    ' Limit to 200 rows as that gets the point across
                    cmd.CommandText =
                        <SQL>
                            SELECT TOP 200
                                Orders.OrderID, 
                                Orders.OrderDate, 
                                Orders.ShipCountry, 
                                FirstName +' ' + LastName As FullName
                            FROM Employees 
                                INNER JOIN Orders ON Employees.EmployeeID=Orders.EmployeeID
                            WHERE Orders.OrderID &lt; 10263
                        </SQL>.Value
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
                End Using
            End Using
            bsData.DataSource = dt
            DataGridView1.DataSource = dt
            DataGridView1.Columns("FullName").AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
        End Sub
    End Class



    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.


    Wednesday, July 17, 2013 8:08 PM
    Moderator
  • I saw the {} and assumed it was C#, like I Said I'm very new to codding and this is the first time I've seen VB code with them.

    I'm still looking at the answers to see which one is best suited for me at this point. I'm kind of leaning more towards the cellformating event as i don't have a very big database and I haven't read anything about XML and LINQ so this is going a little over my head.

    Wednesday, July 17, 2013 11:03 PM
  • ... as i don't have a very big database and I haven't read anything about XML and LINQ so this is going a little over my head.

    XML?

    I must have missed that part - I don't see that.

    As for the LINQ - and I'm speaking for me, not Kevin - it makes life easier, but that's all. There isn't anything I've seen done with LINQ (and my code includes it too) that you can't do otherwise.

    Look at what it's doing and returning then think "now how else can I do this?" and you'll realize that you can do it all with conventional iteration and such. Sure it'll be a LOT longer bit of code, but that's not to say that LINQ is essential.

    Think about it: LINQ came out with Framework 3.5 ... so do you think everything done prior to then didn't work? Of course it did. LINQ is a big help, yes - I enjoy using it, but it's not absolutely required.

    *****

    Now that I've shot a hole in LINQ ;-)

    ... don't back away from it. It's an odd syntax but it really does do a lot in a small little package. Look into it more - investigate it and test it yourself using conventional ways, then write a LINQ statement that will do the same thing (and we'll help you with that).

    If you have a collection, LINQ can help. :)


    Please call me Frank :)

    Wednesday, July 17, 2013 11:28 PM
  • If we look at my last reply, the methods used have been around since VS2003 for data accessing backend databases. The syntax I used has been around since VS2008 and the lack of line continuations since VS2010. What I have done in the example is exploit new features of the Framework and IDE as they immerged that made sense, not simply because they are new.

    In regards to LINQ, yes and it makes life much easier and if one takes the time to study this particular statement there are no surpises.

    In plain English we are replicating a SQL SELECT with a JOIN, creating a result set of anonomous type that is strongly typed that in turn using ToDataTable returns a DataTable, nothing weak about this at any turn, nothing complex if you as I mentioned before take the time to study and learn. What I will say is LINQ is not suited for all task, knowing it's strengths and weaknesses carried a lot of weight. For instance, if you were dealing with say 500,000 records I would not have used this method but instead done this with pure SQL as in the second code block below.

    If we look at Frank's excellent example, he indicates that the navigation is sluggish. At some point my first code example would be sluggish if I were too measure it against my pure SQL method. This is why my first approach was SQL but changed lanes per-say to offer a different direction thinking perhaps the dataset is not large (and that is a good thing, data presented to users should never, ever be larger)

    Side note on large datasets, I work with on a daily basis a enterprise database with billons of records with a schema that larger than must can comprehend that is 30 years in the making. With that said I can not think of any application that presents more than 1,000 rows of data at any one time for any project thus at least in my world I never hit the sluggness discussed above. If I were to then I am doing something very wrong in regards to presenting a large dataset. If someone says I have no choice not too show a large dataset I have to wonder how their requirements got that out of control.  

    Any ways I believe between my examples and Frank's examples you have what it takes to complete your task at hand. No matter which one you select learn and grow from what has been presented. If by chance you picked mine then take what Frank presented and learn from it. If on the other hand you select Frank's method hopefully what I presented will be of some use in future endeavors. My point here is a developer never stops growing especially when "new to coding", this is a impressionable stage where many will settle for what they perceive as easy yet not as tight as other methods and I can point many out. I have taken time here to express these opinions because it is that important to the grow of what makes a developer and more importantly the rich expereince our end users have with our code.

    DataGridView1.DataSource =
             (
                 From T1 In MainTable.AsEnumerable
                 Join T2 In EmployeeTable.AsEnumerable On
                     T1.Field(Of Int32)("EmployeeIdentifier") Equals T2.Field(Of Int32)("EmployeeIdentifier")
                 Select New With
                        {
                            .Identifier = T1.Field(Of Int32)("IDentifier"),
                            .EmpId = T1.Field(Of Int32)("EmployeeIdentifier"),
                            .EmpName = T2.Field(Of String)("Name"),
                            .TheDate = T1.Field(Of Date)("TheDate").ToString("d"),
                            .Dues = T2.Field(Of Decimal)("Dues").ToString("c2")
                        }
             ).ToDataTable

    Pure SQL

                    cmd.CommandText =
                        <SQL>
                            SELECT TOP 200
                                Orders.OrderID, 
                                Orders.OrderDate, 
                                Orders.ShipCountry, 
                                FirstName +' ' + LastName As FullName
                            FROM Employees 
                                INNER JOIN Orders ON Employees.EmployeeID=Orders.EmployeeID
                            WHERE Orders.OrderID &lt; 10263
                        </SQL>.Value


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Thursday, July 18, 2013 1:50 AM
    Moderator
  • Well spoken Frank.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Thursday, July 18, 2013 1:51 AM
    Moderator
  • Bravo! :)


    Please call me Frank :)

    Thursday, July 18, 2013 2:02 AM
  • Hello,

    I'm finally going through the example and i'm stuck in i guess something very basic. The column "EmpID" is a number but get an error. Sorry for all the questions.

    Thanks

    Dim EmpID As String = dgvr.Cells(5).Value.ToString

    Private Sub PeopleDataGridView_CellFormatting(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting
    
            If e.RowIndex > -1 Then
                Dim dgvr As DataGridViewRow = DataGridView1.Rows(e.RowIndex)
                Dim EmpID As String = dgvr.Cells(5).Value.ToString
                Dim qry = From dr As PersonalObraDataSet.PersonalObRow In PersonalObraDataSet.PersonalOb Where dr.EmpID = EmpID
                If qry.Count > 0 Then
                    Dim Nombre As String = qry.First.Name1
                    dgvr.Cells(6).Value = Name
                End If
            End If
    
        End Sub

    Thursday, July 18, 2013 1:14 PM
  • Hello,

    I'm finally going through the example and i'm stuck in i guess something very basic. The column "EmpID" is a number but get an error. Sorry for all the questions.

    Thanks

    Dim EmpID As String = dgvr.Cells(5).Value.ToString

    Private Sub PeopleDataGridView_CellFormatting(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting
    
            If e.RowIndex > -1 Then
                Dim dgvr As DataGridViewRow = DataGridView1.Rows(e.RowIndex)
                Dim EmpID As String = dgvr.Cells(5).Value.ToString
                Dim qry = From dr As PersonalObraDataSet.PersonalObRow In PersonalObraDataSet.PersonalOb Where dr.EmpID = EmpID
                If qry.Count > 0 Then
                    Dim Nombre As String = qry.First.Name1
                    dgvr.Cells(6).Value = Name
                End If
            End If
    
        End Sub

    Assuming that it's in the sixth column from the left (thus the index of 5), if you're certain that it's a number then you can convert it in the code directly, with for example:

    CInt(ExpressionConvertedHere).

    Try that please?


    Please call me Frank :)

    Thursday, July 18, 2013 1:23 PM
  • Initially you said this:

    The bound datagridview has the following columns from PersonalDataTable:

    Employeed ID,Date, Department, Shift, Start Time, Finish Time,Etc.

    If that's the order they're in (in the DGV), then EmployeeID is column zero, not five.


    Please call me Frank :)

    Thursday, July 18, 2013 1:26 PM
  • How about try this and let's see. In the following, obviously you'll need to substitute the name of your DataGridView for "PeopleDataGridView" as that's the name of the one in mine:

    Private Sub PeopleDataGridView_CellFormatting(ByVal sender As Object, _ ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) _ Handles PeopleDataGridView.CellFormatting If e.RowIndex > -1 Then Dim dgvr As DataGridViewRow = PeopleDataGridView.Rows(e.RowIndex) Dim testInt As Integer = CInt(dgvr.Cells(0).Value) Stop End If End Sub


    When it gets to "Stop", hover your mouse over "testInt" and let me know if it worked? If it never gets there (an exception was raised), let me know what that says please?


    Please call me Frank :)

    Thursday, July 18, 2013 1:47 PM
  • Hello,

    I just figured out is not a number it is a string and i did have the wrong column number, but still i get a Null error. Thank you so much for taking the time to help.

    Thursday, July 18, 2013 2:12 PM
  • That's hard to say. Is it, in fact, null? Are you sure that the data has been loaded at that time?

    You might want to try this to be sure:

    If PeopleDataGridView.RowCount > 0 AndAlso e.RowIndex > -1 Then
                ' Now put the code here
    End If


    Please call me Frank :)

    Thursday, July 18, 2013 2:18 PM
  • I'm not sure, but i'm thinking is not yet, because i have the fillby() on a click event. This is the complete code for the form. If i should be starting a new thread with this, let me know.

    Public Class frmParteDiarioPersonal
        Private Sub btnVolver_Click_1(sender As System.Object, e As System.EventArgs) Handles btnVolver.Click
            Me.Close()
            frmMain.Show()
        End Sub
        Private Sub frmParteDiarioPersonal_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
            Dim ParteDiarioPersonalTableApt As New PersonalObraDataSetTableAdapters.PartePersonalTableAdapter
            txtDia.Text = Convert.ToString(txtDate.Value.ToString("dddd"))
        End Sub
        Private Sub DataGridView1_DefaultValuesNeeded(sender As System.Object, e As System.Windows.Forms.DataGridViewRowEventArgs) Handles DataGridView1.DefaultValuesNeeded
            With e.Row
    
                Dim myInt As Integer = 0
                If Integer.TryParse(txtCdEmpresa.Text, myInt) Then
                    .Cells("cdempresa").Value = myInt
                Else
                    .Cells("cdempresa").Value = 0
                End If
    
                If Integer.TryParse(txtDepartamento.Text, myInt) Then
                    .Cells("cddepartamento").Value = myInt
                Else
                    .Cells("cddepartamento").Value = 0
                End If
    
                .Cells("fecha").Value = Format(txtDate.Value, "dd/MM/yyyy")
                .Cells("cdactividad").Value = txtActividad.Text
                .Cells("HInicio").Value = txtEntrada.Text
                .Cells("HFin").Value = txtSalida.Text
            End With
        End Sub
        Private Sub btnCargarInformacion_Click(sender As System.Object, e As System.EventArgs) Handles btnCargar.Click
            Dim ParteDiarioPersonalTableApt As New PersonalObraDataSetTableAdapters.PartePersonalTableAdapter
            Dim PersonalObTableApt As New PersonalObraDataSetTableAdapters.PersonalObTableAdapter
            ParteDiarioPersonalTableApt.ClearBeforeFill = True
            ParteDiarioPersonalTableApt.FillByFecha(PersonalObraDataSet.PartePersonal, txtDate.Text)
            PersonalObTableApt.ClearBeforeFill = True
            PersonalObTableApt.Fillby(PersonalObraDataSet.PersonalOb)
        End Sub
        Private Sub txtDate_ValueChanged(sender As Object, e As System.EventArgs) Handles txtDate.ValueChanged
            txtDia.Text = Convert.ToString(txtDate.Value.ToString("dddd"))
        End Sub
        Private Sub PeopleDataGridView_CellFormatting(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting
            If DataGridView1.RowCount > 0 AndAlso e.RowIndex > -1 Then
                If e.RowIndex > -1 Then
                    Dim dgvr As DataGridViewRow = DataGridView1.Rows(e.RowIndex)
                    Dim cdtrabajador As String = dgvr.Cells(0).Value.ToString
                    Dim qry = From dr As PersonalObraDataSet.PersonalObRow In PersonalObraDataSet.PersonalOb Where dr.cdTrabajador = cdtrabajador
                    If qry.Count > 0 Then
                        Dim Nombre As String = qry.First.Nombre1
                        dgvr.Cells(1).Value = Nombre
                    End If
                End If
            End If
        End Sub
    
    End Class

    Thursday, July 18, 2013 4:29 PM
  • If there's no data then of course it'll result in an exception.

    Did you try to add the "If DGV.RowCount > 0 ..." part in?


    Please call me Frank :)

    Thursday, July 18, 2013 4:37 PM
  • Yes it's in there.

    If i click the button to load the information i see the grid fill the first row and then the exception.

    I have no idea how to fix it or even to research a little more about it.

    Thursday, July 18, 2013 5:08 PM
  • Yes it's in there.

    If i click the button to load the information i see the grid fill the first row and then the exception.

    I have no idea how to fix it or even to research a little more about it.

    Try to take it a step at a time. Let's see if it gets this far without an exception:

        Private Sub DataGridView1_CellFormatting(ByVal sender As Object, _
                                                      ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) _
                                                      Handles DataGridView1.CellFormatting
    
            If DataGridView1.RowCount > 0 AndAlso e.RowIndex > -1 Then
    
                If e.RowIndex > -1 Then
                    Dim dgvr As DataGridViewRow = DataGridView1.Rows(e.RowIndex)
                    Stop
                End If
            End If
    
        End Sub


    Please call me Frank :)

    Thursday, July 18, 2013 5:13 PM
  • No exceptions until this line:

     Dim cdtrabajador As String = dgvr.Cells(0).Value.ToString

    • Edited by david12king Thursday, July 18, 2013 5:32 PM Update
    Thursday, July 18, 2013 5:27 PM
  • No exceptions until the stop

    Good!

    Now modify that to this one:

    Private Sub DataGridView1_CellFormatting(ByVal sender As Object, _ ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) _ Handles DataGridView1.CellFormatting If DataGridView1.RowCount > 0 AndAlso e.RowIndex > -1 Then If e.RowIndex > -1 Then Dim dgvr As DataGridViewRow = DataGridView1.Rows(e.RowIndex) Dim test As Object = dgvr.Cells(0).Value Stop End If End If End Sub


    When it gets to "Stop", hover your mouse over the variable "test". It will show the type (for instance, String or Long or Int32, etc) and it will show you the value of it.

    If that's not the correct column, then change the index number one at a time until you find the column we're interested in (the Employee ID) and let me know your results please.


    Please call me Frank :)

    Thursday, July 18, 2013 5:30 PM
  • variable "test" with each column:

    0 = {}

    1 = Nothing<-----The word nothing

    2 = 0 {Integer}

    3 = ""{String}

    4 = ":"{String}

    5 = ":"{String}

    6 = {}

    7 = 0 {Integer}

    8 = {}

    9 = {}

    10 = {}

    11 = {}

    12 = #7/18/20123# {Date}

    Thursday, July 18, 2013 5:54 PM
  • I don't know what to make of that!

    I was hoping we were getting close but that one has me totally stumped. It's like it's all just "blank data".


    Please call me Frank :)

    Thursday, July 18, 2013 6:13 PM
  • What database type are you using?

    Please call me Frank :)

    Thursday, July 18, 2013 6:13 PM
  • MS Access
    Thursday, July 18, 2013 6:47 PM
  • MS Access

    What version?

    I can work with Access 2007 and if that's what it is, I was going to suggest that you upload the two of them (I can post a link for you to do that) unless it's confidential information.

    Of course I still won't have your setup for them, but I can make a simple one and show you.


    Please call me Frank :)

    Thursday, July 18, 2013 6:56 PM
  • Sorry, it's Access 2010. i can upload it no problem, it doesn't have any personal information other than names.
    Thursday, July 18, 2013 8:38 PM
  • Sorry, it's Access 2010. i can upload it no problem, it doesn't have any personal information other than names.

    I can't work with that so - I'm honestly just at a dead end here.

    Go back and try Kevin's many suggestions. I've never yet seen him wrong when it comes to this stuff!


    Please call me Frank :)

    Thursday, July 18, 2013 8:40 PM
  • i have an old backup of the form in .mdb format, as i would still like to see your example.
    Thursday, July 18, 2013 9:21 PM
  • i have an old backup of the form in .mdb format, as i would still like to see your example.

    I can work with Access 2007 files (.accdb) and SQL CE 3.5 files (.sdf).

    I'm not a database heavyweight as you can tell! ;-)


    Please call me Frank :)

    Thursday, July 18, 2013 9:24 PM
  • you can open the .mdb file without any problems and even save it as the 2007 format. I really would like to see your example. Thanks
    Thursday, July 18, 2013 9:41 PM
  • you can open the .mdb file without any problems and even save it as the 2007 format. I really would like to see your example. Thanks

    I'm willing to give it a try - no promises.

    You can upload it here:

    https://swft.exavault.com/share/view/fmu-cgr7ff6d

    If you would, please let me know what files I need to look for (or just create a new folder and put them there).


    Please call me Frank :)


    Thursday, July 18, 2013 9:43 PM
  • The file is uploaded is PersonalObra.mdb I really appreciate you taking the time to look it over.

    If it makes it easier i can can upload the whole project.

    • Edited by david12king Thursday, July 18, 2013 10:06 PM Update
    Thursday, July 18, 2013 9:55 PM
  • The file is uploaded is PersonalObra.mdb I really appreciate you taking the time to look it over.

    Ok, let's see how far I can get and I'll report back.


    Please call me Frank :)

    Thursday, July 18, 2013 9:57 PM
  • I'm surprised, but you're right - I can open them!

    Tell me what I'm looking at though?


    Please call me Frank :)

    Thursday, July 18, 2013 10:08 PM
  • Parte personal - Daily timesheet

    Project ID

    Date

    Company ID

    Employee ID

    Activity ID

    Start Time

    End Time

    Hours Worked

    Hours (this was going to be over time hours)

    Shift ID

    Equipment ID

    Brand

    Model

    Liters/Gallons 

    -Not used

    -Not Used

    -Not Used

    Type of shift (8 hour shift or longer)

    Number of the line

    Department ID

    -Not Used

    Employees

    -Project ID

    -Company ID

    -Employee ID

    -Department ID

    -Hire Date

    -Fired Date

    -Name

    -Middle Name

    -Last Name

    -

    Not Used

    Thursday, July 18, 2013 10:21 PM
  • "Not Used" should be not there ... in my opinion - unless you set it with default data (or it's a value type), I bet I'll end up dealing with null exceptions but we'll see.

    I'll print out this "legend" ... ughh ... so with that, tell me what I need to do. Which one is the DGV bound to and what needs to happen from there?


    Please call me Frank :)

    Thursday, July 18, 2013 10:26 PM
  • The datagridview is bound to PartePersonal. I have a set of text boxes that set the default value of some of the columns that way the user doesn't have to enter that information for each row.

    For example if you enter in the text boxes the date, the shift, the start time, end time, and department, you only have to enter the employee id, number of hours worked and equipment used.

    The part that we are having problems with is an unbound column that should take the employee ID as reference to return the employee name (Name1, Name2,,etc) from the Employees (PersonalOb) table.

    The columns that are not used are not being added to the datagridview so i don't think they are returning any values.

    If it's easier i can upload the whole project so you don't have to build the thing from scratch.

    Thursday, July 18, 2013 10:39 PM
  • The datagridview is bound to PartePersonal. I have a set of text boxes that set the default value of some of the columns that way the user doesn't have to enter that information for each row.

    For example if you enter in the text boxes the date, the shift, the start time, end time, and department, you only have to enter the employee id, number of hours worked and equipment used.

    The part that we are having problems with is an unbound column that should take the employee ID as reference to return the employee name (Name1, Name2,,etc) from the Employees (PersonalOb) table.

    The columns that are not used are not being added to the datagridview so i don't think they are returning any values.

    If it's easier i can upload the whole project so you don't have to build the thing from scratch.

    Last thing first, I can't open your project as I'm using VS2008 and I assume you're using a higher version than that.

    I'll have to play around with the TableAdapter to get it to not load all columns but it's doable.

    Given that and the time of day, I'll save the project and return to it tomorrow.

    You also mentioned a FillBy Query. Explain that part please? I don't know if it plays into the issue here or not.


    Please call me Frank :)

    Thursday, July 18, 2013 10:48 PM
  • Have a look at this please:

    Option Strict On Option Explicit On Public Class Form1 Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles MyBase.Load Me.PartePersonalTableAdapter.Fill(Me._PersonalObra___copiaDataSet.PartePersonal) Me.PersonalObTableAdapter.Fill(Me._PersonalObra___copiaDataSet.PersonalOb) End Sub Private Sub PartePersonalDataGridView_CellFormatting(ByVal sender As Object, _ ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) _ Handles PartePersonalDataGridView.CellFormatting If PartePersonalDataGridView.RowCount > 0 AndAlso e.RowIndex > -1 Then Dim dgvr As DataGridViewRow = PartePersonalDataGridView.Rows(e.RowIndex) Dim empID As Integer = CInt(dgvr.Cells(3).Value) Dim qry = From dr As _PersonalObra___copiaDataSet.PersonalObRow _ In _PersonalObra___copiaDataSet.PersonalOb _ Where dr.cdTrabajador = empID Stop End If End Sub End Class


    Hover your mouse over and expand each "+" you see there.

    I'm seeing more than one entry that matches the employee ID ... at least if I used the correct field names? I'm really just guessing some since I don't know what I'm reading.

    If I did it correctly then the data isn't right - it shouldn't match but one record (row) in the other DataTable.


    Please call me Frank :)

    Thursday, July 18, 2013 11:25 PM
  • I uploaded the project to the link link you gave me i think it should open with visual studio 2008 is the folder PersonalObra. Thank you so much for all your help.

    This is the query made by the query builder on the table adatper.

    SELECT        cdobra, fecha, cdempresa, cdtrabajador, cdactividad, HInicio, HFin, horas, horasES, cdturno, cdparque, marca, modelo, litros, destroza, precorte, CyVol, 
                             cdtipojornada, linea, cddepartamento, turnoES
    FROM            PartePersonal
    WHERE        (fecha = ?)

    This the code for the entire form.

    Public Class frmParteDiarioPersonal
        Private Sub btnVolver_Click_1(sender As System.Object, e As System.EventArgs) Handles btnVolver.Click
            Me.Close()
            frmMain.Show()
        End Sub
        Private Sub frmParteDiarioPersonal_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
            Dim ParteDiarioPersonalTableApt As New PersonalObraDataSetTableAdapters.PartePersonalTableAdapter
            txtDia.Text = Convert.ToString(txtDate.Value.ToString("dddd"))
        End Sub
        Private Sub DataGridView1_DefaultValuesNeeded(sender As System.Object, e As System.Windows.Forms.DataGridViewRowEventArgs) Handles DataGridView1.DefaultValuesNeeded
            With e.Row
    
                Dim myInt As Integer = 0
                If Integer.TryParse(txtCdEmpresa.Text, myInt) Then
                    .Cells("cdempresa").Value = myInt
                Else
                    .Cells("cdempresa").Value = 0
                End If
    
                If Integer.TryParse(txtDepartamento.Text, myInt) Then
                    .Cells("cddepartamento").Value = myInt
                Else
                    .Cells("cddepartamento").Value = 0
                End If
    
                .Cells("fecha").Value = Format(txtDate.Value, "dd/MM/yyyy")
                .Cells("cdactividad").Value = txtActividad.Text
                .Cells("HInicio").Value = txtEntrada.Text
                .Cells("HFin").Value = txtSalida.Text
            End With
        End Sub
        Private Sub btnCargarInformacion_Click(sender As System.Object, e As System.EventArgs) Handles btnCargar.Click
            Dim ParteDiarioPersonalTableApt As New PersonalObraDataSetTableAdapters.PartePersonalTableAdapter
            Dim PersonalObTableApt As New PersonalObraDataSetTableAdapters.PersonalObTableAdapter
            ParteDiarioPersonalTableApt.ClearBeforeFill = True
            ParteDiarioPersonalTableApt.FillByFecha(PersonalObraDataSet.PartePersonal, txtDate.Text)
            PersonalObTableApt.ClearBeforeFill = True
            PersonalObTableApt.Fillby(PersonalObraDataSet.PersonalOb)
        End Sub
        Private Sub txtDate_ValueChanged(sender As Object, e As System.EventArgs) Handles txtDate.ValueChanged
            txtDia.Text = Convert.ToString(txtDate.Value.ToString("dddd"))
        End Sub
        Private Sub DataGridView1_CellFormatting(ByVal sender As Object, _
                                                     ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) _
                                                     Handles DataGridView1.CellFormatting
    
            If DataGridView1.RowCount > 0 AndAlso e.RowIndex > -1 Then
                If e.RowIndex > -1 Then
                    Dim dgvr As DataGridViewRow = DataGridView1.Rows(e.RowIndex)
                    Dim test As Object = dgvr.Cells(0).Value
                    Stop
                End If
            End If
    
        End Sub
        'Private Sub PeopleDataGridView_CellFormatting(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting
        '   If DataGridView1.RowCount > 0 AndAlso e.RowIndex > -1 Then
        '      If e.RowIndex > -1 Then
        'Dim dgvr As DataGridViewRow = DataGridView1.Rows(e.RowIndex)
        'Dim cdtrabajador As String = dgvr.Cells(0).Value.ToString
        'Dim qry = From dr As PersonalObraDataSet.PersonalObRow In PersonalObraDataSet.PersonalOb Where dr.cdTrabajador = cdtrabajador
        '            If qry.Count > 0 Then
        'Dim Nombre As String = qry.First.Nombre1
        '                dgvr.Cells(1).Value = Nombre
        '            End If
        '        End If
        '    End If
        'End Sub
    End Class

    Thursday, July 18, 2013 11:29 PM
  • David,

    I'm not going to go through the whole program ... if that's being rude then so be it, but I'm simply not going to do it.

    I'm trying to help with concepts. I'll stay at this level.

    I'll leave whatever you've uploaded there (for a while) if someone else wants them, but I'm not going to - particularly since I don't even speak the language so I don't know what these things mean!


    Please call me Frank :)

    Thursday, July 18, 2013 11:35 PM
  • No it's not rude, I JUst thought that it would be easier for you if you could look at it instead of me trying to explain it. I appreciate your help. If your not going to use it the project please delete it. Thanks
    Friday, July 19, 2013 12:20 AM
  • I see some issues with the data itself, but I don't want to comment until I've had time to see what I can do.

    I'll look at it again tomorrow though.

    Don't give up yet. ;-)


    Please call me Frank :)

    Friday, July 19, 2013 12:42 AM
  • David,

    I looked through the data using Access. As much as I don’t like Access, that is a nice thing about it – you can view and edit the data directly in their program.

    Have a look first at the table named “PartePersonal”:

    Those first two rows will trip up anything you try to do.

    What I find perplexing is how value fields (columns) can be blank! A value like an integer or long can’t be blank – so I don’t know how Access works that out, but at any rate, I’ve deleted those two rows.

    Now in the table named “PersonalOB”:

    When I sort them on the column named “cdTrabajado” in ascending order, you can see that two of them have the same value. The one with “Test” in it was removed. This won’t cause a problem per se because I test if it returns any quantity of rows and if so, I take the first one (index 0) but it should only match one, so that row is now gone.

    With that, I put a DataGridView on my form bound to the table “PartePersonal”:

    I’ve scrolled it a bit so you can see both of the two unbound columns that I added called “Name 1” and “Name 2”.

    The code is pretty much what I showed the other day:

    Option Strict On Option Explicit On Public Class Form1 Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles MyBase.Load Me.PartePersonalTableAdapter.Fill(Me._PersonalObra___copiaDataSet.PartePersonal) Me.PersonalObTableAdapter.Fill(Me._PersonalObra___copiaDataSet.PersonalOb) End Sub Private Sub PartePersonalDataGridView_CellFormatting(ByVal sender As Object, _ ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) _ Handles PartePersonalDataGridView.CellFormatting If PartePersonalDataGridView.RowCount > 0 AndAlso e.RowIndex > -1 Then Dim dgvr As DataGridViewRow = PartePersonalDataGridView.Rows(e.RowIndex) Dim empID As Integer = CInt(dgvr.Cells(3).Value) Dim qry = From dr As _PersonalObra___copiaDataSet.PersonalObRow _ In _PersonalObra___copiaDataSet.PersonalOb _ Where dr.cdTrabajador = empID If qry.Count > 0 Then PartePersonalDataGridView.Rows(e.RowIndex).Cells(4).Value = qry.First.Nombre1 PartePersonalDataGridView.Rows(e.RowIndex).Cells(5).Value = qry.First.Nombre2 End If End If End Sub End Class



    I hope this might get you started. :)

     


    Please call me Frank :)

    • Marked as answer by david12king Friday, July 19, 2013 3:16 PM
    Friday, July 19, 2013 1:28 PM
  • Fist of all i just want to say Thank you for sticking with this for so long.

    I have good and bad news. The bad news is that i still have the DBNull error. The good news is that i figured out what the problem is. More bad news I still need help to figure out how to fix it.

    We keep getting the Null error every time, because the formatting event takes place as soon as you click on the grid, before you have a chance to enter the employee ID, so it is looking at an empty cell, it doesn't have to do with the Database or those duplicate ids you found.

    The question is how do you delay the cellformating until after the Employee ID has been entered, not before?

    is it possible to put the cellformatting inside the cell leave event?

    Friday, July 19, 2013 2:31 PM
  • Fist of all i just want to say Thank you for sticking with this for so long.

    I have good and bad news. The bad news is that i still have the DBNull error. The good news is that i figured out what the problem is. More bad news I still need help to figure out how to fix it.

    We keep getting the Null error every time, because the formatting event takes place as soon as you click on the grid, before you have a chance to enter the employee ID, so it is looking at an empty cell, it doesn't have to do with the Database or those duplicate ids you found.

    The question is how do you delay the cellformating until after the Employee ID has been entered, not before?

    is it possible to put the cellformatting inside the cell leave event?

    Maybe we've been saying the same thing but thinking differently.

    I don't understand how the Employee ID doesn't exist - it did in what you sent to me. So is this maybe for adding a new record (row)?


    Please call me Frank :)

    Friday, July 19, 2013 2:38 PM
  • Yes on the new row, because the form loads fine with the Name1 Column with the corresponding name, but when you click on the datagridview, doesn´t matter which cell, it immediately fires the cellformating and looks at the empty cell in the new row.
    Friday, July 19, 2013 2:45 PM
  • Yes on the new row, because the form loads fine with the Name1 Column with the corresponding name, but when you click on the datagridview, doesn´t matter which cell, it immediately fires the cellformating and looks at the empty cell in the new row.

    Let me state the disclaimer that I used to say here all the time: "I'm not a database guy!"

    With that said - this is just my opinion - but I never use a DGV for anything other than display. In fact I always set it as read-only and disable adding, editing, or removing from the DGV.

    The way that I've come to add (or edit) is to use another form shown modally. That way, before it's added I can test the validity of the input including whether or not it's already in the datatable. Then after I'm sure of that, I add it (in code) to the datatable which will then show up in the DGV.

    Not the answer you were looking for but - that's how I'd do it.


    Please call me Frank :)

    Friday, July 19, 2013 2:51 PM
  • Frank,

    In regards to the blanks in the first two rows, as with any major database you can set a field up to accept nulls and MS-Access allows this for numeric and date types. So if there is a business rule that permits this it must a) be respected b) modified so that null is not permited but instead assertion in code and a modified schema is done.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Friday, July 19, 2013 2:58 PM
    Moderator
  • Frank,

    In regards to the blanks in the first two rows, as with any major database you can set a field up to accept nulls and MS-Access allows this for numeric and date types. So if there is a business rule that permits this it must a) be respected b) modified so that null is not permited but instead assertion in code and a modified schema is done.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.


    I've said before and will again - I'm a database lightweight. I can make them work for my needs, but that's the extent of it.

    Please call me Frank :)

    Friday, July 19, 2013 3:01 PM
  • Thanks again for all you help and time invested.

    I still think there is (or should be) a way to make sure the cellformatting doesn't fire until after the user had a chance to enter the employee ID. I'll keep looking for the answer if i don't find anything by myself  i'll post it as a new thread.

    Friday, July 19, 2013 3:24 PM
  • I still think there is (or should be) a way to make sure the cellformatting doesn't fire until after the user had a chance to enter the employee ID. I'll keep looking for the answer if i don't find anything by myself  i'll post it as a new thread.

    David,

    You might want to try something but let me say in advance that I've never done this and have no idea if it'll work or not.

    In your DataSources window, right-click and choose to edit in the designer:

    You'll see a new window open:

    Now select the field (column name) of interest:

    Then with that selected, look at the properties window:

    Notice in that last screenshot that the property for "NullValue" is showing to throw an exception. I'm not sure this is the best one, but maybe try setting it to "Nothing" instead:

    In the CellFormattingEvent, test that it IsNot Nothing and see if that helps?


    Please call me Frank :)

    Friday, July 19, 2013 4:04 PM
  • Just looking at those screenshots, you might also want to experiment with "DefaultValue". That looks to be promising.

    It's worth a shot anyway.


    Please call me Frank :)

    Friday, July 19, 2013 4:07 PM
  • This the answer!

    I knew there was something that needed to tell the formatting to stop if the cell on datagridview, not the database, was empty.

    If dgvr.Cells(0).Value IsNot Nothing AndAlso dgvr.Cells(0).Value IsNot DBNull.Value Then
                    Dim empID As Integer = CInt(dgvr.Cells(0).Value)

    Thought you would like to know.

    Thanks again.

    Friday, July 19, 2013 5:10 PM
  • This the answer!

    I knew there was something that needed to tell the formatting to stop if the cell on datagridview, not the database, was empty.

    If dgvr.Cells(0).Value IsNot Nothing AndAlso dgvr.Cells(0).Value IsNot DBNull.Value Then
                    Dim empID As Integer = CInt(dgvr.Cells(0).Value)

    Thought you would like to know.

    Thanks again.


    Good work! :)

    Please call me Frank :)

    Friday, July 19, 2013 5:12 PM
  • David,

    I looked through the data using Access. As much as I don’t like Access, that is a nice thing about it – you can view and edit the data directly in their program.

    Have a look first at the table named “PartePersonal”:

    Those first two rows will trip up anything you try to do.

    What I find perplexing is how value fields (columns) can be blank! A value like an integer or long can’t be blank – so I don’t know how Access works that out, but at any rate, I’ve deleted those two rows.

    Now in the table named “PersonalOB”:

    When I sort them on the column named “cdTrabajado” in ascending order, you can see that two of them have the same value. The one with “Test” in it was removed. This won’t cause a problem per se because I test if it returns any quantity of rows and if so, I take the first one (index 0) but it should only match one, so that row is now gone.

    With that, I put a DataGridView on my form bound to the table “PartePersonal”:

    I’ve scrolled it a bit so you can see both of the two unbound columns that I added called “Name 1” and “Name 2”.

    The code is pretty much what I showed the other day:

    Option Strict On Option Explicit On Public Class Form1 Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles MyBase.Load Me.PartePersonalTableAdapter.Fill(Me._PersonalObra___copiaDataSet.PartePersonal) Me.PersonalObTableAdapter.Fill(Me._PersonalObra___copiaDataSet.PersonalOb) End Sub Private Sub PartePersonalDataGridView_CellFormatting(ByVal sender As Object, _ ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) _ Handles PartePersonalDataGridView.CellFormatting If PartePersonalDataGridView.RowCount > 0 AndAlso e.RowIndex > -1 Then Dim dgvr As DataGridViewRow = PartePersonalDataGridView.Rows(e.RowIndex) Dim empID As Integer = CInt(dgvr.Cells(3).Value) Dim qry = From dr As _PersonalObra___copiaDataSet.PersonalObRow _ In _PersonalObra___copiaDataSet.PersonalOb _ Where dr.cdTrabajador = empID If qry.Count > 0 Then PartePersonalDataGridView.Rows(e.RowIndex).Cells(4).Value = qry.First.Nombre1 PartePersonalDataGridView.Rows(e.RowIndex).Cells(5).Value = qry.First.Nombre2 End If End If End Sub End Class



    I hope this might get you started. :)

     


    Please call me Frank :)

    I know it's been a while since we talked about this, but is it possible for you to expand a little on the last block of code?

    You helped look-up the name using the employee ID with the query (qry), is it possible to add to the sql statement to show the name only if the employee doesn't have a termination date?

    something like this:

    Where (dr.cdTrabajador = empID) And (TDate Is DBNull.Value) Or (TDate <= txtDate.Text)

    This first statement is where we left off last time.

    If i should start another thread let me know.

    Thanks in advance.


    • Edited by david12king Monday, July 29, 2013 11:13 PM update
    Monday, July 29, 2013 11:10 PM
  • I know it's been a while since we talked about this, but is it possible for you to expand a little on the last block of code?

    You helped look-up the name using the employee ID with the query (qry), is it possible to add to the sql statement to show the name only if the employee doesn't have a termination date?

    something like this:

    Where (dr.cdTrabajador = empID) And (TDate Is DBNull.Value) Or (TDate <= txtDate.Text)


    This first statement is where we left off last time.

    If i should start another thread let me know.

    Thanks in advance.


    I don't know the first thing about Sql statements, sorry.

    Please call me Frank :)

    Monday, July 29, 2013 11:58 PM