select and update query

Posted on 2011-05-03
Last Modified: 2012-05-11
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)
                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));

                if ((int)selectCommand.ExecuteScalar() > 0)
                    selectCommand.CommandText = "UPDATE Books " +
                                                 "SET Title = @Title, " +
                                                 "Author= @Author, " +
                                                 "ISBNNumber = @ISBNNumber, " +
                                                "Description = @Description " +
                                                "WHERE ISBNNumber = @ISBNNumber ";
                    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));
            catch (SqlCeException ex)
                throw ex;          

            return b;

Open in new window

    LVL 13

    Expert Comment

    Use MERGE ( Check example A from URL I posted. It does exactly what you need.

    LVL 13

    Accepted Solution

    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

    LVL 51

    Assisted Solution

    why you are doing this twice?


    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...

    Author Comment

    Yeah the double execution was causing the problem thanks for the help

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Suggested Solutions

    How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
    Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now