|
Subject |
Author |
Date |
|
|
Paddy
|
Jan 30, 2011 - 11:35 PM
|
Hi all,
I am still evaluating the Grid v3.8 and I am still not getting along with the control at all. I am not able to find any code samples for how to work with it.
Pretty much, I am working with three tables in a database that have an FK relationship. I am not using any designers. I prefer to code by hand. I have my code setup where I am creating one DataSet and then creating two DataTables within the DataSet for the information I want based on a FK. I also have a DataRelation set up for the DataTables due to the FK relationship within the database.
I know this works because the way I have the form setup, if an end-user types in a client name, if there is only one phone number for the client, the other controls are auto-populated with the information. This works without issue. If more than one phone number exist, a form comes up asking them to choose the phone number and phone number type from the BoundGrid control. This was coming up without issue prior to trying to code to get the BoundGrid to populate the information. This is where I am having the issue. I am trying to populate the BoundGrid from the DataSet/DataTables/DataRelation code I have going on. The issue I am having is that as soon as the form loads that holds the BoundGrid control, I am getting an OutOfMemoryException and then my app crashes.
The following is what I have done and what I have tried to do to get the BoundGrid to populate the information. Please note that the code to populate the BoundGrid is toward the bottom of the Sub.
|
|
|
Technical Support
|
Feb 2, 2011 - 5:31 AM
|
We asked to you send us a test project so that we can help you more efficiently. Could you do that? Thank you.
|
|
|
Paddy
|
Jan 30, 2011 - 11:37 PM
|
<CLSCompliant(True)> Private Sub cboClientName_LostFocus(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles CboClientName.LostFocus
’Only run the following code if there is data present/selected/typed in the ComboBox.
With CboClientName
If .Text.Trim.Length > 0 _
AndAlso .Text.Trim IsNot Nothing Then
’Check the backend database to see if the client name exists.
’If the client name exists, check to see how many phone numbers the client has.
’If there is only one phone number, populate the MaskedTextBox and ComboBox.
’If there are multiple phone numbers, show a form for the end user to choose the phone number
’they want.
Using SetDatabaseConnection As SqlConnection = New SqlConnection(GetDatabaseConnectionString)
Try
’Open the connection to the database.
SetDatabaseConnection.Open()
’Create a DataSet.
Dim ClientInfoDS As DataSet = New DataSet
’Set the locale of the DataSet.
ClientInfoDS.Locale = CurrentCulture
’Create two DataTables.
Dim ClientPhoneDT As DataTable = New DataTable("ClientInformation_PhoneNumbers")
Dim ClientPhoneTypeDT As DataTable = New DataTable("ClientInformation_PhoneNumberType")
’Set the locale for the DataTables.
ClientPhoneDT.Locale = CurrentCulture
ClientPhoneTypeDT.Locale = CurrentCulture
’Create the columns for the DataTables.
With ClientPhoneDT
.Columns.Add(New DataColumn("PhoneNumberID_PK", GetType(Integer)))
.Columns.Add(New DataColumn("ClientPhoneNumber", GetType(String)))
.Columns.Add(New DataColumn("PhoneNumberTypeID_PKFK", GetType(Integer)))
.Columns.Add(New DataColumn("ClientInfoID_PKFK", GetType(Integer)))
’Set the primary key column.
Dim PrimaryKey(1) As DataColumn
PrimaryKey(1) = .Columns("PhoneNumberID_PK")
.PrimaryKey = PrimaryKey
End With
With ClientPhoneTypeDT
.Columns.Add(New DataColumn("PhoneNumberTypeID_PK", GetType(Integer)))
.Columns.Add(New DataColumn("PhoneNumberTypeName", GetType(String)))
’Set the primary key column.
Dim PrimaryKey(1) As DataColumn
PrimaryKey(1) = .Columns("PhoneNumberTypeID_PK")
.PrimaryKey = PrimaryKey
End With
’Add the DataTables to the DataSet.
With ClientInfoDS
.Tables.Add(ClientPhoneDT)
.Tables.Add(ClientPhoneTypeDT)
End With
’Create a DataRelation for the two tables.
Dim ClientInfoDRe As DataRelation
’Create a new SQLCommand.
Dim MySQLCommand As SqlCommand = New SqlCommand
’Set the attributes for the SQLCommand.
With MySQLCommand
.Connection = SetDatabaseConnection
.CommandType = CommandType.StoredProcedure
.CommandText = "sp_GetPhoneInfoByName"
.Parameters.AddWithValue("@ClientName", CboClientName.Text.Trim.ToString)
End With
’Create a DataAdapter.
Dim ClientInfoDA As SqlDataAdapter = New SqlDataAdapter(MySQLCommand)
’Clear the DataSet prior to doing anything.
ClientInfoDS.Clear()
’Fill the DataTables with the information found.
With ClientInfoDA
.Fill(ClientPhoneDT)
.Fill(ClientPhoneTypeDT)
End With
’Create two DataColumns for the DataRelation relationship.
Dim ParentTableDC, ChildTableDC As DataColumn
With ClientInfoDS
’Create the DataRelation between the PK/FK columns.
ParentTableDC = .Tables("ClientInformation_PhoneNumberType").Columns("PhoneNumberTypeID_PK")
ChildTableDC = .Tables("ClientInformation_PhoneNumbers").Columns("PhoneNumberTypeID_PKFK")
’Create a new DataRelation.
ClientInfoDRe = New DataRelation("Phone Numbers", ParentTableDC, ChildTableDC)
’Add the DataRelation to the DataSet housing the two DataTables.
.Relations.Add(ClientInfoDRe)
End With
’Create two DataRows. One for the parent and another for the child.
Dim ParentTableDR, ChildTableDR As DataRow
’Now the fun begins, let’s go ahead and get the information from the DataTables.
’If there is one phone number, auto populate the MaskedTextBox with the phone number and ComboBox with the phone number type.
’If there are multiple phone numbers, show a form to allow the end user to select the desired phone number.
’If there are no records returned, focus the MaskedTextBox.
For Each ParentTableDR In ClientInfoDS.Tables("ClientInformation_PhoneNumberType").Rows ’PK.
For Each ChildTableDR In ParentTableDR.GetChildRows(ClientInfoDRe) ’FK.
’Now see how many records were returned.
Select Case ClientInfoDS.Tables("ClientInformation_PhoneNumberType").Rows.Count
Case 0 ’No records.
’No records returned, go ahead and focus the MaskedTextBox.
MtbClientPhoneNumber.Focus()
’Go no further because nothing returned.
Exit For
Case 1 ’One record.
’Yippee! A record was returned.
’Auto populate the the MaskedTextBox (Phone Number).
’Auto populate a default value in the ComboBox (Phone Number Type).
MtbClientPhoneNumber.Text = ChildTableDR("ClientPhoneNumber").ToString
CboPhoneNumberType.Text = ParentTableDR("PhoneNumberTypeName").ToString
’Focus the Partner Name ComboBox.
CboPartnerName.Focus()
Case Else ’Multiple records.
With FrmMultiPhoneSelect
’Add some text to the Form Caption Label.
.LblFormCaption.Text = "Please select a phone number from the list below that you would like to use for " & CboClientName.Text & "."
’Populate the control with all the phone numbers and phone number types.
With .GridPhoneNumbers
Dim ClientInfoBS As BindingSource = New BindingSource
With ClientInfoBS
.DataMember = "ClientInformation_PhoneNumbers" ’Child table.
.DataSource = ClientInfoDS ’DataSet that holds two DataTables and has a DataRelation.
End With
.DataSource = ClientInfoBS ’BindingSource.
’Phone Number.
.Columns(0).MappingDataSource = ParentTableDC
.Columns(0).MappingDisplayDataMember = ChildTableDR("ClientPhoneNumber").ToString
.Columns(0).MappingValueDataMember = ChildTableDR("ClientPhoneNumber").ToString
’Phone Number Type.
.Columns(1).MappingDataSource = ChildTableDC
.Columns(1).MappingDisplayDataMember = ParentTableDR("PhoneNumberTypeName").ToString
.Columns(1).MappingValueDataMember = ParentTableDR("PhoneNumberTypeName").ToString
End With
End With
’Show a form to the end user, so that s/he can select from a list of available phone numbers.
ShowFormInMDI(FrmMultiPhoneSelect, FrmMain)
End Select
Next ChildTableDR
Next ParentTableDR
’Close the database connection.
SetDatabaseConnection.Close()
Catch ex As SqlException
MessageBox.Show("There was an error getting data from the database. The error is:" & vbNewLine & _
ex.Message, _
"Error Retrieving Data.", _
MessageBoxButtons.OK, _
MessageBoxIcon.Information, _
MessageBoxDefaultButton.Button1, _
MessageBoxOptions.DefaultDesktopOnly, _
False)
End Try
End Using
End If
End With
End Sub
|
|
|
Paddy
|
Jan 30, 2011 - 11:46 PM
|
If I just do the following: With FrmMultiPhoneSelect
’Add some text to the Form Caption Label.
.LblFormCaption.Text = "Please select a phone number from the list below that you would like to use for " & CboClientName.Text & "."
’Populate the control with all the phone numbers and phone number types.
With .GridPhoneNumbers
.DataSource = ClientInfoDS
.DataMember = "ClientInformation_PhoneNumbers"
End With
End With
Then two rows are generated in the BoundGrid but no information shows at all. I am not getting any errors at this point when I try that. This particular test client has two phone numbers available. I will see if I can get the information to populate. FYI, the BoundGrid has two columns.
|
|
|
steph guillard
|
Dec 2, 2020 - 2:15 AM
|
|
|
|
|
Paddy
|
Jan 30, 2011 - 11:51 PM
|
OK, I am still not able to get the data to display. It just creates two rows with empty cells.
I have tried the following too and no go.
With FrmMultiPhoneSelect
’Add some text to the Form Caption Label.
.LblFormCaption.Text = "Please select a phone number from the list below that you would like to use for " & CboClientName.Text & "."
’Populate the control with all the phone numbers and phone number types.
With .GridPhoneNumbers
.DataSource = ClientInfoDS
.DataMember = "ClientInformation_PhoneNumbers"
.Columns(0).MappingDisplayDataMember = ChildTableDR("ClientPhoneNumber").ToString
.Columns(0).MappingValueDataMember = ChildTableDR("ClientPhoneNumber").ToString
.Columns(1).MappingDisplayDataMember = ParentTableDR("PhoneNumberTypeName").ToString
.Columns(1).MappingValueDataMember = ParentTableDR("PhoneNumberTypeName").ToString
End With
End With
|
|
|
Paddy
|
Jan 30, 2011 - 11:59 PM
|
If I try the following, I am told InvalidOperationException. Cannot bind to IListSource instance. With FrmMultiPhoneSelect
’Add some text to the Form Caption Label.
.LblFormCaption.Text = "Please select a phone number from the list below that you would like to use for " & CboClientName.Text & "."
’Populate the control with all the phone numbers and phone number types.
With .GridPhoneNumbers
’DataSet that holds two DataTables with a DataRelation.
.DataSource = ClientInfoDS
’Table that holds the PK in the PK/FK relationship.
.DataMember = "ClientInformation_PhoneNumberType"
’The columns within the DataTables for the columns in the BoundGrid.
.Columns(0).MappingDataSource = ClientInfoDS
.Columns(0).MappingDisplayDataMember = ChildTableDR("ClientPhoneNumber").ToString
.Columns(0).MappingValueDataMember = ChildTableDR("ClientPhoneNumber").ToString
.Columns(1).MappingDataSource = ClientInfoDS
.Columns(1).MappingDisplayDataMember = ParentTableDR("PhoneNumberTypeName").ToString
.Columns(1).MappingValueDataMember = ParentTableDR("PhoneNumberTypeName").ToString
End With
End With
|
|
|
Technical Support
|
Jan 31, 2011 - 5:25 AM
|
Could you send us a sample project that illustrates the issue?
|
|
|
Paddy
|
Jan 31, 2011 - 10:25 PM
|
With the code I have, am I even doing it right? I can more than likely do this with a DataSet but this DataSet has two DataTables with a DataRelation. I am unable to get this to work. Should be simple to populate this Grid. Other than that, might have to drop Grid and go with ListView instead. I’d rather not have to do that though.
|
|
|
Paddy
|
Jan 31, 2011 - 10:26 PM
|
In the ListView, there it an .Items.Add option, I don’t seem to find anything in your intellisence to be able to add the rows.
|
|
|
Paddy
|
Feb 1, 2011 - 4:59 AM
|
I have been working at this for weeks now and still not able to get it to work. Not about to pay $95 for a control that is useless to me. I have tried the following and it creates two rows which are completely blank (no data). What am I doing wrong?! With .GridPhoneNumbers
.DataSource = ClientInfoDS
.DataMember = ClientInfoDS.Tables(PhoneNumberTypeTable).ToString
’Phone Numbers.
.Columns(0).MappingDisplayDataMember = ChildTableDR(ClientPhoneNumberColumn).ToString
.Columns(0).MappingValueDataMember = ChildTableDR(ClientPhoneNumberColumn).ToString
’Phone Number Types.
.Columns(1).MappingDisplayDataMember = ParentTableDR(PhoneNumberTypeNameColumn).ToString
.Columns(1).MappingValueDataMember = ParentTableDR(PhoneNumberTypeNameColumn).ToString
End With
End With
|
|
|
Paddy
|
Feb 1, 2011 - 5:06 AM
|
Even tried the following just now and nothing. Just creates two blank rows of cells. Frustrating! For Each ParentDR In ClientInfoDS.Tables(PhoneNumberTypeTable).Rows ’PK.
For Each ChildDR In ParentTableDR.GetChildRows(ClientInfoDRe) ’FK.
’Add the phone numbers and the phone number types to the grid.
With FrmMultiPhoneSelect.GridPhoneNumbers
.DataSource = ClientInfoDS
.DataMember = ClientInfoDS.Tables(PhoneNumberTypeTable).ToString
’Phone Numbers.
.Columns(0).MappingDisplayDataMember = ChildDR(ClientPhoneNumberColumn).ToString
.Columns(0).MappingValueDataMember = ChildDR(ClientPhoneNumberColumn).ToString
’Phone Number Types.
.Columns(1).MappingDisplayDataMember = ParentDR(PhoneNumberTypeNameColumn).ToString
.Columns(1).MappingValueDataMember = ParentDR(PhoneNumberTypeNameColumn).ToString
End With
Next ChildDR
Next ParentDR
|
|
|
Technical Support
|
Feb 2, 2011 - 6:13 AM
|
Could you also check your email address? We can see that messages from this forum are not delivered to you.
|
|
|
Paddy
|
Feb 2, 2011 - 8:15 AM
|
I recently updated my email address and address the other day. All should be fine. I have tried so hard to get the control to display the data and its only displaying two rows and empty cells. I even checked the font color and its not a color whereby the text would be hidden. I am lost when it comes to working with the grid v3.8 please do help if you can.
|
|
|
Technical Support
|
Feb 2, 2011 - 8:49 AM
|
We asked you to create a simple test project and send it to us so that we can help you. Do you have any problems with that?
|
|
|
Paddy
|
Feb 2, 2011 - 8:59 AM
|
I don’t have a problem with that. I would also have to send a copy of my database as a BAK file along with it. This database has personal information in it but I am sure I could replace the records with dummy records for security reasons. Do you have access to an instance of SQL Server 2005?
|
|
|
Technical Support
|
Feb 2, 2011 - 9:03 AM
|
Your test project and db bak will be fine. We will restore the db on SQL Server 2005 and check what’s wrong.
|
|
|
Paddy
|
Feb 3, 2011 - 1:49 PM
|
The code I am using is being used on multiple forms, so I have it in a module. When I am pulling up that form to display a user with multiple phone numbers, I am not doing it in form load event. I have not tried it in the form load event. I would rather not duplicate the code. Almost 200 lines of code is being used for that particular situation. Where can I send the project and bak file? Would rather not post it on the forum.
|
|
|
Technical Support
|
Feb 4, 2011 - 2:58 AM
|
|
|