how to update a table using oledb parameters?

by

This annoyed me, screwy little OleDB, so I’ll post my solution here for posterity. It’s an old post but seems like a good place.

OleDB doesn’t recognize named parameters, but it apparently does recognize that you’re trying to convey a named parameter, so you can use that to your advantage and make your SQL semantic and easier to understand. So long as they’re passed in the same order, it’ll accept a variable as a named parameter.

I used this to update a simple Access database in a network folder.

 using (OleDbConnection conn = new OleDbConnection(connString))
 {
       conn.Open();
       OleDbCommand cmd = conn.CreateCommand();

       for (int i = 0; i < Customers.Count; i++)
       {
            cmd.Parameters.Add(new OleDbParameter("@var1", Customer[i].Name))
            cmd.Parameters.Add(new OleDbParameter("@var2", Customer[i].PhoneNum))
            cmd.Parameters.Add(new OleDbParameter("@var3", Customer[i].ID))
            cmd.Parameters.Add(new OleDbParameter("@var4", Customer[i].Name))
            cmd.Parameters.Add(new OleDbParameter("@var5", Customer[i].PhoneNum))

            cmd.CommandText = "UPDATE Customers SET Name=@var1, Phone=@var2" + 
                              "WHERE ID=@var3 AND (Name<>@var4 OR Phone<>@var5)";
            cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
       }
 }

It may look like an excess of code, and yes you’re technically repeating yourself, but this makes it worlds easier when you’re playing connect-the-dots later on…..

Leave a Comment

tech