select and update query

I am using Visual Studio 2008 in windows forms with SQL CE edition.
The query below for some reason isn;t working properly. I want it to search to see if the book exists , if it does through the isbn number which should be enough since isbns are unique  to update the book to the title, author, description that may have been changed. If the book does not exist in the Database add it to it. It would sometimes work If i had ISBNNumber a primary key (it is not right now) I would like it to be but whenever I enter a book I get an exception saying I am entering another item with the same ISBN which makes sense to my problem above.. So any help to fix this issue would be great...
public static Book addOrUpdateBook(Book b)
        {
            try
            {
                using (SqlCeConnection con = BookStoreConnectionString.GetConnection())
            {
            using (SqlCeCommand selectCommand = new SqlCeCommand(string.Empty, con))
            {
                selectCommand.CommandText = "SELECT COUNT(1) FROM Books WHERE ISBNNumber = @ISBNNumber";
                selectCommand.Parameters.Add(new SqlCeParameter("@ISBNNumber", b.ISBNNumber));

                con.Open();
                
                if ((int)selectCommand.ExecuteScalar() > 0)
                {
                    selectCommand.CommandText = "UPDATE Books " +
                                                 "SET Title = @Title, " +
                                                 "Author= @Author, " +
                                                 "ISBNNumber = @ISBNNumber, " +
                                                "Description = @Description " +
                                                "WHERE ISBNNumber = @ISBNNumber ";
                }
                else
                {
                    selectCommand.CommandText = "INSERT INTO Books (ISBNNumber, Title, Author, Description) " +
                                                "VALUES (@ISBNNumber, @Title, @Author, @Description)";
                
                }
                
                selectCommand.Parameters.Add(new SqlCeParameter("@Title", b.Title));
                selectCommand.Parameters.Add(new SqlCeParameter("@Author", b.Author));
                selectCommand.Parameters.Add(new SqlCeParameter("@Description", b.Description));
                
                selectCommand.ExecuteNonQuery();
                      selectCommand.ExecuteNonQuery();
                    }
                }
            }
            catch (SqlCeException ex)
            {
      
                throw ex;          
            }

            return b;
        }
      }
   }

Open in new window

ProgrammingRulesTheWorldAsked:
Who is Participating?
 
dwkorConnect With a Mentor Commented:
Please disregard my advice about MERGE. I did not notice that you're using CE Edition.

Your code looks OK (not optimal but OK). The reason for the error could be duplicated call of ExecuteNonQuery(). Try to remove it.

Besides that you can try something like below. Not sure if it works in CE though.
selectCommand.CommandText = @"
UPDATE Books 
SET Title = @Title, Author= @Author, ISBNNumber = @ISBNNumber, Description = @Description WHERE ISBNNumber = @ISBNNumber 

if @@rowcount = 0
	INSERT INTO Books (ISBNNumber, Title, Author, Description) VALUES (@ISBNNumber, @Title, @Author, @Description)";

Open in new window

0
 
dwkorCommented:
Use MERGE (http://technet.microsoft.com/en-us/library/bb510625.aspx). Check example A from URL I posted. It does exactly what you need.

0
 
HainKurtConnect With a Mentor Sr. System AnalystCommented:
why you are doing this twice?

                selectCommand.ExecuteNonQuery();
                      selectCommand.ExecuteNonQuery();

also, I suggest create a sp with 4 parameter, and put this logic into sp
then from your app just call sp with 4 parameter, sp will take care of insert/update...

by the way what does "not working" means? I could not get which part is not working here, code seems ok except double execution...
0
 
ProgrammingRulesTheWorldAuthor Commented:
Yeah the double execution was causing the problem thanks for the help
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.