Execute Query from another function

Question

I want to execute a query on a button click event.

But that query is written in another function.

Here is my code, and it's not working. What is my problem?

namespace MCE_Member_Registration
{
    public partial class registration_form_view : System.Web.UI.Page
    {
        SqlConnection conn = new SqlConnection("ConnectionString");
        SqlCommand cmd;
        protected void Page_Load(object sender, EventArgs e)
        {
            createform();
        }

        protected void createform() {
            NameValueCollection nvc = Request.Form;
            surname.Text = nvc["txt_surname"];
            cmd.CommandText = "Insert into mce_applicants_information values(N'" + nvc["txt_surname"] + "')";
        }

        protected void confirm_Click(object sender, EventArgs e)
        {
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }
    }
}

This query executes empty. I thought it is because of that SqlCommand cmd thing is in another function... — user2163530
– user2163530, Commented Mar 14, 2013 at 9:37
it's a bad design because you are setting an object in class level. — John Woo
– John Woo, Commented Mar 14, 2013 at 9:38

Tim Schmelter · Accepted Answer · 2013-03-14 09:43:12Z

I'm not sure if this solves your problem. But if you really need another method to create your command, let it return it.

protected SqlCommand  GetCommand() 
{
    SqlCommand cmd = new SqlCommand("Insert into blahblah values(blahblah)", connection);
    return cmd;
}

protected void Button1_Click() {
    connection.Open();
    GetCommand().ExecuteNonQuery();
    connection.Close();
}

Note that this is not best-practise due to several reasons. The connection should be closed even if an exception occured so use using statement instead. But that would be a problem in this approach since the connection is a field.

So i would prefer the all-in-one method approach which also uses parameters tro prevent sql-injection attacks:

protected void Button1_Click() 
{
    ExecuteBlahBlahCommand("blahblah");
}

private void ExecuteBlahBlahCommand(string blaColumnVal)
{
    const string sql = "Insert into blahblah values(@blaColumn)";
    using (var con = new SqlConnection(connectionString))
    using (var cmd = new SqlCommand(sql, con))
    {
        cmd.Parameters.AddWithValue("@blaColumn", blaColumnVal);
        con.Open();
        cmd.ExecuteNonQuery();
    }
}

Aghilas Yakoub · Accepted Answer · 2013-03-14 09:39:13Z

0

I suggest you to use CommandText property and not contructor, because instance of cmd is created before this code, so you adjust your property

protected void CreateQuery() {

    cmd.CommandText = "Insert into blahblah values(blahblah)";
}

protected void Button1_Click() {

    connection.Open();
    CreateQuery();

    cmd.ExecuteNonQuery();
    connection.Close();
}

answered Mar 14, 2013 at 9:39

Aghilas Yakoub

29k5 gold badges49 silver badges51 bronze badges

Comments

Blachshma · Accepted Answer · 2013-03-14 09:43:40Z

0

Answering the question itself - Any variable you declare inside a function cannot be seen outside that function. You need to declare the SqlCommand in the correct scope...

For instance:

SqlCommand cmd;
protected void CreateQuery() 
{
   cmd = new SqlCommand("Insert into blahblah values(blahblah),connection)";
}

protected void Button1_Click() 
{
  CreateQuery();
  connection.Open();
  cmd.ExecuteNonQuery();
  connection.Close();
}

This will declare the variable in the class level, and be accessible to all other methods in that class.

I'll just mention that @Tim Schmelter's answer is a good solution that might better suit your needs.

edited Mar 14, 2013 at 9:43

answered Mar 14, 2013 at 9:38

Blachshma

17.5k4 gold badges62 silver badges73 bronze badges

Collectives™ on Stack Overflow

Execute Query from another function

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related