Update SQL command C#

Question

I'm trying to update a database from my program in C#.

Here is my code which connects to the database and the attempts to update the date column in my RoomsTable table. It looks good to me but nothing happens in the database.

updateConnection = new System.Data.OleDb.OleDbConnection();
updateConnection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\users\spreston\documents\visual studio 2012\Projects\roomChecksProgram\roomChecksProgram\roomsBase.accdb";
updateConnection.Open();

MessageBox.Show("Connected");

string updateCommand = "UPDATE RoomsTable SET Date Checked='9/27/2012'";
updateAdapter = new OleDbDataAdapter(updateCommand, updateConnection);

updateConnection.Close();
updateConnection.Dispose();

I don't know why it isn't working. It looks to me like everything is there.

You need to run an execute() method.

juergen d
– juergen d

2012-09-30 15:57:09 +00:00
Commented Sep 30, 2012 at 15:57 — juergen d
– juergen d, Commented Sep 30, 2012 at 15:57

John Woo · Accepted Answer · 2012-09-30 16:28:10Z

9

use OleDBCommand

string updateCommand = "UPDATE RoomsTable SET [Date Checked]='9/27/2012'";
updateCommand = new OleDbCommand(updateCommand, updateConnection);

updateCommand.ExecuteNonQuery();
updateConnection.Close();

maybe you could refractor the code using Using statement and parameterized the query. and column name Date Checked should be escaped with brackets.

string updateCommand = "UPDATE RoomsTable SET [Date Checked]=@checkedDate WHERE ID = @id"; // '9/27/2012'
using (OleDbConnection conn = new OleDbConnection("connectionStringHERE"))
{
    using (OleDbCommand comm = new OleDbCommand())
    {
        comm.Connection = conn;
        comm.CommandText = updateCommand;
        comm.CommandType = CommandType.Text
        comm.Parameters.AddWithValue("@checkedDate", this.dateTimePicker1.Value)
        comm.Parameters.AddWithValue("@id", row.roomID);
        try
        {
            comm.Open();
            conn.ExecuteNonQuery();
        }
        catch(OleDbException ex)
        {
            MessageBox.Show(ex.Message.ToString());
        }
    }
}

edited Sep 30, 2012 at 16:28

answered Sep 30, 2012 at 15:58

John Woo

265k70 gold badges508 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

16 Comments

Stonep123 Over a year ago

hmm yeah I was following a tutorial I found on the web..(i have no database experience) and it used the method I used. It was for an access databse and everything which is what im using.

Stonep123 Over a year ago

I tried your method and im getting errors ("Cannot implicitly convert type 'System.Data.OleDB.OleDBCommand' to string) and ('string' does not contain a definition for 'ExecuteNonQuery'

Stonep123 Over a year ago

and I do have using System.Data.OldeDb in my code at the top.

Stonep123 Over a year ago

ok i got those errors solved, but now when i run it crashes and the error says there is an error in my update statement whenever .executenonquert is called

John Woo Over a year ago

@Stonep123 where is this 9/27/2012 coming from?

|

LarsTech · Accepted Answer · 2014-10-15 16:54:16Z

0

you have to write this:

updateConnection = new System.Data.OleDb.OleDbConnection();
updateConnection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\users\spreston\documents\visual studio 2012\Projects\roomChecksProgram\roomChecksProgram\roomsBase.accdb";

updateConnection.Open();

MessageBox.Show("Connected");

string updateCommand = "UPDATE RoomsTable SET Date Checked= '"+9/27/2012+"' ";

updateAdapter = new OleDbDataAdapter(updateCommand, updateConnection);

updateConnection.Close();
updateConnection.Dispose();

edited Oct 15, 2014 at 16:54

LarsTech

81.8k14 gold badges161 silver badges237 bronze badges

answered Oct 15, 2014 at 16:46

user4146540

1

1 Comment

LarsTech Over a year ago

Always use parameters in your queries to avoid sql injection. Your updateCommand string isn't what you think it is.

Collectives™ on Stack Overflow

Update SQL command C#

2 Answers 2

16 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

16 Comments

1 Comment

Related