Hi everyone,
please i am writing a program in C# for connecting to a microsoft access database using visual studio 2005 and ADO.NET. i want to be able to use Dataset to update,delete,insert and edit records in the database.please how do i go about this.I already have aform with a list box,a text box and four buttons for inserting,updating,deleting, and inserting records but i havent been able to get the connection to the database. i am a little bit confused if have to use a connection string and a dataAdapter to get a connection to the database everytime i want to perform each action like updating, deleting e.t.c and please how do i write the cade for the update,delete,edit and insert statements as i am a little bit new to ADO.NET and c# platform.below is the code i have written so far but it dosent work. can anyone please help me in modifying/correcting the codes.the the name of my access database is BookCSharp with a table name Books.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Data.OleDb;
using System.Windows.Forms;

namespace DBConnection
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }


        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void button5_Click(object sender, EventArgs e)
        {

        }

        private void button4_Click(object sender, EventArgs e)
        {

        }

        private void button1_Click(object sender, EventArgs e)
        {
                string dbconnection ="Provider=Microsoft.Jet.OLEDB.4.0;" +@"data source=BookCSharp.mdb";

                string dbcommand = "INSERT into Books (Title, Pages, AuthorBook) " + " VALUES ('C# good','300',OmosJigga)";

                OleDbDataAdapter DataAdapterTest = new OleDbDataAdapter (dbcommand, dbconnection);

                DataSet BooksDataSet = new DataSet();

                DataAdapterTest. Fill(BooksDataSet);

                DataTable dtBooks = BooksDataSet.Tables[0];
        }

        private void button2_Click(object sender, EventArgs e)
        {
            string dbconnection2 = "Provider=Microsoft.Jet.OLEDB.4.0;" + @"data source=BookCSharp.mdb";
            string command2 = "UPDATE Books SET Title = 'Omos' WHERE Publisher = 'O,Rielly'";
            OleDbDataAdapter Data2 = new OleDbDataAdapter(command2,dbconnection2);
            DataSet b = new DataSet();
            Data2.Fill(b);
        }

        private void button3_Click(object sender, EventArgs e)
        {
            string dbconnection1 = "Provider=Microsoft.Jet.OLEDB.4.0;" +@"data source=BookCSharp.mdb";
            string command1 = "Delete from Books where (Title = ? AND Pages = ? AND AuthorKey = ? AND Publisher = ?)";
            OleDbDataAdapter Data1 = new OleDbDataAdapter(command1,dbconnection1);
            DataSet a = new DataSet();
            Data1.Fill(a);
            DataTable aBook = a.Tables[0];

        }

        private void textBox1_TextChanged(object sender, EventArgs e)
        {

        }

    }
}

thanks.

Edited 3 Years Ago by happygeek: fixed formatting

Hi omoz4real.
In your code,i found that you hadn't opened your connection before executed a command,so you probably couldn't connect to the database.
If you didn't intend to execute a command which return a table as a result,you should use another way.There is xxCommand.ExecuteNonQuery().
I have a small attached program that clear your confuse.
Hope this useful.

Ok, I give u details or a road map about ADO.Net I think it's interessant to know that from at first.
At contrast of the ADO plus where the connected mode is only connected , In the ADO.NET there are two modes, namely, the connected mode and the disconnected mode. In the first mode we use the connection and the Datareader, those two objects provide us a direct connection to the database. The data reader can only read data by moving forward you can make direct update only by using the ExecuteNonQuery method of the command objet. The second way I mean the disconnected use a data connection dataAdapter and a Dataset
When the connection is established and the method Fill of the dataAdpter is called the last one fill all desired data from data base in the dataset wirch is an xml data representation
then the connection is closed. The Ado dot net is using this technic to enhance the securities connections and to optimise the situation in a multi connection environnement. because the number of connection can decrease the application performances.
By the way a thing you have a mistake when writting the connection string you forgot the root C:\dataBaseName.mdb instead of dataBaseName.mdb in data source

Edited 3 Years Ago by pyTony: fixed formatting (removed unnecessary quote)

Yes.
I agree with Jugortha about two connection mode.And i also agree with him about what doing for each connected type.I had noticed these details in my attached program(readme.txt).
Thank for make it clearly.

Just stumbled upon this old thread.

My thanks for the example attachment program.
I have been searching for so long.

Thanks

>BstrucT

when i try to inert the data to access from vb it will throw an exception " Data type mismatch in criteria expression" this exception wiill thrown in the line " DataAdapterTest.Fill(BooksDataSet)" whats the solution for this how to overcomethis problem please help me..

when i try to inert the data to access from vb it will throw an exception " Data type mismatch in criteria expression" this exception wiill thrown in the line " DataAdapterTest.Fill(BooksDataSet)" whats the solution for this how to overcomethis problem please help me..

[plz check your database table you must be inserting value of data type different as in your table for example my table contain username as data type text and I am entering number as the value of username it shows the data type mismatch

hello ive got related problem is it ok topost this here
my code is

  Dim ConString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=..\systemsdb.mdb"

        conn = New OleDbConnection(ConString)
        Try
            conn.Open()

            str = "Update tbllogin where UserID = " & cbidmod.Text & "set AccessKey = '" & txtresetpassmod.Text & "'"


            '  str = "Select * from tbllogin where UserID =" & cbidmod.Text
            cmd = New OleDbCommand(str, conn)
            cmd.ExecuteNonQuery()
            'conn.Close()
            MsgBox("Record Updated Successfully!")
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

ive got error on update statement... however when i write it like

str = "Update tbllogin set AccessKey = '" & txtresetpassmod.Text & "'"
it works fine but it changes all item on my collumn AccessKey.
my error is missing operatorcan you guys helpme out

Hey, a new thread might have been better but never mind.
An update statement requires a WHERE clause otherwise it will update all of the selected column.

UPDATE table1 SET col1 = 'Some value';`

will update all of the columns col1 so the rows read 'some value'. The WHERE clause explains which columns to update and is a very important part of the SQL statement.
In you posted code you have the WHERE clause before the SET clause which is probably the cause of the problem.

Firstly Thank you, Your code is very useful! However...... I could use a little help on a problem please. I have used the button1_click method above abut the code I am trying to insert is HTML as a string.......I receive " Syntax error (missing operator) in query expression ''<table>BLAHBLAHBLAH ". The HTML is the body of a page where another program uses the same DB to read from. The programs DO NOT RUN at the same time.

Any help would be much appreciated!

This article has been dead for over six months. Start a new discussion instead.