Updating access database using datatable
I haven't had to do work with databases much, and everything I know I've learned from examples from the internet (probably bad ones at that). So what is the proper way to update a database using Ole Db Adapter and data Set? Open(); Ole Db Adapter adapter = new Ole Db Data Adapter("SELECT * From " table, conn); Ole Db Command Builer cmd Builer = new Ole Db Command Builder(adapter); Data Set = new Dat Set(); adapter. It populates the Data Grid View as it is intended to so that part of the program works. Ole Db Public Class frm Database Dim con As New Ole Db Connection Dim ds As New Data Set Dim dt As New Data Table Dim da As New Ole Db Data Adapter Private Sub frm Database_Load(sender As Object, e As Event Args) Handles My Base. However, I'm trying to get the Data Grid View to update the Access database file with any changes that are made to it but after countless attempts at trying to fix my code or looking for an alternative solution, I am stumped. Add(new Ole Db Parameter("@field Name",row[field Name])); // eg: adapter. Select * is usually a poor option to specifying the column names and using aliases. Add(new Ole Db Parameter("@guid",row["GUID"])); adapter. table.column Name (that fixes the problem in My SQL so maybe it does in Access) also, try putting [ ] around the column name. this makes working with your dataset and datatable much easier as you can access the column by its alias instead of by column indexes.
That is beyond my control, so I have to work with what is given me. Fill( data Set, table); Data Table data Table = data Set.
This seems to be the case, since all the tables update except the ones with "GUID" as one of the column names, a reserved word. Update(data Table); // The problem may be that the column names (especially those whose name are reserved words) should be surrounded by square brackets. Open(); Ole Db Data Adapter adapter = new Ole Db Data Adapter("SELECT * From " table, conn); Ole Db Command Builder cmd Builder = new Ole Db Command Builder(adapter); adapter. Format("INSERT INTO ([GUID], [field Name]) Values (@guid,@field Name);", table), conn); Data Table data Table = new Data Table(table); adapter. New Row(); row [ field Name ] = field Value; // eg: row [ "GUID" ] = System.
This page also states that I should be using a Ole Db Adapter and Data Set, which should solve the problem. The Ole Db Command Builder, when it creates its own Insert Command, doesn't surround the names with brackets, so a solution is to manually define the Ole Db Data Adapter's Insert Command: Ole Db Connection conn = new Ole Db Connection("Provider=Microsoft.
Can anyone see anything wrong or something I've missed out that would cause the code not to function as desired?
OLEDB.4.0; Data Source=C:\Users\Joe\Documents\Visual Studio 2012\Projects\school database viewer\school database viewer\db School Database.mdb" con.