Need help to update records into the database

I am writing a console app to insert and update records from a file  into a database. I am able to insert but not sure on updating the exiting records. code below:

string sql = "INSERT INTO sc_lienholder(lienholder_code,name,address,address2,city,state,zipcode,elt_status,is_active,insert_dtim,insert_person) VALUES(@lienholder_code,@name,@address,@address2,@city,@state,@zipcode,@elt_status,@is_active,@insert_dtim,@insert_person)";
 using (SqlCommand cmd = new SqlCommand(sql, cn))
                    {
                        //cn.Open();
                        for (int index = 0; index < list.Count; index++)
                            try
                            {
                                string[] items = list[index].Substring(53).Split(new char[] { ',' });
                                string item = list[index].Substring(0, 51);
                                cmd.Parameters.AddWithValue("@name", item.Replace("'", ""));
                                cmd.Parameters.AddWithValue("@address", items[4]);
                                cmd.Parameters.AddWithValue("@address2", items[5]);
                                cmd.Parameters.AddWithValue("@city", items[6]);
                                cmd.Parameters.AddWithValue("@state", items[7]);
                                cmd.Parameters.AddWithValue("@zipcode", items[8]);
                                cmd.Parameters.AddWithValue("@lienholder_code", items[9].Trim()); //sf SC-77 03/09/2015
                                if (items[10].Trim() == "E") //sf SC-77 03/23/2015
                                {
                                    cmd.Parameters.AddWithValue("@elt_status", "Y"); //sf SC-77 03/23/2015
                                }
                                else
                                {
                                    cmd.Parameters.AddWithValue("@elt_status", "N"); //sf SC-77 03/23/2015
                                }
                                cmd.Parameters.AddWithValue("@is_active", "Y");
                                cmd.Parameters.AddWithValue("@insert_dtim", DateTime.Now);
                                cmd.Parameters.AddWithValue("@insert_person", "Batch Job");
                                cmd.ExecuteNonQuery();
                                cmd.Parameters.Clear();
                                Logger.Instance.LogEvent(String.Format("{0} lienholders copied", index));
                            }
                            catch (Exception ex)
                            {
                                cmd.Parameters.Clear();
                                Logger.Instance.LogException(String.Format("Exception: {0}", ex.Message), ex);
                                Emailer.SendMail(ex.Message, "Error in South Carolina Lien Console Application");
                            }
MeinhoonaaAsked:
Who is Participating?
 
Walter RitzelSenior Software EngineerCommented:
The code will need to have some changes:
1) You will need to create the update statement in the same way you have created the insert: UPDATE <table name> SET <field1> = @field1, ... WHERE <field key> = @value;
2) You will need to identify your primary key;
3) for each line read from file, you will need to query the database to check if it exists. If it not exists, use your insert command; if it exists, use your update command.
0
 
MeinhoonaaAuthor Commented:
how do I do that? need example. I know the theory.
0
 
MeinhoonaaAuthor Commented:
I can take care of update statement. Need help with item # 3.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
MeinhoonaaAuthor Commented:
string sqlUpdate = "UPDATE  sc_lienholder SET name= @name, address = @address, address2 = @address2, city  = @city, state = @state, zipcode = @zipcode, elt_status = @elt_status, is_active = @is_active, where lienholder_code = @lienholder_code";
0
 
MeinhoonaaAuthor Commented:
for some reason if exist query not working.

string sqlcheck = "select * FROM sc_lienholder WHERE lienholder_code = @lienholder_code";
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Your UPDATE statement looks good to me.
You don't need to check before since the UPDATE query has the WHERE clause so it only update the records  with the criteria lienholder_code = @lienholder_code.
0
 
Walter RitzelSenior Software EngineerCommented:
Check SQL seems ok to me. Please post your full code for us to analyze.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.