none
sql server if the row exist it update if the row new won't update

    Question

  • I am
    working with Asp.net and C# and using SQl Server, I insert a row in one plase and works fine but when I try to update the same row it woudn't do anything, I
    try to update an existing row it works with the same code but not if I just
    insert new row.

    my code as
    below:

    String companyName = txCompany.Text.ToString();

            SqlConnection con = new SqlConnection(conString);

            SqlCommand cmd = new SqlCommand("INSERT INTO DocUp (CompanyName)VALUES (@CompanyName)", con);

            cmd.Parameters.AddWithValue("@CompanyName", companyName);

          

             try

            {

                con.Open();

                cmd.ExecuteNonQuery();

            }

            catch (Exception er)

            {

                Response.Write("<script language='javascript'>alert('Connection Problem On Insert');</script>");

            }

            finally

            {

                con.Close();

            }

    string strQuery = "UPDATE DocUp SET  QuoteFileName=@QuoteFileName, ContentType=@ContentType, QuoteFileData=@Data WHERE CompanyName=@Company";

                    SqlCommand cmd = new SqlCommand(strQuery);

                    cmd.Parameters.Add("@QuoteFileName", SqlDbType.VarChar).Value = filename;

                    cmd.Parameters.Add("@ContentType", SqlDbType.VarChar).Value = "application/pdf";

                    cmd.Parameters.Add("@Data", SqlDbType.Binary).Value = bytes;

                    cmd.Parameters.Add("@Company", SqlDbType.VarChar).Value = companyName;

                    InsertUpdateData(cmd);

    private Boolean InsertUpdateData(SqlCommand cmd)

        {

            String strConnString = System.Configuration.ConfigurationManager

            .ConnectionStrings["S7V001_11022014ConnectionString1"].ConnectionString;

            SqlConnection con = new SqlConnection(strConnString);

            cmd.CommandType = CommandType.Text;

            cmd.Connection = con;

            try

            {

                con.Open();

                cmd.ExecuteNonQuery();

                return true;

            }

            catch (Exception ex)

            {

                Response.Write(ex.Message);

                return false;

            }

            finally

            {

                con.Close();

                con.Dispose();

            }

        }

    • Edited by Dhrgam Thursday, February 20, 2014 5:18 PM
    Thursday, February 20, 2014 4:24 PM

Answers

  • My response is I wouldn't. Don't run updates from applications, they're slow on performance, security holes are rife and overall a pain to implement correctly.

    A much easier way is to timestamp inserts and then use your client application to retrieve data back from the server ordered by the most recent entry.

    Think of it book of selfie photographs that you take of yourself each day, you want to see the current state of your face you look at the most recent one. If you develop a spot on your face (an event), then this is captured with the next photograph, along with the rest of your face. (A very strange analogy but I hope you get my point).

    To sum it up in a sentence, "Leave the application layer to display and format data in an orderly manner, leave the database to store data and any changes to data." This is the key to a good, secure application that is easily debuggable.

    Thanks.

    Thursday, February 20, 2014 5:26 PM

All replies

  • Please use the  "Insert code block" button to insert *readable* code blocks 

    try{
    
    con.Open();
    cmd.ExecuteNonQuery();
    return true;
    
    }

    You have not shared what query/functions that you are using to insert data. Please share. 

    Also it looks like more like a .Net related questions, (and if you agree,  ) I would recommend posting the question in .Net forum

    http://social.msdn.microsoft.com/Forums/vstudio/en-US/home?forum=csharpgeneral

    Also I would recommend you to read "How to ask questions in Technical Forums" from the below link


    Satheesh
    My Blog | How to ask questions in technical forum



    Thursday, February 20, 2014 4:36 PM
  • I can just see an update query in posted part. How are doing the insert? check if parameters are getting correct values.

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Thursday, February 20, 2014 4:43 PM
  • Use stored procedures for INSERT & UPDATE.

    Big advantage of stored procedures is that they are server-side objects. Can be developed and tested without the client application.

    Also, take a look at the MERGE command which does INSERT, UPDATE & DELETE in a single (big) statement.

    Stored procedure examples:

    https://www.google.com/#q=stored+procedure+site:+sqlusa.com


    Kalman Toth Database & OLAP Architect SELECT Video Tutorials 4 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012




    Thursday, February 20, 2014 4:43 PM
  • I just updated my question and included the insert part. Thanks for the help
    Thursday, February 20, 2014 5:19 PM
  • When you declared your varchar parameters, I suggest to explicitly specify size as well (third parameter)

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, February 20, 2014 5:21 PM
  • Do you get an error with your code? Can you also trace your code using SQL Server Profiler to verify which command is send to SQL Server?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, February 20, 2014 5:23 PM
  • My response is I wouldn't. Don't run updates from applications, they're slow on performance, security holes are rife and overall a pain to implement correctly.

    A much easier way is to timestamp inserts and then use your client application to retrieve data back from the server ordered by the most recent entry.

    Think of it book of selfie photographs that you take of yourself each day, you want to see the current state of your face you look at the most recent one. If you develop a spot on your face (an event), then this is captured with the next photograph, along with the rest of your face. (A very strange analogy but I hope you get my point).

    To sum it up in a sentence, "Leave the application layer to display and format data in an orderly manner, leave the database to store data and any changes to data." This is the key to a good, secure application that is easily debuggable.

    Thanks.

    Thursday, February 20, 2014 5:26 PM