DataGridView - adding values to drop-down list cell? 1

[CraigBest]

Hope someone can help, this is really kicking my butt.

I have a DataGridView I'm displaying in my VB.NET 2008 application. I'm not having any problem displaying data from the datatable being used as a datasource, but I do have a column in the grid that is supposed to be a drop-down listbox allowing the user to select one of several options that affect the selected row later on.

I have the DataGridView column set to DataGridViewComboBoxColumn, and I can see at design time there is a way to preset a number of list values for the column. Unfortunately for me I do not know ahead of time what the values ought to be, I need to assign them during execution from another datatable (string values).

For the life of me I cannot figure out how to do this, I've been looking all day at examples on the web and I'm more confused now than when I started. Can anyone provide me with a clear example of how to do this? Thank you if you can.

Craig

CraigHartz

 

[ousoonerjoe]

You need to create an object that references the column to load with values. Then just as you would a normal ComboBox, set the DataSource equal to the DataTable, the DisplayMember and ValueMembers accordingly. After the DataGridViewComboBoxColumn is loaded, then you can load your datagrid.

			Dim Colm As DataGridViewComboBoxColumn = dgDetail.Columns("ColumnName")
			Colm.DataSource = dTable
			Colm.DisplayMember = "DisplayValue"
			Colm.ValueMember = "LookupValue"

--------------------------------------------------
Stubbornness is a virtue -- if you are right. --Chuck Noll
--------------------------------------------------

 

[CraigBest]

Thanks SoonerJoe - that was really straightforward and simple to understand, and it looks like it works perfectly. Couldn't fins anything that clear in hours of casting across the net.

I have one other issue which is still stymieing me - when I run the program it seems like everything is loading as it should, but when I click on the down arrows in the Combobox Cells nothing happens. I checked to make sure the grid was not read only, and it's not - I don't understand why the clicks are ignored. any idea what might cause that?

I've primarily used these DataGridViews as read-only in the past so this is a new experience for me!

Thanks

Craig

CraigHartz

 

[ousoonerjoe]

There is a lot to cover regarding the grids. I'm still learning and dealing with some of the little nuances of them as you can see here ( thread796-1627680 ). From what I've found, so far, the Colm.ValueMember MUST be a String data type.

As for the column not responding to clicks, by chance is the Column or Row set to ReadOnly? Any grid click events that may be intercepting the action? Is the grid set to Allow Edits? Can you type in the column and the options change accordingly? What is your "EditMode" set to? Can you change the values in the TextBoxColumns?

--------------------------------------------------
Stubbornness is a virtue -- if you are right. --Chuck Noll
--------------------------------------------------

 

[CraigBest]

OK SoonerJoe, thanks. I dug a bit more and setting the columns in the grid to readonly = false now allows them to be clicked and upen up - but there's no data.

I looked further and realized that while I have defined the DataGridViewComboBoxCoulmns and anm able to load them with data, I can't seem to figure out hoe to assign these objects to the columns in the datagridview. Your original example where you did it when you defined the object doesn't work for me, says the object (I'm assuming it's the DataGridView on the form) doesn't exist. So I tried doing the assignment before and after loading the combobox coluns and it won't seem to let me do that. I'm confused.

Craig

CraigHartz

 

[CraigBest]

Yeah so here's the thing - If I don't use the New keyword when defining the object I can do the assignment, but when I go to run the program I get an error stating that the DGVComboBoxColumn is not a valid object. If I do use the new keyword it creates the object, but won't allow me to do the assignment to the DataGridView. ???

CraigHartz

 

[ousoonerjoe]

Did you set the DataPropertyName = to the name of the column from the datatable? Key question: Are you defining the columns in the designer or are you letting the grid create the column layouts?

Colm.DataPropertyName = "FieldName"

--------------------------------------------------
Stubbornness is a virtue -- if you are right. --Chuck Noll
--------------------------------------------------

 

[CraigBest]

Hi SoonerJoe

OK Well I finally figured out a solution from one of the how-tos on the net. I created the DGVComboBoxColumns just as we discussed, but instead of trying to assign them to the dolumn of the DataGridView, I used the Insert method of the DataViewGrid to force them into it. At least THAT worked!

I did try setting the DataPropertyName, it didn't seen to make any difference. The columns are pre-defined because I'm not using any data controls on the project (every time I try to do that I somehow end up corrupting my work and lose days of effort, so I don't do that anymore). I create a DataTable and populate if from a SQL statement in a DataAdapter directly.

I'll continue to look at this, there must be a way to make it work!

Craig

CraigHartz

 

[ousoonerjoe]

I never use data controls. All data connections are handled programatically.

Regarding the DataGridView:

Here's the methodology I use to create and configure a DGV. Not by any means is this a "perfect approach", but has worked well for what we do here:

Place Grid on form.
Using the designer, declare all columns and column type (combo, check, textbox, etc) that will be needed.
Define any properties (default values, formatting, etc.).
Define the DataPropertyName of each column as it directly relates to the DataTable that will populate it.

In code, write function that will populate specific ComboBoxColumns (PopPayFlag in example below).
Write functions to populate other fields as needed (Running Totals, etc.)
Write load calls.

Here are a few examples from how I code it:

[COLOR=green]'Populates the PayFlag DataGridViewComboBoxColumn named dcPayFlag.[/color]

	Private Sub PopPayFlag(ByVal Colm As DataGridViewComboBoxColumn)
		Dim Cnn As SqlClient.SqlConnection = Nothing
		Dim Cmd As SqlClient.SqlCommand = Nothing
		Dim Rst As SqlClient.SqlDataAdapter = Nothing
		Dim dSet As DataSet = Nothing
		Try
			Cnn = New SqlClient.SqlConnection(ConnString)
			If Cmd Is Nothing Then Cmd = New SqlClient.SqlCommand
			Cmd.Connection = Cnn
			Cmd.CommandType = CommandType.StoredProcedure
			Cmd.CommandText = "cst_PayFlags_Lookup"
			Cmd.Parameters.AddWithValue("@Active", 1)
			If Cnn.State <> ConnectionState.Open Then Cnn.Open()
			Rst = New SqlClient.SqlDataAdapter(Cmd)
			dSet = New DataSet
			Rst.Fill(dSet)
			Colm.DataSource = dSet.Tables(0)
			Colm.DisplayMember = "Display"
			Colm.ValueMember = "Display"
		Catch Ex As Exception
			Ex.Source = System.Reflection.MethodBase.GetCurrentMethod.Name
			ErrorLog(Err.Number, Err.Description, Me.Name, Ex.Source, Err.Erl)
			MessageBox.Show(Me, Err.Number & " :  " & Ex.Message & vbCrLf & vbCrLf & _
			  "An error here is typically due to connectivity. Check with I.T. to see " & _
			  "if there are any issues. Does your Internet work?", "Error in " & Err.Source, _
			  MessageBoxButtons.OK, MessageBoxIcon.Error)
		Finally
			dSet = Nothing
			Rst = Nothing
			Cmd = Nothing
			If Cnn.State <> ConnectionState.Closed Then Cnn.Close()
			Cnn = Nothing
		End Try
	End Sub

[COLOR=green]'the following is from the cmdLoadGrid_Click().[/color]
		Dim InqDs As DataSet = Nothing
					dgDetail.AutoGenerateColumns = False
					dgDetail.Enabled = True
					PopPayFlag(dcPayFlag)
					If InqDs Is Nothing Then
						StatBarEllipse(txtMsg, "No detail records were located. Verify your search critiria and try again.", True)
						Exit Try
					Else
						StatBarEllipse(txtMsg, "Located the header information... looking for the details....", False)
						dgDetail.DataSource = InqDs.Tables(1)
						RunningBalance(dgDetail, dcAmount, dcBalance)
						dgDetail.CurrentCell = dgDetail.Item(dcPayIt.Index, 0)
						StatBarEllipse(txtMsg, "Loaded... any payments to calculate?", False)
					End If
					FormatGridDetails()

[COLOR=green]'From a data class. polls database and loads the DGV or an Error Object if fails.[/color]
    Public Function GridSets(ByVal DtGrid As DataGridView) As Boolean
		If Cnn Is Nothing Then Cnn = New SqlClient.SqlConnection(CnnStr)
        If Cnn.State <> ConnectionState.Open Then Cnn.Open()
        If Cmd Is Nothing Then Cmd = New SqlClient.SqlCommand(CmdStr)
        If Rst Is Nothing Then Rst = New SqlClient.SqlDataAdapter(Cmd)
        If dList Is Nothing Then dList = New DataSet
        Try
            Rst.SelectCommand.Connection = Cnn
            Rst.SelectCommand = Cmd
            Rst.SelectCommand.CommandType = CmdType
            Rst.SelectCommand.CommandText = CmdStr
            Rst.Fill(dSet)
            DtGrid.DataSource = dSet.Tables(0)
			GridSets = True
        Catch ex As Exception
            Errs.Description = ex.Message
            Errs.Number = Err.Number
            Errs.Source = Err.Source & ".GridSets"
            Errs.Line = Err.Erl
			GridSets = False
		Finally
			Rst = Nothing
			dSet = Nothing
			If Cnn.State <> ConnectionState.Closed Then Cnn.Close()
			If Not Cnn Is Nothing Then Cnn = Nothing
		End Try
	End Function

There's a hundred different ways to do it, but this has worked the best for what I do. I hope this helps understand it a little better. I'll gladly answer any questions you still have about it.

--------------------------------------------------
Stubbornness is a virtue -- if you are right. --Chuck Noll
--------------------------------------------------

 

[CraigBest]

Thanks Joe - I'll have a close look at it when I get a chance!

CraigHartz