Executing an INSERT command using ASP.net

Question

I am using Visual Studio 2010 to create a simple Website for a college assignment. I am trying to create a contact form that submits the users name, email and message to my database table Messages.

I have created the relevant web service and I know that it is working when I try to GET data from the Table. I am just a little confused as to how I can INSERT data into the table.

Below is the code to my web service. The method I am concerned with is addMessage() I call the method when a button is clicked that is located on the contact.aspx page.

    public class Customers : System.Web.Services.WebService {

    [WebMethod]
    public DataSet getCustomers() {
        SqlConnection conn;
        SqlDataAdapter myDataAdapter;
        DataSet myDataSet;
        string cmdString = "Select * From Customers";
        conn = new SqlConnection("Data Source=localhost\\SQLEXPRESS;AttachDbFilename=C:\\Users\\n00093500\\Desktop\\MMCA2\\APP_DATA\\NORTHWIND.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");
        myDataAdapter = new SqlDataAdapter(cmdString, conn);
        myDataSet = new DataSet();
        myDataAdapter.Fill(myDataSet, "Customers");
        return myDataSet;
    }

    [WebMethod]
    public void addMessage(String n, String e, String m)
    {
        SqlConnection conn;
        SqlDataAdapter myDataAdapter;
        SqlCommand myCommand = new SqlCommand("INSERT INTO Messages VALUES("+n+","+e+","+m+")");
        conn = new SqlConnection("Data Source=localhost\\SQLEXPRESS;AttachDbFilename=C:\\Users\\n00093500\\Desktop\\MMCA2\\APP_DATA\\NORTHWIND.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");
        //UNSURE WHAT TO DO FROM THIS POINT... CAN I USE myDataAdapter to execute a query?
    }

}

Appreciate any help you guys might have! Thanks

You are right to be concerned. That code is vulnerable to sql injection attacks. It's practically begging to get hacked. — Joel Coehoorn
– Joel Coehoorn, Commented Dec 2, 2013 at 18:18
I don't care about that right now, I'm just learning the bare bones of how to work with ASP. Just need to know how to put the data in the form into my messages table — Javacadabra
– Javacadabra, Commented Dec 2, 2013 at 18:19
@Javacadabra I think that's a poor way to approach Joel's injection concern. It's important, and really not harder than doing it your current way. — Chris Farmer
– Chris Farmer, Commented Dec 2, 2013 at 18:20
@ChrisFarmer If it doesn't involve deviating to far from my original code of course I'll take it onboard, but the reality is I'm trying to integrate the most basic of functionality into a site for a college module that I am taken that will not be looking at the code in that detail. — Javacadabra
– Javacadabra, Commented Dec 2, 2013 at 18:21

Joel Coehoorn · Accepted Answer · 2013-12-02 18:21:22Z

5

[WebMethod]
public void addMessage(String n, String e, String m)
{
    string sql = "INSERT INTO Messages VALUES(@n, @e, @m)";
    using (var conn = new SqlConnection("Data Source=localhost\\SQLEXPRESS;AttachDbFilename=C:\\Users\\n00093500\\Desktop\\MMCA2\\APP_DATA\\NORTHWIND.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"))
    using (var cmd = new SqlCommand(sql, conn))
    {
       //change these three lines to use actual database column types, lengths
       //I'll pretend "e" is a date column just to show an example of how that might look
       cmd.Parameters.Add("@n", SqlDbType.NVarChar, 50).Value = n;
       cmd.Parameters.Add("@e", SqlDbType.DateTime).Value = DateTime.Parse(e);
       cmd.Parameters.Add("@m", SqlDbType.NVarChar, 50).Value = m;

       conn.Open();
       cmd.ExecuteNonQuery();
    }
}

answered Dec 2, 2013 at 18:21

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Javacadabra Over a year ago

Perfect Joel, makes a lot of sense, thanks for the help! Will accept answer in 5 mins

Collectives™ on Stack Overflow

Executing an INSERT command using ASP.net

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related