guidance on use of combobox with sql database

Mar 26, 2009 at 10:48 PM
I am reading a table from sql server express into a grid.  The table has foreign key lookup columns to a separate table with unique ids (e.g. table Color, 1 = red, 2 = blue, 3=green).  So my "car" table actually stores, in column CarColor, values 1, 2, 3.  I want the user to be able to see, in the data grid for each cell in that column, a combobox or listbox with values red, blue, green.  But I need to write back the changed value of the id, e.g. if changed from red to blue by user, need to write back to the table the value 2.   [Car example is just by way of illustration, but general concept in actual app is foreign keys to lookup tables].

I have adapted a nice general solution from the example code in the absence of lookup tables, takes a query on a sql server database, places it into a grid, allows for changes, easy to commit changes back to the data base, works very nicely in my prototype UI, but requires the user to know the needed numeric value of the lookup field (e.g. need to know that blue is 2, enter 2 into the grid).  I would like to take this to the next level of usability with the combobox capability,  I am looking for guidance/suggestions as to the best way to: a) populate the data grid combobox from the lookup table; b) write back the id associated with the combobox item.  I would like to make this as general as possible, e.g. minimize the degree of custom coding that requires knowledge of the table structures.

hope this is adequately clear.  MS Access did this for you automatically, allowing you to define a lookup query for a given field, basically I am looking for a similar capability.

thanks in advance.  btw, many thanks for providing sourcegrid to the community, excellent tool.

Richard Males
Cincinnati, Ohio, USA
Coordinator
Mar 28, 2009 at 9:53 PM
a) I haven't looked exactly at the code how to do this, but this should not be any different than how you usaully work with comboboxes.
 First - get a combobox, then fill it with data, finally - specifify the name of the property, which should be displayed for the user.


May 20, 2009 at 9:41 PM

thank you, I have been away from this for a while, but am now back on it.  Is there anywhere a source code example of how to assign such a combobox editor to all of the rows in a particular column of a datagrid?

 

Thanks In Advance

Richard Males

Cincinnati, Ohio, USA

Coordinator
May 25, 2009 at 8:08 AM

theoretically, it is designed that editors should be shared between all cells.   Practically i have not tested this, but it should work.

May 25, 2009 at 5:40 PM

Thank you.  I have been looking at code in frmSample41.cs, trying to add columns to my datagrid after binding to a datasource.  This works ok, but the colum entries for each row for each added column are:

"Error: object reference not set to an instance of an object".

I am assuming that I need to fill each column/row in the datagrid, but am not clear how to do that after I have set the datasource.  I am sure I am missing something simle.

 

My code is as follows, works fine, the columns/rows populated from the bound data view (table in SQL Server Express) are fine, editable.

Any help much appreciated, I am really anxious to get this capability working, and I hope that learning how to use the combobox editor on an added column will ultimately get me where I want to go (which is dealing with foreign keys in my tables).

Thanks again.

Dick Males

Cincinnati, Ohio, USA

 private void btnGo_Click(object sender, EventArgs e)
        {
            try
            {

                SourceGrid.Cells.Views.Cell captionModel = new SourceGrid.Cells.Views.Cell();
                captionModel.BackColor = dataGrid1.BackColor;
               
                System.Data.DataSet dataset = new System.Data.DataSet();

                using (System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(txtSQLQuery.Text, sConnectionString))
                {
                    adapter.Fill(dataset);
                }

                dataGrid1.FixedColumns = 1;
                dataGrid1.FixedRows = 1;
                dataGrid1.Columns.Clear();

                DevAge.ComponentModel.BoundDataView bd = new DevAge.ComponentModel.BoundDataView(dataset.Tables[0].DefaultView);
              
                SourceGrid.DataGridColumn gridColumn;

                SourceGrid.Cells.Editors.ComboBox externalIdEditor = new SourceGrid.Cells.Editors.ComboBox(typeof(int));
                externalIdEditor.StandardValues = new int[] { 1, 2, 3, 4 };
                externalIdEditor.StandardValuesExclusive = true;
                externalIdEditor.Control.FormattingEnabled = true;

                DevAge.ComponentModel.Validator.ValueMapping mapping = new DevAge.ComponentModel.Validator.ValueMapping();
                mapping.ValueList = new int[] { 1, 2, 3, 4 };
                mapping.DisplayStringList = new string[] { "Reference 1", "Reference 2", "Reference 3", "Reference 4" };
                mapping.BindValidator(externalIdEditor);

                SourceGrid.Cells.Editors.TextBoxNumeric numericEditor = new SourceGrid.Cells.Editors.TextBoxNumeric(typeof(decimal));
                numericEditor.TypeConverter = new DevAge.ComponentModel.Converter.NumberTypeConverter(typeof(decimal), "N");
                numericEditor.AllowNull = true;  //the database value can be null (System.DbNull)


                dataGrid1.DataSource = bd;

                //Create a custom View class
                SourceGrid.Cells.Views.Cell view = new SourceGrid.Cells.Views.Cell();
                view.BackColor = Color.LightBlue;
                //Manually add the column
               
                gridColumn = dataGrid1.Columns.Add("Country", "Country", typeof(string));
          
                gridColumn.DataCell.View = view;
               
                               
                // add the combo box

                gridColumn = dataGrid1.Columns.Add("ExternalID", "ExternalID", externalIdEditor);

                // and a numeric editor

                gridColumn = dataGrid1.Columns.Add("Population", "Population", numericEditor);
               
                dataGrid1.Columns[0].AutoSizeMode = SourceGrid.AutoSizeMode.MinimumSize;
                dataGrid1.Columns[0].Width = 20;

                dataGrid1.Columns.AutoSizeView();

            }
            catch (Exception err)
            {
                DevAge.Windows.Forms.ErrorDialog.Show(this, err, "Error loading dataset");
            }

          
                       
        }

Coordinator
May 26, 2009 at 10:06 AM

I would go with creating columns before setting the data source. Do you really need to do this the opposite way?

Jul 15, 2009 at 10:47 PM

Add New problem with combobox.  I am having a problem adding a new record to a datagrid when combobox editors are assigned to the grid.

I have resolved the general problem of using the combobox with the datagrid, but need to solve this problem of adding a new row without violating the combobox lookup.

I developed a simple test sql server database with two lookup tables: color (e.g. red, green, yellow) and shape (e.g. square, oval, ellipse) and a master table that just has an ID key (bigint) and two foreign keys to the color and shape tables, each stored as a bigint (c# long).  I can read and populate the combobox values based on the contents of the lookup table, and display the datagrid with the appropriate combobox editor assigned to the color and shape columns, respectively.  Thus, each row shows the id key, the color text, and the shape text, e.g.

1  red  square

2 green oval

The problem comes in when I attempt to add a new row.  The blank bottom row of the grid, where the row is to be added, gives the following message for the two grid cells that are populated by comboboxes: 

Error: cannot convert <null> to display String.

If I click in the grid cell, I get a conversion error exception was unhandled message in debug mode.

I am looking for behavior similar to that shown in frmSample49, where double-clicking on the bottom row enters a new row with default data that can then be modified.  I can assign a default value to each cell before the row is added, such that the combobox will not fail, if somebody can tell me how to do that.

I should also note that I am heading towards a general solution such that I can pass the needed sql code for each combobox editor to my grid form object, to have it do the lookups and populate the combobox from the lookup tables, and also to pass the column of the grid to which the editor has been assigned, so that I can more easily code datagrids for multiple tables that use different foreign keys/lookups.

Thanks in advance, sorry if this is not adequately clear.  Code for using the comboboxes is below:

//////////

SourceGrid.Cells.Views.Cell captionModel = new SourceGrid.Cells.Views.Cell();
                captionModel.BackColor = dataGrid1.BackColor;
                
                dataset = new System.Data.DataSet();

                adapter = new System.Data.SqlClient.SqlDataAdapter(txtSQLQuery.Text, sConnectionString);
                SqlCommandBuilder commandBuilder = new SqlCommandBuilder(adapter);

                adapter.Fill(dataset);
                
                dataGrid1.FixedColumns = 1;
                dataGrid1.FixedRows = 1;
                dataGrid1.Columns.Clear();

                 bd = new DevAge.ComponentModel.BoundDataView(dataset.Tables[0].DefaultView);

                dataGrid1.DataSource = bd;
                bd.AllowNew = true;
                bd.AllowEdit = true;
                bd.AllowDelete = true;
                
                               
                SourceGrid.DataGridColumn gridColumn;

                // construct the array of combobox editors

                SourceGrid.Cells.Editors.ComboBox[] editorArray = new SourceGrid.Cells.Editors.ComboBox[2];
                     
                editorArray[0] = new SourceGrid.Cells.Editors.ComboBox(typeof(long));// editor;
                editorArray[1] = new SourceGrid.Cells.Editors.ComboBox(typeof(long));// editor;
                
                // info for populating the two combo boxes

                string[] lookupSQL = new string[2] { "SELECT [ColorID],[ColorDescription] from Color", "SELECT [ShapeID],[Shape] from ShapeLookup" };
                int[] columnIndex = new int[2] { 2, 3 };

                // populate the combo box editors

                for (int lIndex = 0; lIndex < 2; lIndex++)
                {
                    int colIndex = columnIndex[lIndex];
                    string sql = lookupSQL[lIndex];

                    System.Data.DataSet datasetLookup = new System.Data.DataSet();
                    System.Data.SqlClient.SqlDataAdapter adapterLookup = new System.Data.SqlClient.SqlDataAdapter(sql, sConnectionString);
                    adapterLookup.Fill(datasetLookup);

                    int dsLength = datasetLookup.Tables[0].Rows.Count;

                    long[] lookupID = new long[dsLength];
                    string[] stringValue = new string[dsLength];

                    int index = 0;
                    foreach (DataRow dr in datasetLookup.Tables[0].Rows)
                    {
                        lookupID[index] = (long)dr[0];
                        string tempString = (string)dr[1];
                        stringValue[index] = tempString.Trim();
                        index++;
                    }
                    
                    editorArray[lIndex].StandardValues = lookupID;
                    editorArray[lIndex].StandardValuesExclusive = true;
                    editorArray[lIndex].Control.FormattingEnabled = true;

                    DevAge.ComponentModel.Validator.ValueMapping mapping = new DevAge.ComponentModel.Validator.ValueMapping();
                    mapping.ValueList = lookupID;
                    mapping.DisplayStringList = stringValue;
                    mapping.BindValidator(editorArray[lIndex]);

                    // assign editor to appropriate column

                    dataGrid1.Columns[colIndex].DataCell.Editor = editorArray[lIndex];
                }



                dataGrid1.Columns[0].AutoSizeMode = SourceGrid.AutoSizeMode.EnableAutoSize;
                dataGrid1.Columns[0].Width = 20;
                dataGrid1.Columns.AutoSizeView();

//////////

 

Richard Males

Cincinnati, OH, USA

 

Dec 22, 2011 at 6:03 PM

Richard,

Did you ever get the add new row working?

I've been able to make your example work. I had to change Long to Integer in my case (datatype of returned database ID column has to match datatype of lookup array; otherwise, conversion error.)

But the new row still gives the error and putting null in the lookupID array is not allowed.

I'd rather not modify dariusdamalakas's code.