ASP.NET insert data & update

Question

User-1683022223 posted

hello

what is wrong with my code 

first 

am insert new data into sql (work)  from table_vacation

then i want update user data from table_infoname date i add from table_vacation 

the code update am trying here

 //add report from Table_vacation
        protected void Insert(object sender, EventArgs e)
        {
            string IpassAstringfrompage1 = Convert.ToString(Session["Name"]);
            string filename = Path.GetFileName(FileUpload2.PostedFile.FileName);
            string contentType = FileUpload2.PostedFile.ContentType;
            using (Stream fs = FileUpload2.PostedFile.InputStream)
            {
                using (BinaryReader br = new BinaryReader(fs))
                {
                    byte[] bytes = br.ReadBytes((Int32)fs.Length);
                    string constr = ConfigurationManager.ConnectionStrings["kankonConnectionString"].ConnectionString;

                    SqlConnection con;
                    SqlCommand cmd;
                    using (con = new SqlConnection(constr))
                    {
                        string query = "insert into Table_vacation (idnamev,jop,eldarajah,el3malv,datesp10,idcivilv,datestart,dateend,vacationdata,totalva,infovacation,namevacation,iPAddress,dataaddtime,edit,Name,ContentType,backupmyfile) VALUES (@idnamev,@jop,@eldarajah,@el3malv,@datesp10,@idcivilv,@datestart,@dateend,@vacationdata,@totalva,@infovacation,@namevacation,@iPAddress,@dataaddtime,@edit,@Name,@ContentType,@backupmyfile)";
                        using (cmd = new SqlCommand(query))
                        {
                            cmd.Connection = con;
                            cmd.Parameters.AddWithValue("@Name", filename);
                            cmd.Parameters.AddWithValue("@ContentType", contentType);
                            cmd.Parameters.AddWithValue("@backupmyfile", bytes);
                            cmd.Parameters.AddWithValue("@idnamev", txtUsername.Text);
                            cmd.Parameters.AddWithValue("@jop", joptitel.Text);
                            cmd.Parameters.AddWithValue("@eldarajah", eldarajah.Text);
                            cmd.Parameters.AddWithValue("@el3malv", markazel3mel.Text);
                            cmd.Parameters.AddWithValue("@datesp10", filenumber.Text);
                            cmd.Parameters.AddWithValue("@idcivilv", civilid.Text);
                            cmd.Parameters.AddWithValue("@datestart", txtBoxDate.Text);
                            cmd.Parameters.AddWithValue("@dateend", txtBoxDate2.Text);
                            cmd.Parameters.AddWithValue("@vacationdata", txtBoxDate4.Text);
                            cmd.Parameters.AddWithValue("@totalva", lblLabel1.Text);
                            cmd.Parameters.AddWithValue("@infovacation", VacationList1.Text);
                            cmd.Parameters.AddWithValue("@namevacation", "اجازة دورية");

                            cmd.Parameters.AddWithValue("@iPAddress", IpassAstringfrompage1);
                            cmd.Parameters.AddWithValue("@dataaddtime", Convert.ToDateTime(DateTime.Now.ToLongTimeString()));
                            cmd.Parameters.AddWithValue("@edit", "(جديد)");

                            cmd.Connection = con;
                            try
                            {
                                con.Open();
                                cmd.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                Response.Write(ex.Message);
                            }
                            finally
                            {
                                con.Close();
                            }
                        }
                    }

                    // update user date from Table_infoname
                    using (con = new SqlConnection(str))
                    {
                       
                           String pname = "update_User_vacation_date"; ;
                            con.Open();
                            SqlCommand com = new SqlCommand(pname, con);
                            com.CommandType = CommandType.StoredProcedure;

                            cmd.Parameters.AddWithValue("@DateTimePicker1", txtBoxDate.Text.ToString());
                            cmd.Parameters.AddWithValue("@DateTimePicker2", txtBoxDate2.Text.ToString());

                            try
                            {
                                con.Open();
                                cmd.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                Response.Write(ex.Message);
                            }
                            finally
                            {
                                con.Close();
                            }
                        }
                    }

                    Response.Redirect(Request.Url.AbsoluteUri);
                }
            }
   

SQL StoredProcedure

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[update_User_vacation_date]
(
     @DateTimePicker1 date,
      @DateTimePicker2 date
	)
	as
	begin
	 UPDATE Table_infoname 
	 SET
      DateTimePicker1=@DateTimePicker1,
	  DateTimePicker2=@DateTimePicker2
	  end

Answer 1

User475983607 posted

Use DateTime parameters types not strings when handling dates. 

DateTime DateTimePicker1 = DateTime.MinValue;
DateTime.TryParse(txtBoxDate.Text, out DateTimePicker1);

command.Parameters.Add("@DateTimePicker1", 
    System.Data.SqlDbType.DateTime).Value = DateTimePicker1;

I recommend validating the user inputs before passing the input as parameters.  Doing so will solve issues like this.

Answer 2

User-1683022223 posted

sir  nothing change

Answer 3

User475983607 posted

sir  nothing change

I do not understand your code or explanation.    But, the stored procedure updates every row in the Table_inforname.  Is that what you expect? 

I recommend using the Visual Studio debugger to single step through the logic.   

Answer 4

User-1330468790 posted

Hi kankonq8, 

  

Sorry that I don't get the point of the problem. 

Do you mean that you can not execute the sql operation via your codes?

The image shows different dates in the table "table_infoname" comparing with the table "table_vacation" but it is obviously not what you described as the dates are from two different years.

  

Could you please specify the problem in more details?

  

Best regards,

Sean

Answer 5

User-1683022223 posted

(1) i used search data users information from textbox  , its display  info all in textbox (2)

(3+2) then i insert just data + date into table vacation 

so i want update or add this (A+B) date vacation to user in table_infoname same as insert button

Answer 6

User-1330468790 posted

Hi kankonq8,

 

I undertand that the current conditions are:

• You have already implemented the insert operation successfully
• The problem is that you want to update the table "table_infoname" with the 'datestart' and 'dateend' but the stored procedure does not work as you expected.

If so, I think the problem locates in the stored procedure since you don't set condition for the update.

The stored procedure should look like below (you might need to modify it with your design):

ALTER PROCEDURE [dbo].[update_User_vacation_date]
(
     @DateTimePicker1 date,
      @DateTimePicker2 date,
     @civilid int
	)
	as
	begin
	 UPDATE Table_infoname 
	 SET
      DateTimePicker1=@DateTimePicker1,
	  DateTimePicker2=@DateTimePicker2
      WHERE civilid = @civilid
	  end

Then in C# codes,  you have to modify the update codes like below:

// update user date from Table_infoname
                    using (con = new SqlConnection(str))
                    {
                       
                           String pname = "update_User_vacation_date"; ;
                            con.Open();
                            SqlCommand com = new SqlCommand(pname, con);
                            com.CommandType = CommandType.StoredProcedure;

                            cmd.Parameters.AddWithValue("@DateTimePicker1", txtBoxDate.Text.ToString());
                            cmd.Parameters.AddWithValue("@DateTimePicker2", txtBoxDate2.Text.ToString());
                            cmd.Parameters.AddWithValue("@civilid" , civilid.Text);

                            try
                            {
                                con.Open();
                                cmd.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                Response.Write(ex.Message);
                            }
                            finally
                            {
                                con.Close();
                            }
                        }
                    }

 

At the mean time, you should also focus on the exception which might be thrown during the code executing. 

If you catch any exception, feel free to let me know.

 

Best regards,

Sean

Answer 7

User-1683022223 posted

 public partial class Vacations : System.Web.UI.Page
    {
        public SqlConnection con;
        public SqlCommand cmd;
        public SqlDataAdapter da;
        public DataSet ds;
        public DataTable dt;

        string str = ConfigurationManager.ConnectionStrings["kankonConnectionString"].ConnectionString;

 protected void Button34_Click(object sender, EventArgs e)
        {
            try
            {
                String constring = ConfigurationManager.ConnectionStrings["kankonConnectionString"].ConnectionString;
                SqlConnection sqlcon = new SqlConnection(constring);
                String pname = "update_User_vacation_date"; ;
                sqlcon.Open();
                SqlCommand com = new SqlCommand(pname, sqlcon);
                com.CommandType = CommandType.StoredProcedure;

                

                
                com.Parameters.AddWithValue("@civilid", civilid.Text.ToString());

                com.Parameters.AddWithValue("@DateTimePicker1", txtBoxDate.Text.ToString());
                com.Parameters.AddWithValue("@DateTimePicker2", txtBoxDate2.Text.ToString());
                com.ExecuteNonQuery();

                sqlcon.Close();
            }
            catch (Exception)
            {
            }
        }

ALTER PROCEDURE [dbo].[update_User_vacation_date]
(
	 @civilid NVARCHAR(MAX),
     @DateTimePicker1 date,
      @DateTimePicker2 date
   
	)
	as
	begin
	 UPDATE Table_infoname 
	 SET

	 
      civilid=@civilid,
      DateTimePicker1=@DateTimePicker1,
	  DateTimePicker2=@DateTimePicker2


      WHERE civilid = @civilid
	  end

when i add the update   with button work very well now thanks Sean Fang!

also am search users by civilid 

when i try merge code with insert (update not working & insert working)

Answer 8

User-1683022223 posted

fixed