-
Sep 16th, 2021, 10:57 AM
#1
Thread Starter
Lively Member
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.
-
Sep 16th, 2021, 11:07 AM
#2
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)
-
Sep 16th, 2021, 11:21 AM
#3
Thread Starter
Lively Member
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'
-
Sep 16th, 2021, 11:47 AM
#4
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.
-
Sep 16th, 2021, 12:14 PM
#5
Thread Starter
Lively Member
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
-
Sep 16th, 2021, 01:18 PM
#6
Re: Logic
Are you overwriting the ComboBox item's with the SqlDataReader code?
-
Sep 16th, 2021, 07:54 PM
#7
Re: Logic
Originally Posted by dday9
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:
Using con As New SqlConnection("Data Source=DESKTOP-O6TSDMJ;Initial Catalog=RCMS;Integrated Security=True"),
cmd As New SqlCommand("Select tenantregID, tenantfullname from tbltenants order by firstname", con),
adapter As New SqlDataAdapter(cmd)
'...
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:
Using adapter As New SqlDataAdapter("Select tenantregID, tenantfullname from tbltenants order by firstname",
"Data Source=DESKTOP-O6TSDMJ;Initial Catalog=RCMS;Integrated Security=True")
'...
End Using
-
Sep 16th, 2021, 07:59 PM
#8
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.
-
Sep 16th, 2021, 09:15 PM
#9
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.
-
Sep 17th, 2021, 06:06 AM
#10
Thread Starter
Lively Member
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.
-
Sep 17th, 2021, 06:36 AM
#11
Re: Logic
Originally Posted by dr225
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.
-
Sep 17th, 2021, 07:00 AM
#12
Thread Starter
Lively Member
Re: Logic
That is all garbage. Be Kind my friend...
-
Sep 17th, 2021, 09:44 AM
#13
Re: Logic
Originally Posted by dr225
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|