ASP.net database code using c#

Question

Please tell me the error in this code, the data is not stored in the table.

SqlConnection con;
SqlCommand com;

string constr = @"Data Source=SQL5004.myASP.NET;Initial Catalog=DB_9F2D70_arjunb98;User Id=DB_9F2D70_arjunb98_admin;Password=#;";

protected void submit_Click(object sender, EventArgs e)
{
    try
    {
        con = new SqlConnection(constr);
        con.Open();

        com = new SqlCommand();
        com.CommandText = "insert into table values ('" + DropDownList2.SelectedValue + "', '" + DropDownList1.SelectedValue + "','" + name.Text + "','" + mail.Text + "', '" + Address.Text + "', '" + DropDownList3.SelectedValue + "', '" + ph.Text + "','" + message.Text + "')";

        com.Connection = con;
        com.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        Page.ClientScript.RegisterStartupScript(
        Page.GetType(),
            "MessageBox", "<script language='javascript'>alert('Sorry! the data is not Submitted, Please try again ')</script>");
    }
    finally
    {
        con.Close();
    }

1. SQL Injection vulnerability. 2. You're swallowing the exception - do something with it so you know what the error actually was. 3. I'm 99.9% sure that you have to specify the columns as well as the values - and even if you don't, it's best practice to do so - what happens if you add columns to the table later? 4. `table` is a SQL reserved word - did you actually name your table "table"? — Tim, Feb 02 '16 at 04:59
Which line of your code is specific for ASP.NET? We do not care what the code runs in - if it is not relevant to the question, REMOVE THE TAG. — TomTom, Feb 04 '16 at 06:00

score 0 · Answer 1 · answered Feb 02 '16 at 05:27

Aahhhhh. You have messed up your code. Also you have not shown your aspx so I am not much aware of the parameters you are using.

Also as suggested by Tim your code is prone to SQL Injection. So its better to used parameterized queries(recommended in your case)

So a simple reference with your code will be as below:-

On ButtonClick you may write something like below

protected void submit_Click(object sender, EventArgs e)
{
    SqlConnection conn = new SqlConnection("Data Source=SQL5004.myASP.NET;Initial Catalog=DB_9F2D70_arjunb98;User Id=DB_9F2D70_arjunb98_admin;Password=#;"); // connection string described by you
    SqlCommand cmd = new SqlCommand("insert into Person(Column1,Column2,Column3,....) values(@Column1, @Column2, @Column3,...)", conn);
    cmd.Parameters.AddWithValue("@Column1", TextBoxID.text); // it may be option as per your requirement(ex:- Dropdownlist)
    cmd.Parameters.AddWithValue("@Column2", textbox2.Text);
    cmd.Parameters.AddWithValue("@Column3", textbox3.Text);
    try
    {
        conn.Open();
        cmd.ExecuteNonQuery();
    }
    catch
    {
        label.Text = "Error when saving on database";
        conn.Close();
    }

    // Empty your controls here
    Control1.text = "";
    Control2.text = "";
    Control3.text = "";
}

There are also many other links available for your requirement.

http://www.aspsnippets.com/Articles/Using-Parameterized-queries-to-prevent-SQL-Injection-Attacks-in-SQL-Server.aspx

Hope that helps and clarifies your doubt too.

Happy learning :)

You can also use [`using` statement](http://stackoverflow.com/questions/4717789/in-a-using-block-is-a-sqlconnection-closed-on-return-or-exception) — शेखर, Feb 02 '16 at 05:36
@user3332694: if it helped you, kindly accept it for others too :) — Nad, Feb 02 '16 at 12:41
@user3332694: if it worked, kindly tick the answer. So it can be helpful to others — Nad, Feb 03 '16 at 05:05

ASP.net database code using c#

1 Answers1