Trouble writing to database in C# WinForm Application

Accepted answer +7 upvotes

There are many problems in your code above.

• First, a command should be executed, not simply declared. (this is why the database is not being modified)
• Second, you use reserved keywords in your statement (so even if you execute your statement, it will fail and throw an exception)
• Third, you are concatenating strings to build the command text. A very bad move that would leave your application susceptible to SQL injection attacks
• Fourth, the connection should be closed after usage

Let me try to write a replacement code

string cmdText = "Insert INTO RetentionTable " +
                "([DateTime],Center,CSP,MemberID,ContractNumber,RetentionType," + 
                "RetentionTrigger,MemberReason,ActionTaken,Other) " + 
                "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
 using(OleDbConnection cn = new OleDbConnection(conString))
 using(OleDbCommand cmd = new OleDbCommand(cmdText, cn))
 {
    cmd.Parameters.AddWithValue("@p1", DateTime.Now.ToString());
    cmd.Parameters.AddWithValue("@p2", GetCenter("")); 
    cmd.Parameters.AddWithValue("@p3", GetName(""));
    cmd.Parameters.AddWithValue("@p4", GetMemberID(""));
    cmd.Parameters.AddWithValue("@p5", GetContractNumber(""));
    cmd.Parameters.AddWithValue("@p6", GetType("")); 
    cmd.Parameters.AddWithValue("@p7", GetTrigger(""));
    cmd.Parameters.AddWithValue("@p8", GetReason(""));
    cmd.Parameters.AddWithValue("@p9", GetAction(""));
    cmd.Parameters.AddWithValue("@p10", GetOther(""));
    cmd.ExecuteNonQuery();
 }

The DATETIME is a reserved keyword in Access and thus, if you want to use it for your column names then you need to enclose it in square brackets.

The string concatenation is a bad practice in MSAccess but it is a fatal flaw in other database where your code could be used for Sql Injections (more difficult in Access but not impossible). If you use a parameterized query as in my example, you remove the Sql Injection problem, but also you let the framework code to pass the correct value to the database engine with the correct formatting required for dates, strings and decimals.

Another point to consider is to not have a global OleDbConnection object, but create, use and destroy the object whenever your need it. The Connection Pooling will avoid performance problems and your code will not suffer from memory leaks when a connection fails for whatever reason

I want also add that your GetXXXXX methods seems all to return strings. Remember that these methods should return values compatible with the underlying database field where you want to write