none
Relationship between controls on form and dataset RRS feed

  • Question

  • I am struggling through the MSDN docs and how-tos. I've barely used .NET and never have used all these data controls. However, I have a task to update the database with an app.

    Can someone verify my assumptions?
    1) A dataset holds a collection of records seperately from the database in memory
    2) Syncs between the data in the dataset and the database occur a table at a time when a table adapter's update method is called
    3) For multiple tables, a call to a table adapter manager's update will call individual table adapter update methods in an order you can specify (unclear on how to specify it)
    4) Joins happen automagically when I drag controls from more than one table onto my form
    5) If I want to view one record at a time on my form in one area, and all records in another place on my form, i'd have to create two seperate datasets


    Wednesday, November 4, 2009 3:33 PM

Answers

  • To answer your quesitons
    1) Yes, Collections of Objects
    2) Correct
    3) I prefer to code my update methods for more flexibility
    4) Yes if you have your relationship defined in the database, I prefer to do the join in codes
    5) Not really you can create a parameter then assign a value to the parameter

    Here is a sample of code that can get you started

    #Region "Visual Studio Namespace"
    Imports System
    Imports System.Data
    Imports System.Data.SqlClient
    Imports System.Text
    Imports System.IO
    #End Region
    
    
    
    Public Class Form2
    
        Public ds As DataSet
    
        Public dt1 As DataTable
        Public dt2 As DataTable
    
    
        Private Sub Form2_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    
            Try
                ds = New DataSet
                'Northwind Database Connection
                Using Conn As New SqlConnection(My.Settings.MyConnection)
                    'Get Orders Table
                    Using Adapter As New SqlDataAdapter("Select * from dbo.Orders", Conn)
                        Adapter.SelectCommand.CommandType = CommandType.Text
                        Adapter.Fill(ds, "Orders")
                        'Assign Datatable
                        dt1 = ds.Tables("Orders")
                    End Using
                    'Get Customers Table
                    Using Adapter As New SqlDataAdapter("Select * from dbo.Customers", Conn)
                        Adapter.SelectCommand.CommandType = CommandType.Text
                        Adapter.Fill(ds, "Customers")
                        'Assign Datatable
                        dt2 = ds.Tables("Customers")
                    End Using
    
                End Using
                'Design Schema after closing Connection
                'DataTable orders
                'Create PrimaryKey
                dt1.Constraints.Add("pkOrderID", dt1.Columns("OrderID"), True)
                'Allow Null Values
                dt1.Columns("ShippedDate").AllowDBNull = True
                'Set the AutoIncrement for the primary key Column 
                With dt1.Columns("OrderID")
                    .AutoIncrement = True
                    .AutoIncrementSeed = -1
                    .AutoIncrementStep = -1
                End With
    
                'DataTable Customers
                dt2.Constraints.Add("pkCustomerID", dt2.Columns("CustomerID"), True)
                'Allow null
                'With dt2.Columns("CustomerID")
                '    .AutoIncrement = True
                '    .AutoIncrementSeed = -1
                '    .AutoIncrementStep = -1
                'End With
    
                '******************************* DESIGN RELATIONSHIP *************************
    
                'RelationShip Between Customers and Orders
                With ds.Relations
                    .Add(New DataRelation("relCustomers_Orders", dt2.Columns("CustomerID"), dt1.Columns("CustomerID"), True))
                End With
    
                Me.DataGridView1.DataSource = dt1
    
    
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
    
        End Sub
    
        Private Sub BindObject()
            Dim bs As BindingSource
    
            bs = New BindingSource
            With bs
                .DataSource = ds
                .DataMember = dt1.TableName.ToString()
            End With
    
            'you can now bind controls to the DataTable
    
        End Sub
    
        Private Sub Save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Save.Click
    
            'After User change data you can send your changed data back to the database
            'Here is a sample
    
            Dim dtRows_Added As DataTable = dt1.GetChanges(DataRowState.Added)
            Dim dtRows_Modified As DataTable = dt1.GetChanges(DataRowState.Modified)
            Dim dtRows_Deleted As DataTable = dt1.GetChanges(DataRowState.Deleted)
    
            If ds.HasChanges() Then
                Using Conn As New SqlConnection(My.Settings.MyConnection)
                    Using Adapter As New SqlDataAdapter()
                        'Insert Command
                        If Not (dtRows_Added Is Nothing) Then
                            Adapter.InsertCommand = New SqlCommand("spI_Orders", Conn)
                            Adapter.InsertCommand.CommandType = CommandType.StoredProcedure
                            'Get the Parameter return value
                            Dim myParmAdded As SqlParameter = Adapter.InsertCommand.Parameters.Add("@Rowcount", SqlDbType.Int, 5, "@@ROWCOUNT")
                            myParmAdded.Direction = ParameterDirection.ReturnValue
                            With Adapter.InsertCommand.Parameters
                                .Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID")
                                .Add("@EmployeeID", SqlDbType.Int, 5, "EmployeeID")
                                .Add("@OrderDate", SqlDbType.DateTime, 22, "OrderDate")
                                'keep adding fields with their parameters etc....
                            End With
                        End If
    
                        'Update Command
                        If Not (dtRows_Modified Is Nothing) Then
                            'Similar Code to the above insert command use UpdateCommand Instead
                        End If
    
                        'Delete Command
                        If Not (dtRows_Deleted Is Nothing) Then
                            'Similare code to the above insert command use DeleteCommand instead
                        End If
    
    
                        'once you are done you need to update your datatable 
                        Adapter.Update(dt1)
                    End Using
                End Using
            End If
    
    
        End Sub
    End Class

    John
    Wednesday, November 4, 2009 4:13 PM

All replies

  • To answer your quesitons
    1) Yes, Collections of Objects
    2) Correct
    3) I prefer to code my update methods for more flexibility
    4) Yes if you have your relationship defined in the database, I prefer to do the join in codes
    5) Not really you can create a parameter then assign a value to the parameter

    Here is a sample of code that can get you started

    #Region "Visual Studio Namespace"
    Imports System
    Imports System.Data
    Imports System.Data.SqlClient
    Imports System.Text
    Imports System.IO
    #End Region
    
    
    
    Public Class Form2
    
        Public ds As DataSet
    
        Public dt1 As DataTable
        Public dt2 As DataTable
    
    
        Private Sub Form2_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    
            Try
                ds = New DataSet
                'Northwind Database Connection
                Using Conn As New SqlConnection(My.Settings.MyConnection)
                    'Get Orders Table
                    Using Adapter As New SqlDataAdapter("Select * from dbo.Orders", Conn)
                        Adapter.SelectCommand.CommandType = CommandType.Text
                        Adapter.Fill(ds, "Orders")
                        'Assign Datatable
                        dt1 = ds.Tables("Orders")
                    End Using
                    'Get Customers Table
                    Using Adapter As New SqlDataAdapter("Select * from dbo.Customers", Conn)
                        Adapter.SelectCommand.CommandType = CommandType.Text
                        Adapter.Fill(ds, "Customers")
                        'Assign Datatable
                        dt2 = ds.Tables("Customers")
                    End Using
    
                End Using
                'Design Schema after closing Connection
                'DataTable orders
                'Create PrimaryKey
                dt1.Constraints.Add("pkOrderID", dt1.Columns("OrderID"), True)
                'Allow Null Values
                dt1.Columns("ShippedDate").AllowDBNull = True
                'Set the AutoIncrement for the primary key Column 
                With dt1.Columns("OrderID")
                    .AutoIncrement = True
                    .AutoIncrementSeed = -1
                    .AutoIncrementStep = -1
                End With
    
                'DataTable Customers
                dt2.Constraints.Add("pkCustomerID", dt2.Columns("CustomerID"), True)
                'Allow null
                'With dt2.Columns("CustomerID")
                '    .AutoIncrement = True
                '    .AutoIncrementSeed = -1
                '    .AutoIncrementStep = -1
                'End With
    
                '******************************* DESIGN RELATIONSHIP *************************
    
                'RelationShip Between Customers and Orders
                With ds.Relations
                    .Add(New DataRelation("relCustomers_Orders", dt2.Columns("CustomerID"), dt1.Columns("CustomerID"), True))
                End With
    
                Me.DataGridView1.DataSource = dt1
    
    
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
    
        End Sub
    
        Private Sub BindObject()
            Dim bs As BindingSource
    
            bs = New BindingSource
            With bs
                .DataSource = ds
                .DataMember = dt1.TableName.ToString()
            End With
    
            'you can now bind controls to the DataTable
    
        End Sub
    
        Private Sub Save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Save.Click
    
            'After User change data you can send your changed data back to the database
            'Here is a sample
    
            Dim dtRows_Added As DataTable = dt1.GetChanges(DataRowState.Added)
            Dim dtRows_Modified As DataTable = dt1.GetChanges(DataRowState.Modified)
            Dim dtRows_Deleted As DataTable = dt1.GetChanges(DataRowState.Deleted)
    
            If ds.HasChanges() Then
                Using Conn As New SqlConnection(My.Settings.MyConnection)
                    Using Adapter As New SqlDataAdapter()
                        'Insert Command
                        If Not (dtRows_Added Is Nothing) Then
                            Adapter.InsertCommand = New SqlCommand("spI_Orders", Conn)
                            Adapter.InsertCommand.CommandType = CommandType.StoredProcedure
                            'Get the Parameter return value
                            Dim myParmAdded As SqlParameter = Adapter.InsertCommand.Parameters.Add("@Rowcount", SqlDbType.Int, 5, "@@ROWCOUNT")
                            myParmAdded.Direction = ParameterDirection.ReturnValue
                            With Adapter.InsertCommand.Parameters
                                .Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID")
                                .Add("@EmployeeID", SqlDbType.Int, 5, "EmployeeID")
                                .Add("@OrderDate", SqlDbType.DateTime, 22, "OrderDate")
                                'keep adding fields with their parameters etc....
                            End With
                        End If
    
                        'Update Command
                        If Not (dtRows_Modified Is Nothing) Then
                            'Similar Code to the above insert command use UpdateCommand Instead
                        End If
    
                        'Delete Command
                        If Not (dtRows_Deleted Is Nothing) Then
                            'Similare code to the above insert command use DeleteCommand instead
                        End If
    
    
                        'once you are done you need to update your datatable 
                        Adapter.Update(dt1)
                    End Using
                End Using
            End If
    
    
        End Sub
    End Class

    John
    Wednesday, November 4, 2009 4:13 PM
  • So, looking at your code, I assume you do not use the visual designer?
    I think I am getting more lost trying to navigate through the various properties, files, and generated code then working on my project.
    Wednesday, November 4, 2009 6:21 PM
  • I use the designer if I have to present a quick demo or I have small application (like 3 tables).
    Designer has limitations. In other words depends on what you are trying to develop.

    Some developers use ADO.NET Entity Framework

    Here is a good link that shows you how you start with ADO.NET http://www.devcity.net/Data-Programming/ADO(6)NET.aspx

    (look for ADO.NET for Beginners Part One)


    John
    Wednesday, November 4, 2009 7:20 PM
  • Couple Questions:

    1) If I just add a datagridview without setting anything, then nothing is displayed. Are you using designer to set up grid view and just leaving data source empty? What's the procedure?

    2) How is BindObject() getting called?

    I'm working in C#, so I don't know if the events are different, but I don't think so.


    Wednesday, November 4, 2009 7:35 PM
  • Drag and drop datagridview to your form then assign a datatable to datagridview.datasource = yourdatatable 

    BindObject procedure is not getting called I put there to show you that you can bind textbox, labels, combobox etc... to your datatable

    Here is the code again in C#

    #region "Visual Studio Namespace"
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;
    using System.IO;
    #endregion
    
    
    
    public class Form2
    {
        
        public DataSet ds;
        
        public DataTable dt1;
        public DataTable dt2;
        
        
        private void // ERROR: Handles clauses are not supported in C# Form2_Load(object sender, System.EventArgs e)
        {
            
            try {
                ds = new DataSet();
                //Northwind Database Connection
                using (SqlConnection Conn = new SqlConnection(My.Settings.MyConnection)) {
                    //Get Orders Table
                    using (SqlDataAdapter Adapter = new SqlDataAdapter("Select * from dbo.Orders", Conn)) {
                        Adapter.SelectCommand.CommandType = CommandType.Text;
                        Adapter.Fill(ds, "Orders");
                        //Assign Datatable
                        dt1 = ds.Tables("Orders");
                    }
                    //Get Customers Table
                    using (SqlDataAdapter Adapter = new SqlDataAdapter("Select * from dbo.Customers", Conn)) {
                        Adapter.SelectCommand.CommandType = CommandType.Text;
                        Adapter.Fill(ds, "Customers");
                        //Assign Datatable
                        dt2 = ds.Tables("Customers");
                        
                    }
                }
                //Design Schema after closing Connection
                //DataTable orders
                //Create PrimaryKey
                dt1.Constraints.Add("pkOrderID", dt1.Columns("OrderID"), true);
                //Allow Null Values
                dt1.Columns("ShippedDate").AllowDBNull = true;
                //Set the AutoIncrement for the primary key Column 
                {
                    dt1.Columns("OrderID").AutoIncrement = true;
                    dt1.Columns("OrderID").AutoIncrementSeed = -1;
                    dt1.Columns("OrderID").AutoIncrementStep = -1;
                }
                
                //DataTable Customers
                dt2.Constraints.Add("pkCustomerID", dt2.Columns("CustomerID"), true);
                //Allow null
                //With dt2.Columns("CustomerID")
                // .AutoIncrement = True
                // .AutoIncrementSeed = -1
                // .AutoIncrementStep = -1
                //End With
                
                //******************************* DESIGN RELATIONSHIP *************************
                
                //RelationShip Between Customers and Orders
                {
                    ds.Relations.Add(new DataRelation("relCustomers_Orders", dt2.Columns("CustomerID"), dt1.Columns("CustomerID"), true));
                }
                
                    
                this.DataGridView1.DataSource = dt1;
            }
            catch (Exception ex) {
                
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                
            }
        }
        
        private void BindObject()
        {
            BindingSource bs = default(BindingSource);
            
            bs = new BindingSource();
            {
                bs.DataSource = ds;
                bs.DataMember = dt1.TableName.ToString();
                
                //you can now bind controls to the DataTable
                
            }
        }
        
        private void // ERROR: Handles clauses are not supported in C# ToolStripButton1_Click(System.Object sender, System.EventArgs e)
        {
            
            //After User change data you can send your changed data back to the database
            //Here is a sample
            
            DataTable dtRows_Added = dt1.GetChanges(DataRowState.Added);
            DataTable dtRows_Modified = dt1.GetChanges(DataRowState.Modified);
            DataTable dtRows_Deleted = dt1.GetChanges(DataRowState.Deleted);
            
            if (ds.HasChanges()) {
                using (SqlConnection Conn = new SqlConnection(My.Settings.MyConnection)) {
                    using (SqlDataAdapter Adapter = new SqlDataAdapter()) {
                        //Insert Command
                        if ((dtRows_Added != null)) {
                            Adapter.InsertCommand = new SqlCommand("spI_Orders", Conn);
                            Adapter.InsertCommand.CommandType = CommandType.StoredProcedure;
                            //Get the Parameter return value
                            SqlParameter myParmAdded = Adapter.InsertCommand.Parameters.Add("@Rowcount", SqlDbType.Int, 5, "@@ROWCOUNT");
                            myParmAdded.Direction = ParameterDirection.ReturnValue;
                            {
                                Adapter.InsertCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
                                Adapter.InsertCommand.Parameters.Add("@EmployeeID", SqlDbType.Int, 5, "EmployeeID");
                                Adapter.InsertCommand.Parameters.Add("@OrderDate", SqlDbType.DateTime, 22, "OrderDate");
                                //keep adding fields with their parameters etc....
                            }
                        }
                        
                        //Update Command
                        if ((dtRows_Modified != null)) {
                        }
                        //Similar Code to the above insert command use UpdateCommand Instead
                        
                        //Delete Command
                        if ((dtRows_Deleted != null)) {
                        }
                        //Similare code to the above insert command use DeleteCommand instead
                        
                        
                        //once you are done you need to update your datatable 
                        Adapter.Update(dt1);
                    }
                }
                
                
            }
        }
    }
     
    John
    Wednesday, November 4, 2009 7:41 PM
  • Ok, I got data and it is displayed in the gridview. Now my problem is the grid view is all smooshed up in the corner and the column names are not fully visible. I'd also like to call the columns something other than the field name from the database, so it is more readable.

    Good progress so far! I appreciate your help.
    Wednesday, November 4, 2009 8:29 PM
  • Here is a sample code for a datagridview, this procedure gets called at run-time, basically changing properties and adding combobox and many other DataGridView specs.

    private void FillUsersDGV()
    {
        try {
            
            //Bind DataGridView
            DataGridViewTextBoxColumn TxtBoxColumn = default(DataGridViewTextBoxColumn);
            DataGridViewComboBoxColumn cboColumn = default(DataGridViewComboBoxColumn);
            
            {
                dgvUsers.AutoGenerateColumns = false;
                dgvUsers.Columns.Clear();
                    //dtUSER 'Using Binding Source for data source for Filtering.
                dgvUsers.DataSource = bsUSER;
            }
            //Populate DataGridView Columns
            //Index Column 0
            TxtBoxColumn = new DataGridViewTextBoxColumn();
            {
                TxtBoxColumn.DataPropertyName = dtUSER.Columns(USER_UserID).ColumnName.ToString();
                TxtBoxColumn.Name = dtUSER.Columns(USER_UserID).ColumnName.ToString();
                TxtBoxColumn.HeaderText = "User ID";
                TxtBoxColumn.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
                TxtBoxColumn.DefaultCellStyle.BackColor = Color.Silver;
                dgvUsers.Columns.Add(TxtBoxColumn);
                //AutoSize Column
                TxtBoxColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
                //Get Column Width After AutoSizing
                int myColWidth = dgvUsers.Columns(dtUSER.Columns(USER_UserID).ColumnName.ToString()).Width;
                //Turn Off AutoSizeMode.
                TxtBoxColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.None;
                TxtBoxColumn.Width = myColWidth;
                //Assign the declared Width
                TxtBoxColumn.Visible = true;
            }
            
            //Index Column 2
            TxtBoxColumn = new DataGridViewTextBoxColumn();
            {
                TxtBoxColumn.DataPropertyName = dtUSER.Columns(USER_FirstName).ColumnName.ToString();
                TxtBoxColumn.Name = dtUSER.Columns(USER_FirstName).ColumnName.ToString();
                TxtBoxColumn.HeaderText = "First Name";
                TxtBoxColumn.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft;
                dgvUsers.Columns.Add(TxtBoxColumn);
                //AutoSize Column
                TxtBoxColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
                //Get Column Width After AutoSizing
                int myColWidth = dgvUsers.Columns(dtUSER.Columns(USER_FirstName).ColumnName.ToString()).Width;
                //Turn Off AutoSizeMode
                TxtBoxColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.None;
                TxtBoxColumn.Width = myColWidth;
                // Assign the declared width
                TxtBoxColumn.Visible = true;
            }
            //.DataGridView.Rows.Item(2).Height = 100
            
            //Index Column 3
            TxtBoxColumn = new DataGridViewTextBoxColumn();
            {
                TxtBoxColumn.DataPropertyName = dtUSER.Columns(USER_LastName).ColumnName.ToString();
                TxtBoxColumn.Name = dtUSER.Columns(USER_LastName).ColumnName.ToString();
                TxtBoxColumn.HeaderText = "Last Name";
                TxtBoxColumn.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft;
                dgvUsers.Columns.Add(TxtBoxColumn);
                //AutoSize Column
                TxtBoxColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
                //Get Column Width After Autosizing
                int myColWidth = dgvUsers.Columns(dtUSER.Columns(USER_LastName).ColumnName.ToString()).Width;
                //Turn Off AutoSizeMode
                TxtBoxColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.None;
                TxtBoxColumn.Width = myColWidth;
                //Assign the declared width
                TxtBoxColumn.Visible = true;
            }
            
            //Email Field
            TxtBoxColumn = new DataGridViewTextBoxColumn();
            {
                TxtBoxColumn.DataPropertyName = dtUSER.Columns(USER_Email).ColumnName.ToString();
                TxtBoxColumn.Name = dtUSER.Columns(USER_Email).ColumnName.ToString();
                TxtBoxColumn.HeaderText = "Login ID";
                TxtBoxColumn.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft;
                dgvUsers.Columns.Add(TxtBoxColumn);
                //AutoSize Column 
                TxtBoxColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
                //Get Column Width After AutoSizing
                int myColWidth = dgvUsers.Columns(dtUSER.Columns(USER_Email).ColumnName.ToString()).Width;
                //Turn Off AutoSize 
                TxtBoxColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.None;
                TxtBoxColumn.Width = myColWidth;
                // Assign the declared width 
                TxtBoxColumn.Visible = true;
            }
            
            //Index Column 4
            cboColumn = new DataGridViewComboBoxColumn();
            {
                cboColumn.DataSource = dtCustomUSERSTATUS;
                cboColumn.DisplayMember = dtCustomUSERSTATUS.Columns("UserStatus").ToString();
                cboColumn.DataPropertyName = dtUSER.Columns(USER_Status).ColumnName.ToString();
                cboColumn.Name = dtUSER.Columns(USER_Status).ColumnName.ToString();
                cboColumn.HeaderText = "Status";
                cboColumn.ValueMember = dtCustomUSERSTATUS.Columns("UserStatusBit").ToString();
                dgvUsers.Columns.Add(cboColumn);
                //Change the Look of the combobox, don't show the drop down list.
                cboColumn.DisplayStyle = DataGridViewComboBoxDisplayStyle.Nothing;
                cboColumn.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
                //AutoSize Column 
                cboColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
                //Get Column Width After AutoSizing
                int myColWidth = dgvUsers.Columns(dtUSER.Columns(USER_Status).ColumnName.ToString()).Width;
                //Turn Off AutoSizeMode
                cboColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.None;
                cboColumn.Width = myColWidth;
                //Assign the declared width
                cboColumn.Visible = true;
            }
            
            
            
            //Index Column 5
            cboColumn = new DataGridViewComboBoxColumn();
            {
                cboColumn.DataSource = dtCustomFIRSTTIMEUSER;
                cboColumn.DisplayMember = dtCustomFIRSTTIMEUSER.Columns("FirstTimeUser").ToString();
                cboColumn.DataPropertyName = dtUSER.Columns(USER_FirstTimeUser).ColumnName.ToString();
                cboColumn.Name = dtUSER.Columns(USER_FirstTimeUser).ColumnName.ToString();
                cboColumn.HeaderText = "First Time User";
                cboColumn.ValueMember = dtCustomFIRSTTIMEUSER.Columns("FirstTimeUserBit").ToString();
                dgvUsers.Columns.Add(cboColumn);
                //Change the Look of the combobox, don't show the drop down list.
                cboColumn.DisplayStyle = DataGridViewComboBoxDisplayStyle.Nothing;
                cboColumn.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
                //AutoSize Column 
                cboColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
                //Get Column width After AutoSizing
                int myColWidth = dgvUsers.Columns(dtUSER.Columns(USER_FirstTimeUser).ColumnName.ToString()).Width;
                //Turn Off AutoSizeMode
                cboColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.None;
                cboColumn.Width = myColWidth;
                //Assign the declared width
                cboColumn.Visible = true;
            }
            
            //Security Roles
            cboColumn = new DataGridViewComboBoxColumn();
            {
                cboColumn.DataSource = dtROLE;
                cboColumn.DisplayMember = dtROLE.Columns(ROLE_Role).ColumnName.ToString();
                cboColumn.DataPropertyName = dtUSER.Columns(USER_RoleID).ColumnName.ToString();
                cboColumn.Name = dtUSER.Columns(USER_RoleID).ColumnName.ToString();
                cboColumn.HeaderText = "Security Roles";
                cboColumn.ValueMember = dtROLE.Columns(ROLE_RoleID).ColumnName.ToString();
                dgvUsers.Columns.Add(cboColumn);
                //Change the look of the comboBox, don't show the drop down list.
                cboColumn.DisplayStyle = DataGridViewComboBoxDisplayStyle.Nothing;
                cboColumn.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
                //AutoSize Column
                cboColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
                //Get Column Width After AutoSizing
                int myColWidth = dgvUsers.Columns(dtUSER.Columns(USER_RoleID).ColumnName.ToString()).Width;
                //Turn Off AutoSizeMode
                cboColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.None;
                cboColumn.Width = myColWidth;
                //Assign the declared width 
                cboColumn.Visible = true;
            }
            
            //Index Column 6
            TxtBoxColumn = new DataGridViewTextBoxColumn();
            {
                TxtBoxColumn.DataPropertyName = dtUSER.Columns(USER_Address).ColumnName.ToString();
                TxtBoxColumn.Name = dtUSER.Columns(USER_Address).ColumnName.ToString();
                TxtBoxColumn.HeaderText = "Address";
                TxtBoxColumn.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft;
                dgvUsers.Columns.Add(TxtBoxColumn);
                //AutoSize Column
                TxtBoxColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
                //Get Column Width After AutoSizing
                int myColWidth = dgvUsers.Columns(dtUSER.Columns(USER_Address).ColumnName.ToString()).Width;
                //Turn Off AutoSizeMode
                TxtBoxColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.None;
                TxtBoxColumn.Width = myColWidth;
                // Assign the declare width 
                TxtBoxColumn.Visible = true;
            }
            
            //Index Column 7
            TxtBoxColumn = new DataGridViewTextBoxColumn();
            {
                TxtBoxColumn.DataPropertyName = dtUSER.Columns(USER_City).ColumnName.ToString();
                TxtBoxColumn.Name = dtUSER.Columns(USER_City).ColumnName.ToString();
                TxtBoxColumn.HeaderText = "City";
                TxtBoxColumn.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft;
                dgvUsers.Columns.Add(TxtBoxColumn);
                //AutoSize Column 
                TxtBoxColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
                int myColWidth = dgvUsers.Columns(dtUSER.Columns(USER_City).ColumnName.ToString()).Width;
                //Turn Off AutoSizeMode
                TxtBoxColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.None;
                TxtBoxColumn.Width = myColWidth;
                //Assign the declare width 
                TxtBoxColumn.Visible = true;
            }
            
            //Index Column 8
            TxtBoxColumn = new DataGridViewTextBoxColumn();
            {
                TxtBoxColumn.DataPropertyName = dtUSER.Columns(USER_State).ColumnName.ToString();
                TxtBoxColumn.Name = dtUSER.Columns(USER_State).ColumnName.ToString();
                TxtBoxColumn.HeaderText = "State";
                TxtBoxColumn.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft;
                dgvUsers.Columns.Add(TxtBoxColumn);
                //AutoSize Column 
                TxtBoxColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
                int myColWidth = dgvUsers.Columns(dtUSER.Columns(USER_State).ColumnName.ToString()).Width;
                //Turn Off AutoSizeMode
                TxtBoxColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.None;
                TxtBoxColumn.Width = myColWidth;
                //Assign the declare width 
                TxtBoxColumn.Visible = true;
            }
            
            //Index Column 9
            TxtBoxColumn = new DataGridViewTextBoxColumn();
            {
                TxtBoxColumn.DataPropertyName = dtUSER.Columns(USER_Country).ColumnName.ToString();
                TxtBoxColumn.Name = dtUSER.Columns(USER_Country).ColumnName.ToString();
                TxtBoxColumn.HeaderText = "Country";
                TxtBoxColumn.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
                dgvUsers.Columns.Add(TxtBoxColumn);
                //AutoSize Column 
                TxtBoxColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
                int myColWidth = dgvUsers.Columns(dtUSER.Columns(USER_Country).ColumnName.ToString()).Width;
                //Turn Off AutoSizeMode
                TxtBoxColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.None;
                TxtBoxColumn.Width = myColWidth;
                //Assign the declare width 
                TxtBoxColumn.Visible = true;
            }
            
            //Index Column 10
            TxtBoxColumn = new DataGridViewTextBoxColumn();
            {
                TxtBoxColumn.DataPropertyName = dtUSER.Columns(USER_Phone).ColumnName.ToString();
                TxtBoxColumn.Name = dtUSER.Columns(USER_Phone).ColumnName.ToString();
                TxtBoxColumn.HeaderText = "Phone #";
                TxtBoxColumn.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
                TxtBoxColumn.DefaultCellStyle.Format = "(###) ###-####";
                dgvUsers.Columns.Add(TxtBoxColumn);
                //AutoSize Column 
                TxtBoxColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
                int myColWidth = dgvUsers.Columns(dtUSER.Columns(USER_Phone).ColumnName.ToString()).Width;
                //Turn Off AutoSizeMode
                TxtBoxColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.None;
                TxtBoxColumn.Width = myColWidth;
                //Assign the declare width 
                TxtBoxColumn.Visible = true;
            }
            
            //Index Column 11
            TxtBoxColumn = new DataGridViewTextBoxColumn();
            {
                TxtBoxColumn.DataPropertyName = dtUSER.Columns(USER_PhoneExtension).ColumnName.ToString();
                TxtBoxColumn.Name = dtUSER.Columns(USER_PhoneExtension).ColumnName.ToString();
                TxtBoxColumn.HeaderText = "Ext #";
                TxtBoxColumn.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
                dgvUsers.Columns.Add(TxtBoxColumn);
                //AutoSize Column 
                TxtBoxColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
                int myColWidth = dgvUsers.Columns(dtUSER.Columns(USER_PhoneExtension).ColumnName.ToString()).Width;
                //Turn Off AutoSizeMode
                TxtBoxColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.None;
                TxtBoxColumn.Width = myColWidth;
                //Assign the declare width 
                TxtBoxColumn.Visible = true;
            }
            
            //Index Column 12
            TxtBoxColumn = new DataGridViewTextBoxColumn();
            {
                TxtBoxColumn.DataPropertyName = dtUSER.Columns(USER_LastLoginDateTime).ColumnName.ToString();
                TxtBoxColumn.Name = dtUSER.Columns(USER_LastLoginDateTime).ColumnName.ToString();
                TxtBoxColumn.HeaderText = "Last Login";
                TxtBoxColumn.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
                TxtBoxColumn.DefaultCellStyle.Format = "dddd, MMMM dd, yyyy hh:mm tt";
                dgvUsers.Columns.Add(TxtBoxColumn);
                //AutoSize Column 
                TxtBoxColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
                int myColWidth = dgvUsers.Columns(dtUSER.Columns(USER_LastLoginDateTime).ColumnName.ToString()).Width;
                //Turn Off AutoSizeMode
                TxtBoxColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.None;
                TxtBoxColumn.Width = myColWidth;
                //Assign the declare width 
                TxtBoxColumn.Visible = true;
            }
            
            //Index Column 13
            cboColumn = new DataGridViewComboBoxColumn();
            {
                cboColumn.DataSource = dtUSER;
                cboColumn.DisplayMember = dtUSER.Columns(USER_Email).ColumnName.ToString();
                cboColumn.DataPropertyName = dtUSER.Columns(USER_ModifiedBy).ColumnName.ToString();
                cboColumn.Name = dtUSER.Columns(USER_ModifiedBy).ColumnName.ToString();
                cboColumn.HeaderText = "Modified By";
                cboColumn.ValueMember = dtUSER.Columns(USER_UserID).ColumnName.ToString();
                dgvUsers.Columns.Add(cboColumn);
                //Change the look of the combobox, don't show the drop down list
                cboColumn.DisplayStyle = DataGridViewComboBoxDisplayStyle.Nothing;
                cboColumn.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
                //AutoSize Column
                cboColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
                //Get Column Width after AutoSizing
                int myColWidth = dgvUsers.Columns(dtUSER.Columns(USER_ModifiedBy).ColumnName.ToString()).Width;
                //Turn off AutoSizeMode
                cboColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.None;
                cboColumn.Width = myColWidth;
                //Assign the declared width 
                cboColumn.Visible = true;
            }
            
            //Index Column 14
            TxtBoxColumn = new DataGridViewTextBoxColumn();
            {
                TxtBoxColumn.DataPropertyName = dtUSER.Columns(USER_ModifiedDate).ColumnName.ToString();
                TxtBoxColumn.Name = dtUSER.Columns(USER_ModifiedDate).ColumnName.ToString();
                TxtBoxColumn.HeaderText = "Modified Date";
                TxtBoxColumn.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
                TxtBoxColumn.DefaultCellStyle.Format = "dddd, MMMM dd, yyyy hh:mm tt";
                dgvUsers.Columns.Add(TxtBoxColumn);
                //AutoSize Column 
                TxtBoxColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
                int myColWidth = dgvUsers.Columns(dtUSER.Columns(USER_ModifiedDate).ColumnName.ToString()).Width;
                //Turn Off AutoSizeMode
                TxtBoxColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.None;
                TxtBoxColumn.Width = myColWidth;
                //Assign the declare width 
                TxtBoxColumn.Visible = true;
            }
            
            //For Each dgvRow As DataGridViewRow In dgvUsers.Rows
            // 'Adjust the height of each Row
            // dgvRow.Height = 20
            //Next
            
            dgvUsers.CurrentRow.Selected = false;
                
            dgvUsers.ClearSelection();
        }
        catch (Exception ex) {
            MessageBox.Show(ex.Message);
            
        }
    }
     
    John
    Wednesday, November 4, 2009 8:49 PM
  • I am not understanding this block of code for the insert

          private void btnUpdate_Click(object sender, EventArgs e)
          {
             DataTable rows_Added    = coursesDataTable.GetChanges(DataRowState.Added);
             DataTable rows_Modified = coursesDataTable.GetChanges(DataRowState.Modified);
             DataTable rows_Deleted  = coursesDataTable.GetChanges(DataRowState.Deleted);
    
             if( coursesDataSet.HasChanges() )
             {
                try
                {
                   OracleConnection connection = new OracleConnection();
                   connection.ConnectionString = ConfigurationManager.AppSettings["ConnectionString"];
    
                   OracleDataAdapter adapter = new OracleDataAdapter();
                   
                   // Insert Command
                   if( rows_Added != null )
                   {
                      string sqlInsert = "INSERT INTO OLEEDO_COURSEID (EEDO_COURSEID, EEDOREVIEW_COURSEID, EEDOFINAL_COURSEID, PRESENTATION)";
                      sqlInsert       += "VALUES(:courseID, :reviewCourseID, :finalCourseID, :presentation)";
    
                      adapter.InsertCommand = new OracleCommand(sqlInsert, connection);
                      adapter.InsertCommand.CommandType = CommandType.Text;
    
                      // Get the parameter return value
                      OracleParameter numAdded = adapter.InsertCommand.Parameters.Add("@Rowcount", OracleType.Int32, 5, "@@ROWCOUNT");
                      numAdded.Direction = ParameterDirection.ReturnValue;
                      {
                         adapter.InsertCommand.Parameters.Add("courseID",       OracleType.NChar, 20, "EEDO_COURSEID");
                         adapter.InsertCommand.Parameters.Add("reviewCourseID", OracleType.NChar, 20, "EEDOREVIEW_COURSEID");
                         adapter.InsertCommand.Parameters.Add("finalcourseID",  OracleType.NChar, 20, "EEDOFINAL_COURSEID");
                         adapter.InsertCommand.Parameters.Add("presentation",   OracleType.NChar, 5,  "PRESENTATION");
                         // Keep adding fields with their parameters etc....
                      }
                   }
    
                   // Update Command
                   if ((rows_Modified != null))
                   {
                      //Similar code to the above insert command use UpdateCommand Instead
                   }
                   
    
                   // Delete Command
                   if( rows_Deleted != null )
                   {
                      // Similar code to the above insert command use DeleteCommand instead
                   }
                  
                   // Update database to reflect datatable 
                   adapter.Update(coursesDataTable);
                }
                catch(Exception error)
                {
                   MessageBox.Show(error.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
    
             }
          }


    What are the arguments in adapter.InsertCommand.Parameters.Add ?
    I look it up on MSDN and it says its a string, well yea, but should one be the same as the field name in the DB, the table adapter, or the gridview?
    I don't follow how I'm getting new rows from the grid view, thier field names, and making them params to an insert command to the DB.
    I don't understand the size param either. Is it a size in bytes? of?

    When I run it, add a new record to the gridview, and hit the update button, I get an error: ORA-01036:Illegal varibale name/number

    I looked on MSDN for the command and paramters information and it really doesn't explain this particular method.
    I'd be all set if I wanted to write a collection of params to the command line though :P



    Wednesday, November 4, 2009 9:42 PM
  • You need the parameters when you send your data back to the database basically (Insert, Update and Delete Commands)
    Insert Command you can supply parameter for every field including your primarykey if it's not set as autoincrement.

    UpdateCommand you need to supply parameter for every field including primary key and adding at the end if it's datarowversion.original  or current etc..

    DeleteCommand you need to supply parameter only for the PrimaryKey

    Datagridview is bound to the datatable so once you insert new row in datagridview the row will be inserted automatically in the datatable.

    The parameter size is basically the datatype size in the database like nvarchar(100) that's nvarchar type with a size 100


    Here is a link that has a good example

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.insertcommand(VS.80).aspx
     
    John
    • Edited by Codernater Wednesday, November 4, 2009 10:13 PM some reason
    Wednesday, November 4, 2009 10:10 PM
  • Ok, I narrowed down the problem to the rows returned parameter. I assumed oracle doesn't like that syntax with the @@. When I commented that bit out it ran and insterted a record. I'll see if I can find an example of getting back rows returned online and go on to update and delete.

    I really appreciate your help!


    Wednesday, November 4, 2009 10:34 PM
  • No Probem.
    Please Remember to mark as an answer.

    Thanks
    John
    • Proposed as answer by Codernater Thursday, November 5, 2009 3:11 AM
    Wednesday, November 4, 2009 10:39 PM