Edit using c# and ms access

Question

i have a problem, i tried this code :

    private void button1_Click(object sender, RoutedEventArgs e)
    {
        conn.Open();
        OleDbCommand cmd = new OleDbCommand("UPDATE tbl_Fullname SET Firstname=@firstn,Lastname=@lastn,Middlename=@midn WHERE fnID=@idn", conn);

        cmd.Parameters.Add("@idn", OleDbType.VarChar).Value = textBox5.Text;
        cmd.Parameters.Add("@firstn", OleDbType.VarChar).Value = textBox1.Text;
        cmd.Parameters.Add("@lastn", OleDbType.VarChar).Value = textBox2.Text;
        cmd.Parameters.Add("@midn", OleDbType.VarChar).Value = textBox3.Text;
        cmd.ExecuteNonQuery();
        conn.Close();
    }

there was no error but when i ckecked my db access there is nothing changed.

is this the exact code for updatint and editing?

here is my whole code:

    private void Window_Loaded(object sender, RoutedEventArgs e)
    {

    }

    private void button3_Click(object sender, RoutedEventArgs e)
    {

        OleDbCommand cmd = new OleDbCommand("INSERT INTO tbl_Fullname (Lastname,Firstname,Middlename) VALUES (@first,@last,@midn)",conn);

        conn.Open();
        cmd.Parameters.Add("@first", OleDbType.VarChar).Value = textBox1.Text;
        cmd.Parameters.Add("@last", OleDbType.VarChar).Value = textBox2.Text;
        cmd.Parameters.Add("@midn", OleDbType.VarChar).Value = textBox3.Text;
        cmd.ExecuteNonQuery();
        conn.Close();
    }

    private void textBox4_TextChanged(object sender, TextChangedEventArgs e)
    {
        conn.Open();
        OleDbCommand cmd2 = new OleDbCommand("SELECT fnID,Lastname,Firstname,Middlename FROM tbl_Fullname WHERE fnID= @id", conn);


        cmd2.Parameters.Add("@id", OleDbType.VarChar).Value = textBox4.Text;
        try
        {
            OleDbDataReader dr = cmd2.ExecuteReader();
            if (dr.Read())
            {
                textBox1.Text = dr[1].ToString();
                textBox2.Text = dr[2].ToString();
                textBox3.Text = dr[3].ToString();
                textBox5.Text = dr[0].ToString();

            }
            else
            {
                MessageBox.Show("No result");
            }

        }
        catch (Exception ex)
        {


            MessageBox.Show(ex.Message);
        }
        conn.Close();

    }

    private void button1_Click(object sender, RoutedEventArgs e)
    {
        conn.Open();
        OleDbCommand cmd = new OleDbCommand("UPDATE tbl_Fullname SET Firstname=@firstn,Lastname=@lastn,Middlename=@midn WHERE fnID=@idn", conn);

        cmd.Parameters.Add("@idn", OleDbType.VarChar).Value = textBox5.Text;
        cmd.Parameters.Add("@firstn", OleDbType.VarChar).Value = textBox1.Text;
        cmd.Parameters.Add("@lastn", OleDbType.VarChar).Value = textBox2.Text;
        cmd.Parameters.Add("@midn", OleDbType.VarChar).Value = textBox3.Text;
        cmd.ExecuteNonQuery();
        conn.Close();
    }
}

}

i hope someone can help me. i need it badly. thank you in advance.

What does your connection string look like? Are you sure that the code is running, i.e., you have the event properly wired up? You can test by doing something obvious like displaying a pop-up message. — mellamokb
– mellamokb, Commented Oct 7, 2011 at 5:26

Community · Accepted Answer · 2020-06-20 09:12:55Z

MSDN says:

The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used. For example:

SELECT * FROM Customers WHERE CustomerID = ?

Therefore, the order in which OleDbParameter objects are added to the OleDbParameterCollection must directly correspond to the position of the question mark placeholder for the parameter in the command text.

So make it that way:

 private void button1_Click(object sender, RoutedEventArgs e)
    {
        conn.Open();
        OleDbCommand cmd = new OleDbCommand("UPDATE tbl_Fullname SET Firstname=?,Lastname=?,Middlename=? WHERE fnID=?", conn);

        cmd.Parameters.Add("@firstn", OleDbType.VarChar).Value = textBox1.Text;
        cmd.Parameters.Add("@lastn", OleDbType.VarChar).Value = textBox2.Text;
        cmd.Parameters.Add("@midn", OleDbType.VarChar).Value = textBox3.Text;
        cmd.Parameters.Add("@idn", OleDbType.VarChar).Value = textBox5.Text;
        cmd.ExecuteNonQuery();
        conn.Close();
    }

Note that order of parameters is important here!

YvesR · Accepted Answer · 2011-10-07 05:29:05Z

0

Add a try/catch around your SQL and output the sqlCommand Exeption.

Also see this link http://idealprogrammer.com/net-languages/code-samples/sql-command-update-statement-source-code/ I found where you can see how to insert,update,etc. properly.

answered Oct 7, 2011 at 5:29

YvesR

6,2829 gold badges46 silver badges75 bronze badges

Collectives™ on Stack Overflow

Edit using c# and ms access

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related