refresh data within combobox within datagrid view

[gbalcom]

Hello,
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:

    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:

    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??

 

[jmcilhinney]

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.

 

[gbalcom]

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

    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)

    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.

 

[gbalcom]

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:

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).

It seems the insert command has a value of "nothing". Why isn't the command builder making what it needs?

 

[jmcilhinney]

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.

 

[gbalcom]

I've tried that...

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"

 

[jmcilhinney]

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]

JmiCilhinney,
Here is the code for the form:

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:

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.