adding rows...

[bords]

Hello everyone...can anyone modify my code below....i have a database named "Database1.accdb"
with a table "Applicants"...The columns are ID(auto incremented),Name,and Age....
on this code, i can display the data from my database...
My problem is that how can i add a new record to my database...or delete a record...
I have read tutorials and code snippets here but i want a specific code...please help me guys...
On my designer form, i have dragged the Applicants node so in my form there is now the IDtextbox,
NameTextbox, and AgeTextbox...i have also added AddButton, DeleteButton, and UpdateButton...
How to write a code in which when i click the AddButtton, the values in the textboxes will
be added to my database...and when i delete then a record will be deleted....Please include
the namespace...or anything that needs to be declared....
By d way, im using MS Access 2007...Tanx guys...Any help will be greatly appreciated...

using System.Data.OleDb;

        private void button1_Click(object sender, EventArgs e)
        { 
	    const string connStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Bords\MyDocuments\Database1.accdb";
            string query1 = "Select * From Applicants"; 

            using (OleDbConnection conn = new OleDbConnection(connStr))
            {
                conn.Open();
                using (OleDbCommand cmd = new OleDbCommand(query1, conn))
                {
                    using (OleDbDataReader dr = cmd.ExecuteReader())
                    {
                        if (dt != null)
                        dt.Dispose();
                        dt = new DataTable();
                        dt.Load(dr);
                        dataGridView1.DataSource = dt;
                    }
                }
                conn.Close();
            }
        }

[Ionelul]

Hi bords,

To delete a row :

const string connStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Bords\MyDocuments\Database1.accdb";            
string query1 = "DELETE From Applicants where Id = " + IdTextBox.Text;             
 using (OleDbConnection conn = new OleDbConnection(connStr))            
{                
        conn.Open();                
        using (OleDbCommand cmd = new OleDbCommand(query1, conn))                
              {
                  cmd.ExecuteNonQuery();
              }
         conn.Close();            
 }

To insert a new row:

string SqlString = "INSERT INTO Applicants VALUES (";
SqlString += IdTextbox.Text  + ", "; //in your case, comment this line because you have autoincrement on Id column
SqlString += " '" + NameTextbox.Text + "' " + " , ";
SqlString += " '" + AgeTextBox.Text + "' " + " ) ";
using (OleDbConnection conn = new OleDbConnection(connStr))            
{                
        conn.Open();                
        using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))                
              {
                  cmd.ExecuteNonQuery();
              }
        conn.Close();            
 }

to update a row:

string SqlString = "Update Applicants set";
SqlString += "Name =" + " '" + NameTextbox.Text + "' " + " , ";
SqlString +="Age = " + " '" + AgeTextBox.Text + "' " ;
SqlString += "WHERE Id = " + IdTextbox.Text;
using (OleDbConnection conn = new OleDbConnection(connStr))            
{                
        conn.Open();                
        using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))                
              {
                  cmd.ExecuteNonQuery();
              }
         conn.Close();            
 }

Cheers,
Ionut

[sknake]

You should consider using parameterized SQL. Take a look at this thread:
http://www.daniweb.com/forums/thread248513.html
More threads: http://www.google.com/search?hl=en&source=hp&q=site%3Adaniweb.com+%2Bsknake+%2Boledbparameter&aq=f&oq=&aqi=

Code:

const string connStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Bords\MyDocuments\Database1.accdb";
      const string query = "DELETE From Applicants where Id = ?";
      using (OleDbConnection conn = new OleDbConnection(connStr))
      {
        conn.Open();
        using (OleDbCommand cmd = new OleDbCommand(query, conn))
        {
          cmd.Parameters.Add(new OleDbParameter("ApplicationId", Convert.ToInt32(IdTextBox.Text)));
          cmd.ExecuteNonQuery();
        }
        conn.Close();
      }

[privatevoid]

Yep you really should use parameterised querys instead of directly inserting arguments into strings! See security issues regarding SQL injection etc.

I tend to use somthing like this:

public DataSet Query(string sql, params SqlParameter[] args)
{
    try
    {
        SqlConnection connection = new SqlConnection(this._connection);
        SqlCommand command = new SqlCommand(sql, connection);
        if (args != null) command.Parameters.AddRange(args);
        SqlDataAdapter adapter = new SqlDataAdapter(command);
        DataSet returnData = new DataSet();
        adapter.Fill(returnData);
                
         adapter.Dispose();
         command.Dispose();
         connection.Dispose();

         return returnData;
    }
    catch (Exception ex)
    {
         //...
     }
}


public void SetName(int id, string name)
{
    string sql = "UPDATE dbo.Sites SET siteName= @name \n" 
                       + "WHERE siteId = @id;";

    Query(sql, new SqlParameter("@id", id)
              , new SqlParameter("@name", name));
}

[bords]

its solved....thanx really a lot guys...
youre so helpful eversince i joined here....