none
How to update Datagridview dynamically using ms access in vb .net 2010

    Question

  •   provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source ="
            dataFile = "C:\Data_Logg\Data_Log.accdb"
            connString = provider & dataFile
            myConnection.ConnectionString = connString
            myConnection.Open()

              Dim str As String
                str = "insert into Ch_Data ([Date_1], [Time_1], [Tag], [Ch1], [Ch2], [Ch3], [Ch4], [Ch5], [Ch6], [Ch7], [Ch8], [Ch9], [Ch10], [Ch11], [Ch12], [Pwr_Stat], [Tag_Id]) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
                Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
                cmd.Parameters.Add(New OleDbParameter("Date_1", CType(cur_Date, Date)))
                cmd.Parameters.Add(New OleDbParameter("Time_1", CType(cur_Time, Date)))

    ..

    ...

    ...

        cmd.Parameters.Add(New OleDbParameter("Tag_Id", CType(TagId, Integer)))
                'myConnection.Close()
                Try
                    cmd.ExecuteNonQuery()
                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try

    i have done the above code to insert data into the table in one form & i have to update the same data with some query in datagridview and chart which are in seperate forms. i done above code based on the details i got from forum. pls anybody give some tips to do. thanks in advance

    Saturday, August 09, 2014 10:48 AM

Answers

  • Hello,

    I would suggest loading the DataGridView via a connection and command where the command.ExecuteReader loads the data into a DataTable. Create a BindingSource, set the data source of the BindingSource to the DataTable. Now set the BindingSource as the DataSource of the DataGridView.

    Now when adding a new record.

    1. Use a insert and select statement as shown here. The important thing here is we immediately get the new primary key to use in the bullet three.
    2. In the event you are doing the insert, cast the BindingSource.DataSource to a DataTable i.e. CType(BindingSource.DataSource,DataTable)
    3. From the above you do CType(BindingSource.DataSource,DataTable).Rows.Add(New Object() {a,b,c}) where a,b,c represents the data that makes up the row just added to the database.

    I have a broad-stroke example that shows how to do the add record here. In the example I have split up code into different files but you can do this in one file. I recommend not using string concatenation for writing your SQL statement and this demo shows how to avoid string concatenation. Lastly I show two different ways to do OleDbParameter, add and AddWithValue, there are time for both which is why I show both.

    Note, for field names in the insert statement you don't need [fieldname] unless the field name is a reserve work and if so best not to use a reserve word in the first place.

    Hope this helps.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Saturday, August 09, 2014 11:25 AM
  • thanks for your reply, i will try...

    I got thinking about this as the question comes up on a regular basis so I wrote a demonstration project in a MSDN article

    The solution has a class project for database operations and a forms project, one for VB.NET and one for C#.

    Give it a try then go into the code and read the comments.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Tuesday, August 12, 2014 3:19 PM

All replies

  • Hello,

    I would suggest loading the DataGridView via a connection and command where the command.ExecuteReader loads the data into a DataTable. Create a BindingSource, set the data source of the BindingSource to the DataTable. Now set the BindingSource as the DataSource of the DataGridView.

    Now when adding a new record.

    1. Use a insert and select statement as shown here. The important thing here is we immediately get the new primary key to use in the bullet three.
    2. In the event you are doing the insert, cast the BindingSource.DataSource to a DataTable i.e. CType(BindingSource.DataSource,DataTable)
    3. From the above you do CType(BindingSource.DataSource,DataTable).Rows.Add(New Object() {a,b,c}) where a,b,c represents the data that makes up the row just added to the database.

    I have a broad-stroke example that shows how to do the add record here. In the example I have split up code into different files but you can do this in one file. I recommend not using string concatenation for writing your SQL statement and this demo shows how to avoid string concatenation. Lastly I show two different ways to do OleDbParameter, add and AddWithValue, there are time for both which is why I show both.

    Note, for field names in the insert statement you don't need [fieldname] unless the field name is a reserve work and if so best not to use a reserve word in the first place.

    Hope this helps.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Saturday, August 09, 2014 11:25 AM
  • thanks for your reply, i will try...
    Tuesday, August 12, 2014 12:26 PM
  • thanks for your reply, i will try...

    I got thinking about this as the question comes up on a regular basis so I wrote a demonstration project in a MSDN article

    The solution has a class project for database operations and a forms project, one for VB.NET and one for C#.

    Give it a try then go into the code and read the comments.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Tuesday, August 12, 2014 3:19 PM