Results 1 to 13 of 13

Thread: Logic

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jul 2021
    Posts
    71

    Logic

    Hi,

    I have the follow code that works fine.. It pulls all the registered tenants from the tbltenants to the tenantname column - combobox dropdownlist to select from.

    Code:
     Try
                Using con As New SqlConnection("Data Source=DESKTOP-O6TSDMJ;Initial Catalog=RCMS;Integrated Security=True")
                    DsUnitdetails.EnforceConstraints = False
                    Me.VwunitdetailsTableAdapter.Fill(Me.DsUnitdetails.vwunitdetails, TxtPropertyIDClick.Text)
    
                    con.Open()
    
                    Using cmd As New SqlCommand("Select tenantregID, tenantfullname from tbltenants order by firstname", con)
                        Using myreader As SqlClient.SqlDataReader = cmd.ExecuteReader
                            TenantName.Items.Clear()
                            While myreader.Read
                                TenantName.Items.Add(myreader("tenantfullname"))
                            End While
                        End Using
                    End Using
    
                    con.Close()
    
                End Using
            Catch ex As Exception
                MessageBox.Show(ex.ToString())
            End Try

    Now, on my datagridview I have a column 'TenantName' - this has the names of ALL the tenants from the tenants table such as John Smith, Alex James and Ali Omar.

    In the tbltenants - each tenant has a unique tenantregID, so considering the above:

    John Smith has a tenantregID = 1
    Alex James has a tenantregID = 2
    Ali Omar has a tenantregID = 3


    Now, when I call the update statement - I want for the selected tenant name, the tenantregID is instead inserted to the database i.e., to the table tblunits..

    The update query is below:

    Code:
     query = "UPDATE dbo.tblunits SET flattype=@flattype, units=@units, rentamt=@rentamt, vacant=@vacant, tenantregID=@tenantregID WHERE propertyID=@propertyID and unitID=@unitID"
                    cmd = New SqlCommand(query, conn1)
    
                    cmd.Parameters.AddWithValue("@flattype", DgUnitsUp.Rows(i).Cells(5).Value.ToString())
                    cmd.Parameters.AddWithValue("@units", DgUnitsUp.Rows(i).Cells(1).Value.ToString())
                    cmd.Parameters.AddWithValue("@rentamt", DgUnitsUp.Rows(i).Cells(2).Value.ToString())
                    cmd.Parameters.AddWithValue("@vacant", DgUnitsUp.Rows(i).Cells(3).Value.ToString())
                    cmd.Parameters.AddWithValue("@tenantregID", #########################)
                    cmd.Parameters.AddWithValue("@propertyID", DgUnitsUp.Rows(i).Cells(0).Value.ToString())
                    cmd.Parameters.AddWithValue("@UnitID", DgUnitsUp.Rows(i).Cells(9).Value.ToString())
    So even though, the user has selected a tenant name from the combobox - the tenantregID is saved instead to the database.

    Note: I have a bounded textbox called tenantregID set to Visible False on the datagridview for all the rows. This is however set to NULL at the moment because no Tenant has been selected. So when a tenant is updated I expect the tenantregID for that unit to be updated by being matched to the tenantfullname.

    How can I do this please?

    Thanks,
    Riyadh
    Last edited by dr225; Sep 16th, 2021 at 11:03 AM.

  2. #2
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    Re: Logic

    Populate the tenant records into a DataTable, bind the ComboBox to the DataTable, set the DisplayMember to tenantfullname and the ValueMember to tenantregId:
    Code:
    Dim dt As New DataTable()
    Using con As New SqlConnection("Data Source=DESKTOP-O6TSDMJ;Initial Catalog=RCMS;Integrated Security=True")
        Using cmd As New SqlCommand("Select tenantregID, tenantfullname from tbltenants order by firstname", con)
            Using adapter As New SqlDataAdapter(cmd)
                adapter.Fill(dt)
                With TenantName
                    .DisplayMember = "tenantfullname"
                    .ValueMember = "tenantregID"
                    .DataSource = dt
                End With
            End Using
        End Using
    End Using
    Now when you need to get the tenantregID from the ComboBox, you'd get the SelectedValue:
    Code:
    cmd.Parameters.AddWithValue("@tenantregID", TenantName.SelectedValue)
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Jul 2021
    Posts
    71

    Re: Logic

    Code:
    cmd.Parameters.AddWithValue("@vacant", DgUnitsUp.Rows(i).Cells(3).Value.ToString())
          cmd.Parameters.AddWithValue("@tenantregID", TenantName.SelectedValue)
                    cmd.Parameters.AddWithValue("@UnitID", DgUnitsUp.Rows(i).Cells(9).Value.ToString())
    Error message:

    TenantName - SelectedValue is not a member of 'DataGridViewComboBoxColumn'

  4. #4
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    Re: Logic

    You did not mention that this was a DataGridViewComboBoxColumn in your original post, that would have been helpful.

    While I cannot test, I believe it would be as simple as getting the cell's value.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Jul 2021
    Posts
    71

    Re: Logic

    Code:
                    query = "UPDATE dbo.tblunits SET flattype=@flattype, units=@units, rentamt=@rentamt, vacant=@vacant, tenantregID=@tenantregID WHERE propertyID=@propertyID and unitID=@unitID"
                    cmd = New SqlCommand(query, conn1)
    
                    cmd.Parameters.AddWithValue("@flattype", DgUnitsUp.Rows(i).Cells(6).Value.ToString())
                    cmd.Parameters.AddWithValue("@units", DgUnitsUp.Rows(i).Cells(1).Value.ToString())
                    cmd.Parameters.AddWithValue("@rentamt", DgUnitsUp.Rows(i).Cells(2).Value.ToString())
                    cmd.Parameters.AddWithValue("@vacant", DgUnitsUp.Rows(i).Cells(3).Value.ToString())
                    cmd.Parameters.AddWithValue("@tenantregID", DgUnitsUp.Rows(i).Cells(8).Value.ToString())
                    cmd.Parameters.AddWithValue("@propertyID", DgUnitsUp.Rows(i).Cells(0).Value.ToString())
                    cmd.Parameters.AddWithValue("@UnitID", DgUnitsUp.Rows(i).Cells(9).Value.ToString())
    At runtime, it works for the first update and if I decide to edit and update again - then I receive the error message:


    conversion failed when converting the nvarchar ' John Smith' to data type int

    Note: John Smith (Tenant Name) is in the second row of the datagridview

    Thanks

  6. #6
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    Re: Logic

    Are you overwriting the ComboBox item's with the SqlDataReader code?
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  7. #7
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Logic

    Quote Originally Posted by dday9 View Post
    Populate the tenant records into a DataTable, bind the ComboBox to the DataTable, set the DisplayMember to tenantfullname and the ValueMember to tenantregId:
    Code:
    Dim dt As New DataTable()
    Using con As New SqlConnection("Data Source=DESKTOP-O6TSDMJ;Initial Catalog=RCMS;Integrated Security=True")
        Using cmd As New SqlCommand("Select tenantregID, tenantfullname from tbltenants order by firstname", con)
            Using adapter As New SqlDataAdapter(cmd)
                adapter.Fill(dt)
                With TenantName
                    .DisplayMember = "tenantfullname"
                    .ValueMember = "tenantregID"
                    .DataSource = dt
                End With
            End Using
        End Using
    End Using
    Now when you need to get the tenantregID from the ComboBox, you'd get the SelectedValue:
    Code:
    cmd.Parameters.AddWithValue("@tenantregID", TenantName.SelectedValue)
    For the record, you don't need to nest Using blocks if there is not code between them. A single using statement can be used to create multiple objects:
    vb.net Code:
    1. Using con As New SqlConnection("Data Source=DESKTOP-O6TSDMJ;Initial Catalog=RCMS;Integrated Security=True"),
    2.       cmd As New SqlCommand("Select tenantregID, tenantfullname from tbltenants order by firstname", con),
    3.       adapter As New SqlDataAdapter(cmd)
    4.     '...
    5. End Using
    That said, in this case, you don;t need to create the connection and the command because there's a data adapter constructor that will do that for you:
    vb.net Code:
    1. Using adapter As New SqlDataAdapter("Select tenantregID, tenantfullname from tbltenants order by firstname",
    2.                                     "Data Source=DESKTOP-O6TSDMJ;Initial Catalog=RCMS;Integrated Security=True")
    3.     '...
    4. End Using

  8. #8
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Logic

    You're making a mess of something very simple. You should be binding one DataTable to the grid, another to the column and then saving the changes with a single call to Update on a data adapter. I suggest that you follow the VB CodeBank link in my signature below and check out my thread on ComboBox Column In DataGridView and see how it's done there.

  9. #9
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Logic

    With regards to saving the data, I suggest that you check out my CodeBank thread on Retrieving & Saving Data. It includes an example of saving data from a DataTable using a data adapter. That's what you should be doing, with the DataTable bound to the DataGridView via a BindingSource.

  10. #10

    Thread Starter
    Lively Member
    Join Date
    Jul 2021
    Posts
    71

    Re: Logic

    I do know where the problem resides, but don't know how to solve it:

    Code:
    For i As Integer = 0 To DgUnitsUp.Rows.Count - 1
                DgUnitsUp.Rows(i).Cells(8).Value = DgUnitsUp.Rows(i).Cells(7).Value
            Next
    Now, what I do is that on BtnEdit click, I switch the columns from textbox to combobox and populate the combobox with the tenantfullname from the tbltenants.
    I also match the tenantfullname from the textbox to the one on the dropdownlist of the combobox.

    Now, as a test I changed to this code:

    Code:
     Dim dt As New DataTable()
                    Using cmd As New SqlCommand("Select tenantregID, tenantfullname from tbltenants order by firstname", con)
                        Using adapter As New SqlDataAdapter(cmd)
                            adapter.Fill(dt)
                            With TenantName
                                .DisplayMember = "tenantregID"
                                .ValueMember = "tenantregID"
                                .DataSource = dt
                            End With
                        End Using
                    End Using
    Now at runtime, when I clicked the Edit button the textbox switch does happen, but instead of displaying the tenantregid - it still shows the tenantfullname. Clicking the dropdownlist the tenantfullname disappears and you can see the tenantregid.

    Therefore, if a user saves one of the rows and click 'Update' the second row even though I am thinking am saving the tenantregid - it is not, it is the tenantfullname that is what is displayed when the daagridview is loaded. So all the tenant name on the comboxbox on the datagridview have the tenantfullname eventhough I changed display to tenantregid.

    Hence the error message:

    Conversion failed when converting the nvarchar value 'John Smith' to data type int.

    Note: The first record since I set it to an int value (tenantregid) no problem and the error message was triggered by the second row as it has the tennatfullname and its trying to save that on the tenantregid field.


    Code:
     Private Sub UnitsDetails()
    
            Try
                Using con As New SqlConnection("Data Source=DESKTOP-O6TSDMJ;Initial Catalog=RCMS;Integrated Security=True")
                    DsUnitdetails.EnforceConstraints = False
                    Me.VwunitdetailsTableAdapter.Fill(Me.DsUnitdetails.vwunitdetails, TxtPropertyIDClick.Text)
    
                    con.Open()
    
                    Using cmd As New SqlCommand("Select flattype from lkupflattype", con)
                        Using myreader As SqlClient.SqlDataReader = cmd.ExecuteReader
                            FlatType.Items.Clear()
                            While myreader.Read
                                FlatType.Items.Add(myreader("FlatType"))
                            End While
                        End Using
                    End Using
    
                    con.Close()
    
                End Using
            Catch ex As Exception
                MessageBox.Show(ex.ToString())
            End Try
    
    
            Try
                Using con As New SqlConnection("Data Source=DESKTOP-O6TSDMJ;Initial Catalog=RCMS;Integrated Security=True")
                    DsUnitdetails.EnforceConstraints = False
                    Me.VwunitdetailsTableAdapter.Fill(Me.DsUnitdetails.vwunitdetails, TxtPropertyIDClick.Text)
    
                    con.Open()
    
                    Dim dt As New DataTable()
                    Using cmd As New SqlCommand("Select tenantregID, tenantfullname from tbltenants order by firstname", con)
                        Using adapter As New SqlDataAdapter(cmd)
                            adapter.Fill(dt)
                            With TenantName
                                .DisplayMember = "tenantregID"  'this was changed to visualise what is truly happening on the 'Tenant Name' field.
                                .ValueMember = "tenantregID"
                                .DataSource = dt
                            End With
                        End Using
                    End Using
    
                    con.Close()
                End Using
    
            Catch ex As Exception
                MessageBox.Show(ex.ToString())
            End Try
    
            For i As Integer = 0 To DgUnitsUp.Rows.Count - 1
                DgUnitsUp.Rows(i).Cells(6).Value = DgUnitsUp.Rows(i).Cells(5).Value
                DgUnitsUp.Rows(i).Cells(8).Value = DgUnitsUp.Rows(i).Cells(7).Value
            Next
    
    
            'set Flattype combobox to Visible
            DgUnitsUp.Columns(5).Visible = True
            DgUnitsUp.Columns(6).Visible = False
    
            'set Tenantname combobox to Visible
            DgUnitsUp.Columns(7).Visible = True
            DgUnitsUp.Columns(8).Visible = False
    
    
        End Sub

    Code:
      Private Sub DgPropDet_CellMouseClick(sender As System.Object, e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DgPropDet.CellMouseClick
    
                Call UnitsDetails()
    
                DgUnitsUp.ClearSelection()
    
            End If
    
        End Sub
    Thanks
    Last edited by dr225; Sep 17th, 2021 at 06:32 AM.

  11. #11
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Logic

    Quote Originally Posted by dr225 View Post
    I do know where the problem resides, but don't know how to solve it:

    Code:
    For i As Integer = 0 To DgUnitsUp.Rows.Count - 1
                DgUnitsUp.Rows(i).Cells(8).Value = DgUnitsUp.Rows(i).Cells(7).Value
            Next
    Now, what I do is that on BtnEdit click, I switch the columns from textbox to combobox and populate the combobox with the tenantfullname from the tbltenants.
    I also match the tenantfullname from the textbox to the one on the dropdownlist of the combobox.

    Now, as a test I changed to this code:

    Code:
     Dim dt As New DataTable()
                    Using cmd As New SqlCommand("Select tenantregID, tenantfullname from tbltenants order by firstname", con)
                        Using adapter As New SqlDataAdapter(cmd)
                            adapter.Fill(dt)
                            With TenantName
                                .DisplayMember = "tenantregID"
                                .ValueMember = "tenantregID"
                                .DataSource = dt
                            End With
                        End Using
                    End Using
    Now at runtime, when I clicked the Edit button the textbox switch does happen, but instead of displaying the tenantregid - it still shows the tenantfullname. Clicking the dropdownlist the tenantfullname disappears and you can see the tenantregid.

    Therefore, if a user saves one of the rows and click 'Update' the second row even though I am thinking am saving the tenantregid - it is not, it is the tenantfullname.

    Hence the error message:

    Conversion failed when converting the nvarchar value 'John Smith' to data type int.

    Note: The first record since I set it to an int value (tenantregid) no problem and the error message was triggered by the second row as it has the tennatfullname and its trying to save that on the tenantregid field.


    Code:
     Private Sub UnitsDetails()
    
            Try
                Using con As New SqlConnection("Data Source=DESKTOP-O6TSDMJ;Initial Catalog=RCMS;Integrated Security=True")
                    DsUnitdetails.EnforceConstraints = False
                    Me.VwunitdetailsTableAdapter.Fill(Me.DsUnitdetails.vwunitdetails, TxtPropertyIDClick.Text)
    
                    con.Open()
    
                    Using cmd As New SqlCommand("Select flattype from lkupflattype", con)
                        Using myreader As SqlClient.SqlDataReader = cmd.ExecuteReader
                            FlatType.Items.Clear()
                            While myreader.Read
                                FlatType.Items.Add(myreader("FlatType"))
                            End While
                        End Using
                    End Using
    
                    con.Close()
    
                End Using
            Catch ex As Exception
                MessageBox.Show(ex.ToString())
            End Try
    
    
            Try
                Using con As New SqlConnection("Data Source=DESKTOP-O6TSDMJ;Initial Catalog=RCMS;Integrated Security=True")
                    DsUnitdetails.EnforceConstraints = False
                    Me.VwunitdetailsTableAdapter.Fill(Me.DsUnitdetails.vwunitdetails, TxtPropertyIDClick.Text)
    
                    con.Open()
    
                    Dim dt As New DataTable()
                    Using cmd As New SqlCommand("Select tenantregID, tenantfullname from tbltenants order by firstname", con)
                        Using adapter As New SqlDataAdapter(cmd)
                            adapter.Fill(dt)
                            With TenantName
                                .DisplayMember = "tenantregID"  'this was changed to visualise what is truly happening on the 'Tenant Name' field.
                                .ValueMember = "tenantregID"
                                .DataSource = dt
                            End With
                        End Using
                    End Using
    
                    con.Close()
                End Using
    
            Catch ex As Exception
                MessageBox.Show(ex.ToString())
            End Try
    
            For i As Integer = 0 To DgUnitsUp.Rows.Count - 1
                DgUnitsUp.Rows(i).Cells(6).Value = DgUnitsUp.Rows(i).Cells(5).Value
                DgUnitsUp.Rows(i).Cells(8).Value = DgUnitsUp.Rows(i).Cells(7).Value
            Next
    
    
            'set Flattype combobox to Visible
            DgUnitsUp.Columns(5).Visible = True
            DgUnitsUp.Columns(6).Visible = False
    
            'set Tenantname combobox to Visible
            DgUnitsUp.Columns(7).Visible = True
            DgUnitsUp.Columns(8).Visible = False
    
    
        End Sub

    Code:
      Private Sub DgPropDet_CellMouseClick(sender As System.Object, e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DgPropDet.CellMouseClick
    
                Call UnitsDetails()
    
                DgUnitsUp.ClearSelection()
    
            End If
    
        End Sub
    Thanks
    That is all garbage. Have you checked out my CodeBank thread? If not, why not? If so, why are you doing that garbage instead of what I demonstrated there? You're taking something simple and making it hard for no good reason. The logic is very simple:

    1. Populate a parent DataTable with an ID column and a display column.
    2. Bind that table to the combo box column with the ID as the ValueMember and the display as the DisplayMember.
    3. Populate a child DataTable with a foreign key column.
    4. Bind that table to the grid with the foreign key bound to the combo box column.

    That's it, that's all. The ID values will be stored in the grid and thus saved to the database while the display value is what the user sees. Stop trying to make it complex. You then use a data adapter to save all the changes in the child DataTable with a single call to Update.

  12. #12

    Thread Starter
    Lively Member
    Join Date
    Jul 2021
    Posts
    71

    Re: Logic

    That is all garbage. Be Kind my friend...

  13. #13
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Logic

    Quote Originally Posted by dr225 View Post
    That is all garbage. Be Kind my friend...
    I could have been much harsher. I've directed you to a simple example of doing it the right way so I'm not sure why you're posting something that is significantly different from that. If you didn't know how to do it in the first place, that's one thing. I've written garbage code in the past too. We all have. When you're shown the proper way to do it though, the idea is that you ditch the garbage and do it the right way.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width