How to update data if it already exist via ASP.NET visual studio 2017

Question

1.00/5 (2 votes)

See more:

I am developing a web application via asp.net. I want to update the data if already exist otherwise insert. I used the below code. but the update statement is not working.

What I have tried:

MySqlCommand cmdCount = new MySqlCommand("SELECT count(*) from travel WHERE  clientid='" + Session["clientid"] + "' and clientida='" + Session["clientida"] + "' ", con);
            con.Open();
            int count = Convert.ToInt16(cmdCount.ExecuteScalar());
            if (count > 0)
            {
                // UPDATE STATEMENT     

                MySqlCommand updCommand = new MySqlCommand("UPDATE travel SET Tcountry =@Tcountry,TArrivalDate=@TArrivalDate,TDepartureDate=@TDepartureDate,TReasonofTravel=@TReasonofTravel WHERE  clientid='" + Session["clientid"] + "' and clientida='" + Session["clientida"] + "' ", con);
                updCommand.Parameters.AddWithValue("@Tcountry", DropDownList1.Text);
                updCommand.Parameters.AddWithValue("@TArrivalDate", TextBox1.Text);
                updCommand.Parameters.AddWithValue("@TDepartureDate", TextBox2.Text);
                updCommand.Parameters.AddWithValue("@TReasonofTravel", DropDownList2.Text);            
                int rowsUpdated = updCommand.ExecuteNonQuery();

            }
            else
            {

                // INSERT STATEMENT
                MySqlCommand insCommand = new MySqlCommand("INSERT into travel(clientid,clientida,Tcountry,TArrivalDate,TDepartureDate,TReasonofTravel)VALUES(@clientid,@clientida,@Tcountry,@TArrivalDate,@TDepartureDate,@TReasonofTravel)", con);

                insCommand.Parameters.AddWithValue("@clientid", TextBox3.Text);
                insCommand.Parameters.AddWithValue("@clientida", TextBox4.Text);
                insCommand.Parameters.AddWithValue("@Tcountry", DropDownList1.Text);
                insCommand.Parameters.AddWithValue("@TArrivalDate", TextBox1.Text);
                insCommand.Parameters.AddWithValue("@TDepartureDate", TextBox2.Text);
                insCommand.Parameters.AddWithValue("@TReasonofTravel", DropDownList2.Text);
                int rowsUpdated = insCommand.ExecuteNonQuery();
            }

Posted 2-Sep-19 13:33pm

Member 14192879

Updated 2-Sep-19 14:56pm

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MadMyche · Accepted Answer · 2019-09-02T14:57:00

Well the first thing to tell you is to use the debugger, set some breakpoints, and see what values are where in your code:
What value is assigned at int count = ...ExecuteScalar();
Not what you are expecting?

Maybe we should create some values for those Session variables you are usingAnd naturally I am going to tell you to use these in your query as parameters- I see in your INSERT/UPDATE block you know how to use cmd.Parameters.AddWithValue()

So lets start with this block of code, have a debug

C#

string sesClientID = Session["clientid"];
string sesClientIDa = Session["clientida"];

C#

MySqlCommand cmdCount = new MySqlCommand("SELECT count(*) from travel WHERE  clientid=@ClientID AND clientida=@ClientIDa", con);
cmdCount.Parameters.AddWithValue("@ClientID", ClientID);
cmdCount.Parameters.AddWithValue("@ClientIDa", ClientIDa);
con.Open();
int count = Convert.ToInt16(cmdCount.ExecuteScalar());
if (count > 0) // set breakpoint on this line

So working from here you should be able to see what your Session values are and if it is finding it in the DB or not. Continue this way through your code, and remember to ALWAYS USE parameters.

One part of discontinuity that I see is that you are using the TextBox values for the INSERT statement as opposed to the Session variables.
You may want to check those as well and compare to see if they are matching or not.

How to update data if it already exist via ASP.NET visual studio 2017

1 solution

Solution 1

Add your solution here

Preview 0

How to update data if it already exist via ASP.NET visual studio 2017

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us