?
Solved

select and update query

Posted on 2011-05-03
4
Medium Priority
?
216 Views
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)
        {
            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

0
Comment
  • 2
4 Comments
 
LVL 13

Expert Comment

by:dwkor
ID: 35517048
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
 
LVL 13

Accepted Solution

by:
dwkor earned 1000 total points
ID: 35517076
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
 
LVL 61

Assisted Solution

by:HainKurt
HainKurt earned 1000 total points
ID: 35517106
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
 

Author Comment

by:ProgrammingRulesTheWorld
ID: 35518130
Yeah the double execution was causing the problem thanks for the help
0

Featured Post

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

839 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