Asp.net with c# , insert and update in save button

Question

I am using one button named as Button 1. In Button 1 button I perform insert as well as update. I can insert a new row. But when I update the row I had a error on that:

"ORA-00933: SQL command not properly ended ".

My code is:

protected void Button1_Click(object sender, EventArgs e)
{
    string UserName = "UserName";
    Session["UserName"] = lb1.Text;

    TextBox TextBox1 = (TextBox)FindControl("TextBox1");
    Label label11 = (Label)FindControl("label11");
    TextBox TextBox2 = (TextBox)FindControl("TextBox2");
    TextBox TextBox3 = (TextBox)FindControl("TextBox3");
    TextBox TextBox4 = (TextBox)FindControl("TextBox4");
    DropDownList DropDownList3 = (DropDownList)FindControl("DropDownList3");
    DropDownList DropDownList1 = (DropDownList)FindControl("DropDownList1");
    TextBox TextBox5 = (TextBox)FindControl("TextBox5");
    TextBox TextBox6 = (TextBox)FindControl("TextBox6");
    DropDownList DropDownList2 = (DropDownList)FindControl("DropDownList2");
    TextBox TextBox7 = (TextBox)FindControl("TextBox7");
    TextBox TextBox8 = (TextBox)FindControl("TextBox8");

           { 

        con.Open();

        OleDbDataAdapter da = new OleDbDataAdapter("select * from 
service_master where req_no='" + this.TextBox1.Text.ToString() + "'", con);
        DataSet ds = new DataSet();
        da.Fill(ds);
        if (ds.Tables[0].Rows.Count > 0)
        {
            string sql1 = "update service_master set req_no='" + this.TextBox1.Text.ToString() + "' , req_dt='" + label11.Text.ToString() + "',req_by='" + Session["UserName"].ToString() + "', ser_cd='" + TextBox3.Text.ToString() + "',serv_desc= '" + TextBox4.Text.ToString() + "',serv_grp_cd='" + DropDownList3.SelectedItem.Value.ToString() + "',base_uom_cd= '" + DropDownList1.SelectedItem.Value.ToString() + "',sac_cd='" + TextBox5.Text.ToString() + "',ser_long_desc='" + TextBox6.Text.ToString() + "',tax_ind='" + DropDownList2.SelectedItem.Value.ToString() + "',active_ind= '" + TextBox7.Text.ToString() + "',del_ind='" + TextBox8.Text.ToString() + "' where req_no='" + this.TextBox1.Text.ToString() + "')";
            OleDbCommand cmd = new OleDbCommand(sql1, con);
            cmd.ExecuteNonQuery();
            WebMsgBox.Show("Data Successfully Updated");
        }
        else
        {
            string sql = "insert into service_master(req_no,req_dt,req_by,ser_cd,serv_desc,serv_grp_cd,base_uom_cd,sac_cd,ser_long_desc,tax_ind,active_ind,del_ind ) values(" + this.TextBox1.Text.ToString() + ",'" + label11.Text.ToString() + "', '" + Session["UserName"].ToString() + "', '" + TextBox3.Text.ToString() + "','" + TextBox4.Text.ToString() + "','" + DropDownList3.SelectedItem.Value.ToString() + "','" + DropDownList1.SelectedItem.Value.ToString() + "','" + TextBox5.Text.ToString() + "','" + TextBox6.Text.ToString() + "','" + DropDownList2.SelectedItem.Value.ToString() + "','" + TextBox7.Text.ToString() + "','" + TextBox8.Text.ToString() + "')";
            OleDbCommand com = new OleDbCommand(sql, con);
            com.ExecuteNonQuery();
            WebMsgBox.Show("The data for request number" + TextBox1.Text + "is saved");
        }

        con.Close();
    }
}

this link maybe help you : https://stackoverflow.com/questions/8940471/sql-error-ora-00933-sql-command-not-properly-ended — mehdi farhadi, Feb 26 '18 at 12:13
(OT) please give your controls more meaningful names than "TextBox7" - your future self will thank you for it when this code needs updating. — Hans Keﬆing, Feb 26 '18 at 16:09

SANM2009 · Answer 1 · 2018-02-26T12:14:26.780

Your query should look something like this

//insert query
//string sql1 = "INSERT INTO Test(id, name) VALUES(@User_FirstName, @User_LastName)";
//update sample query
string sql1 = "UPDATE Test SET User_FirstName=@User_FirstName, User_LastName=@User_LastName";

SqlCommand cmd = new SqlCommand(smt, _connection);
cmd.Parameters.Add("@User_FirstName", FirstName.Text);
cmd.Parameters.Add("@User_LastName", LastName.Text);

Always use Parameters to preform any database actions. Using user input is very dangerous, look up sql injections.

Asp.net with c# , insert and update in save button

1 Answers1