Results 1 to 8 of 8
Like Tree1Likes
  • 1 Post By jmcilhinney

Thread: refresh data within combobox within datagrid view

  1. #1
    gbalcom is offline VB.NET Forum Newbie
    .NET Framework
    .NET 4.5
    Join Date
    Oct 2016
    Location
    Georgia
    Posts
    12
    Reputation
    7

    refresh data within combobox within datagrid view

    frmRecord.PNGHello,
    I have a form (frmRecord) which has a datagridview on it that represents related "tags" for the record shown on frmRecord. This datagrid view has a combo box, which shows available "tags". The data source for the datagrid view is tblJunctionBindingSource. The datasource for the combo box is tblTagsBindingsource.


    frmRecord has a button on it to create a new tag. This button will open a form to add the new tag (frmAddTag). Code for this button's click event is below:

    Code:
        Private Sub Button6_Click(sender As Object, e As EventArgs) Handles Button6.Click
            Dim frm As New frmAddTag
            frm.ShowDialog()
        End Sub



    frmAddTag has the following code behind the save new tag buttons click event:

    Code:
        Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
            frmRecord.TblTagsTableAdapter.Insert(Me.txtComment.Text.ToString, Me.txtDescription.Text.ToString, Date.Now())
            frmRecord.TblTagsBindingSource.EndEdit()
            frmRecord.TblTagsTableAdapter.Update(frmRecord.ACS_Engineering_Changes_beDataSet.tblTags)
    
    
            frmRecord.TblTagsTableAdapter.Fill(frmRecord.ACS_Engineering_Changes_beDataSet.tblTags)
    
    
            MsgBox("Record Added sucessfully")
    
    
            Me.txtComment.Text = ""
            Me.txtDescription.Text = ""
    
    
            'refresh data grid view on form
    
    
           frmRecord.TblJunctionDataGridView.Update()
    
    
    
    
            'close form
            Me.Close()
    
    
    
    
        End Sub

    I can see in the debug folder that the database (access accdb) is indeed taking the record. But when clicking the combo box on frmRecord's datagridview, the new tag isn't available. What am I doing wrong??
    Last edited by gbalcom; 12-06-2016 at 4:11 PM. Reason: add an image

  2. #2
    jmcilhinney's Avatar
    jmcilhinney is offline VB.NET Forum Moderator
    .NET Framework
    .NET 4.0
    Join Date
    Aug 2004
    Location
    Sydney, Australia
    Posts
    13,773
    Reputation
    1703
    Like so many people, you're doing it the wrong way around. You should be adding the new value to the DataSet first, so it will then show up in your grid even before its in the database. You then use your table adapter to save the changes from your DataTable back to the database. That frmAddTag should not be communicating with the database. It should simply be returning the new value to the main form. The main for then adds that new value to its own DataSet and then to the database.

  3. #3
    gbalcom is offline VB.NET Forum Newbie
    .NET Framework
    .NET 4.5
    Join Date
    Oct 2016
    Location
    Georgia
    Posts
    12
    Reputation
    7
    Jmchilhinney,
    Thank you for the response. I've reread it multiple times, and I'm digesting the fact that I don't really understand the "flow" of data here. I've spent time this morning reading on the MSDN site, and trying to get a basic grasp of datasets, datatables, and their flow to form objects. Frankly, I've got a lot to learn. I see you're beginner tutorial in your signature, which I've bookmarked for when I have time. Until then, here's what I have so far:

    Save button on frmAddNewTag

    Code:
        Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
            '====record below will add it to the database.  Forums said it was a bad idea...trying to figure it out still
            'frmRecord.TblTagsTableAdapter.Insert(Me.txtComment.Text.ToString, Me.txtDescription.Text.ToString, Date.Now())
    
    
    
    
            Dim myTable As DataTable
            myTable = New ACS_Engineering_Changes_beDataSet.tblTagsDataTable
            Dim Row As DataRow = myTable.NewRow
    
    
            'add row to collection
            Row("Description") = Me.txtDescription.ToString
            Row("Comments") = Me.txtComment.ToString
            Row("TimeStamp") = Date.Now
    
    
            myTable.Rows.Add(Row)
    
    
    
    
            'frmRecord.TblTagsTableAdapter.Update(frmRecord.ACS_Engineering_Changes_beDataSet.tblTags)
    
    
            frmRecord.TblTagsTableAdapter.GetData()
            'frmRecord.TblTagsBindingSource.EndEdit()
            MsgBox("Record Added sucessfully")
    
    
            Me.txtComment.Text = ""
            Me.txtDescription.Text = ""
    
    
            'refresh data grid view on form
            frmRecord.TblJunctionDataGridView.Update()
    
    
    
    
            'close form
            Me.Close()
    
    
    
    
        End Sub
    Then back on the frmRecord, add new tag button (Calls prior then executes more code)

    Code:
        Private Sub Button6_Click(sender As Object, e As EventArgs) Handles Button6.Click
            Dim frm As New frmAddTag
            frm.ShowDialog()
            'frm.BindingNavigatorAddNewItem.PerformClick()
    
    
            Me.TblTagsBindingSource.EndEdit()
            TblJunctionDataGridView.Update()
    
    
            'Dim i As Integer = 0
    
    
            'For i = 0 To TblJunctionDataGridView.ColumnCount - 1
            'MessageBox.Show("Column" & i & " is " & TblJunctionDataGridView.Columns(i).GetType.ToString)
            'Next i
    
    
    
    
    
    
            Dim ComboBoxColumn As DataGridViewComboBoxColumn = TblJunctionDataGridView.Columns(2)
    
    
            ComboBoxColumn.HeaderText = "Tag Name"
            ComboBoxColumn.DataSource = TblTagsBindingSource
    
    
    
    
        End Sub
    I can't figure out how to make the data source take the new record. It doesn't do anything at this point.

  4. #4
    gbalcom is offline VB.NET Forum Newbie
    .NET Framework
    .NET 4.5
    Join Date
    Oct 2016
    Location
    Georgia
    Posts
    12
    Reputation
    7

    Unhappy Still trying

    I'm still struggling with this. I've read and re-read the tutorials here: Visual Basic .NET programming for Beginners - How to Add a New Record

    I have a basic understanding now of how the dataset, and data adapter and connection objects work (hopefully)

    Here's my new code:

    Code:
    Public Class frmAddTag
        Dim con As New OleDb.OleDbConnection
        Dim dbProvider As String = "PROVIDER=Microsoft.ACE.OLEDB.12.0;"
        Dim TheDatabase As String = My.Settings.Database_Location
        Dim FullDatabasePath As String = TheDatabase
        'Dim dbsource As String = "Data Source = '" & FullDatabasePath & "'"
        Dim dbsource As String = "Data Source = " & FullDatabasePath & "; Persist Security Info=False;"
        Dim myDocumentsFolder As String
    
    
    
    
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        'specify only the fields needed.
        Dim sql As String = "Select [Description], [Comments], [TimeStamp] FROM tblTags"
        Dim MaxRows As Integer
    
    
    
    
    
    
    
    
    
    
        Private Sub btnClose_Click(sender As Object, e As EventArgs) Handles btnClose.Click
            Me.Close()
    
    
        End Sub
    
    
        Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
            '====record below will add it to the database.  Forums said it was a bad idea...trying to figure it out still
            'frmRecord.TblTagsTableAdapter.Insert(Me.txtComment.Text.ToString, Me.txtDescription.Text.ToString, Date.Now())
    
    
    
    
            'myDocumentsFolder = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)
    
    
    
    
    
    
            'MessageBox.Show(dbProvider & dbsource)
    
    
    
    
            If Me.txtComment.ToString = "" Or Me.txtDescription.ToString = "" Then
                MessageBox.Show("Both fields must be filled out to save record")
    
    
            Else
                'GET HOW MANY ROWS ARE IN THE TABLE
                MaxRows = ds.Tables("tblTags").Rows.Count
    
    
                'CREATE THE COMMAND BUILDER
                Dim cb As New OleDb.OleDbCommandBuilder(da)
                Dim dsNewRow As DataRow
    
    
                'CREATE NEW ROW
                dsNewRow = ds.Tables("tblTags").NewRow()
    
    
                'BUILD ROW INFORMATION
    
    
                dsNewRow.Item("Description") = Me.txtDescription.Text
                dsNewRow.Item("Comments") = Me.txtComment.Text
                dsNewRow.Item("TimeStamp") = Date.Now
    
    
                Try
                    con.Open()
    
    
                    'ADD THIS NEW ROW TO THE DATASET
    
    
                    ds.Tables("tblTags").Rows.Add(dsNewRow)
    
    
                    '======================================================================================================
                    'STILL HAVING TROUBLE WITH THIS SQL INSERT STATMENT.  NEED TO SHOW THE VALUES TO DETERMINE WHAT IS WRONG.
                    '======================================================================================================
                    cb.GetInsertCommand()
    
    
    
    
    
    
    
    
                    'MessageBox.Show(da.SelectCommand.CommandText)
                    'MessageBox.Show(da.InsertCommand.CommandText)
                    'MessageBox.Show(da.UpdateCommand.CommandText)
                    'MessageBox.Show(da.DeleteCommand.CommandText)
    
    
                    'UPDATE DATABASE WITH NEW DATASET INFORMATION
                    da.Update(ds, "tblTags")
    
    
                Catch ex As Exception
                    MessageBox.Show("Error Updating: " & Err.Description)
                Finally
                    con.Close()
    
    
                End Try
            End If
    
    
    
    
    
    
            Me.txtComment.Text = ""
            Me.txtDescription.Text = ""
    
    
            'refresh data grid view on form
            frmRecord.TblJunctionDataGridView.Update()
    
    
    
    
    
    
            'close form
            Me.Close()
    
    
    
    
        End Sub
    
    
        Private Sub frmAddTag_Load(sender As Object, e As EventArgs) Handles Me.Load
            'FINISH CONNECTION STRING
            con.ConnectionString = dbProvider & dbsource
    
    
            'OPEN DB
            con.Open()
            
    
    
            'PASS THE SQL STRING AND CONNECTION OBJECT TO THE DATA ADAPTER
            da = New OleDb.OleDbDataAdapter(sql, con)
    
    
            'FILL DATASET WITH RECORDS FROM THE DB'S TABLE
            da.Fill(ds, "tblTags")
    
    
    
    
    
    
            'CLOSE DB
            con.Close()
        End Sub
    End Class
    The error I've been getting is this: Syntax error in INSERT INTO (I'm attaching screenshots).
    Capture.PNGCapture1.PNG
    It seems the insert command has a value of "nothing". Why isn't the command builder making what it needs?

  5. #5
    jmcilhinney's Avatar
    jmcilhinney is offline VB.NET Forum Moderator
    .NET Framework
    .NET 4.0
    Join Date
    Aug 2004
    Location
    Sydney, Australia
    Posts
    13,773
    Reputation
    1703
    The command builder IS making what it needs, but it does so dynamically. If the error message says that there's a syntax error in the INSERT statement then obviously an INSERT statement is being generated.

    What you're experiencing is not uncommon and generally arises because of poor choice of database column names. If you use a query with a wildcard, e.g. "SELECT * FROM MyTable", then the command builder will not automatically escape your column names in the statements it generates. That means that any column names that are reserved words or contain spaces or other special characters will cause a syntax error. For instance, a column named 'Password' will be an issue in Access because that's a reserved word. The best option is to avoid such column names but, if that's not possible for some reason, then you need to escape at least those problematic column names in your SQL code. There are two ways to do that:

    1. Don't use a wildcard in your query. If you use the column names yourself then you will need to escape those column names and then the command builder will follow your lead and do the same.
    2. Set the QuotePrefix and QuoteSuffix properties of the command builder. The command builder will then wrap all column names in those values, thus forcing them all to be interpreted as single identifiers. For Access, you would use "[" and "]" respectively.

  6. #6
    gbalcom is offline VB.NET Forum Newbie
    .NET Framework
    .NET 4.5
    Join Date
    Oct 2016
    Location
    Georgia
    Posts
    12
    Reputation
    7

    I've tried that...

    Quote Originally Posted by jmcilhinney View Post
    The command builder IS making what it needs, but it does so dynamically. If the error message says that there's a syntax error in the INSERT statement then obviously an INSERT statement is being generated.

    What you're experiencing is not uncommon and generally arises because of poor choice of database column names. If you use a query with a wildcard, e.g. "SELECT * FROM MyTable", then the command builder will not automatically escape your column names in the statements it generates. That means that any column names that are reserved words or contain spaces or other special characters will cause a syntax error. For instance, a column named 'Password' will be an issue in Access because that's a reserved word. The best option is to avoid such column names but, if that's not possible for some reason, then you need to escape at least those problematic column names in your SQL code. There are two ways to do that:

    1. Don't use a wildcard in your query. If you use the column names yourself then you will need to escape those column names and then the command builder will follow your lead and do the same.
    2. Set the QuotePrefix and QuoteSuffix properties of the command builder. The command builder will then wrap all column names in those values, thus forcing them all to be interpreted as single identifiers. For Access, you would use "[" and "]" respectively.

    Hello Jmcilhinney,
    Thank you for the response. I have tried the brackets, based on a web search before I posted last. I also had changed my select statement to only pull the required colums. I'm using


    Dim sql As String = "Select [Description], [Comments], [TimeStamp] FROM tblTags"

  7. #7
    jmcilhinney's Avatar
    jmcilhinney is offline VB.NET Forum Moderator
    .NET Framework
    .NET 4.0
    Join Date
    Aug 2004
    Location
    Sydney, Australia
    Posts
    13,773
    Reputation
    1703
    And? Are you saying, without actually saying, that you're still seeing a syntax error in the INSERT statement after those changes? If so then you should look at the INSERT statement. Before calling Update, call GetInsertCommand on the command builder and then look at the CommandText of that command. You can either do that in code or set a breakpoint and do it in the debugger. If you do that and the issue is still not clear to you, post and show us the actual code you're using now and the actual INSERT statement.
    gbalcom likes this.

  8. #8
    gbalcom is offline VB.NET Forum Newbie
    .NET Framework
    .NET 4.5
    Join Date
    Oct 2016
    Location
    Georgia
    Posts
    12
    Reputation
    7
    JmiCilhinney,
    Here is the code for the form:

    Code:
    ublic Class frmAddTag
        Dim con As New OleDb.OleDbConnection
        Dim dbProvider As String = "PROVIDER=Microsoft.ACE.OLEDB.12.0;"
        Dim TheDatabase As String = My.Settings.Database_Location
        Dim FullDatabasePath As String = TheDatabase
        Dim dbsource As String = "Data Source = " & FullDatabasePath & "; Persist Security Info=False;"
        Dim myDocumentsFolder As String
    
    
    
    
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        'specify only the fields needed.
        Dim sql As String = "Select [Description], [Comments], [TimeStamp] FROM tblTags"
        Dim MaxRows As Integer
        Private Sub frmAddTag_Load(sender As Object, e As EventArgs) Handles Me.Load
            'FINISH CONNECTION STRING
            con.ConnectionString = dbProvider & dbsource
    
    
            'OPEN DB
            con.Open()
    
    
            'PASS THE SQL STRING AND CONNECTION OBJECT TO THE DATA ADAPTER
            da = New OleDb.OleDbDataAdapter(sql, con)
    
    
            'FILL DATASET WITH RECORDS FROM THE DB'S TABLE
            da.Fill(ds, "tblTags")
    
    
            'CLOSE DB
            con.Close()
        End Sub
    
    
        Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
    
    
            If Me.txtComment.ToString = "" Or Me.txtDescription.ToString = "" Then
                MessageBox.Show("Both fields must be filled out to save record")
    
    
            Else
                'GET HOW MANY ROWS ARE IN THE TABLE
                MaxRows = ds.Tables("tblTags").Rows.Count
    
    
                'CREATE THE COMMAND BUILDER
                Dim cb As New OleDb.OleDbCommandBuilder(da)
                Dim dsNewRow As DataRow
    
    
                'CREATE NEW ROW
                dsNewRow = ds.Tables("tblTags").NewRow()
    
    
                'BUILD ROW INFORMATION
    
    
                dsNewRow.Item("Description") = Me.txtDescription.Text
                dsNewRow.Item("Comments") = Me.txtComment.Text
                dsNewRow.Item("TimeStamp") = Date.Now
    
    
                Try
                    con.Open()
    
    
                    'ADD THIS NEW ROW TO THE DATASET
    
    
                    ds.Tables("tblTags").Rows.Add(dsNewRow)
    
    
                    '======================================================================================================
                    'STILL HAVING TROUBLE WITH THIS SQL INSERT STATMENT.  NEED TO SHOW THE VALUES TO DETERMINE WHAT IS WRONG.
                    '======================================================================================================
    
    
                    Debug.Print(cb.GetInsertCommand.CommandText)
    
    
    
    
                    'UPDATE DATABASE WITH NEW DATASET INFORMATION
                    da.Update(ds, "tblTags")
    
    
                Catch ex As Exception
                    MessageBox.Show("Error Updating: " & Err.Description)
                Finally
                    con.Close()
    
    
                End Try
            End If
    
    
    
    
    
    
            Me.txtComment.Text = ""
            Me.txtDescription.Text = ""
    
    
            'refresh data grid view on form
            frmRecord.TblJunctionDataGridView.Update()
    
    
    
    
    
    
            'close form
            Me.Close()
    
    
    
    
        End Sub
    I set a breakpoint at the "Try" line under the "btnSave_Click" Sub, and stepped through. The debug.print(cb.GetInsertCommand.CommandText) line produces this:
    Code:
    INSERT INTO tblTags (Description, Comments, TimeStamp) VALUES (?, ?, ?)
    Why aren't the values being populated from the new row I've created into the question marks? I'm unclear on the next step.

    Thank you for your efforts thus far in helping me, I really appreciate it.

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •