Writing to a database in asp.net

Question

I'm currently finishing an asp.net project for a class and began to notice a major flaw with one of the requisites. The application should ask five questions and write the answers to a database, afterwards it should display the results of the survey to the user.

This is what I have attempted so far:

 public static string GetConnectionString()
    {
        string connStr = String.Format("server={0}; user id={1}; password={2};" + "database= -table to be accessed-; pooling=false", 
            "-database server-", "-user-", "-password-");
        return connStr;
    } 

 protected void Button1_Click(object sender, EventArgs e)
    {

        if (Page.IsValid)
        {
            string sex = gender.Text;
            string likes = interests.Text;
            string edu = education.Text;
            string nation = nationality.Text;
            string userage = age.Text;

            MySql.Data.MySqlClient.MySqlConnection mycon;

            mycon = new MySqlConnection(GetConnectionString());

            try
            {
                MySqlCommand cmd = new MySqlCommand("INSERT INTO survey (gender, age, birthplace, occupation, winner) VALUES ('" + sex  + ", " + likes + ", " + edu + ", " + userage + "')", mycon);
                cmd.ExecuteNonQuery();
                mycon.Open();
            }

            catch (Exception ex)
            {
                Response.Write(ex.Message);
            }

            finally
            {
                mycon.Close();
            }

        }
    }

I went ahead and replaced the database information with placeholders.

The database is MySql and hosted on an external server.

The issue I'm experiencing is that the code compiles, however the information does not get written to the database. I'm not certain if this is due to the fact that I'm still testing the code and have not uploaded the web application to the server or the fact that it's just wrong.

As far as displaying the results go, if the above code is correct it would simply be a matter of changing the sql query, correct?

Thanks in advance for the insight.

Nothing that raises any flags. It "works" perfectly in the sense that it compiles and loads. It simply fails to do its job. — Edaleen Cruz
– Edaleen Cruz, Commented Feb 23, 2013 at 20:50
You should be getting an error or exception. You may have other problems. — Steve Wellens
– Steve Wellens, Commented Feb 24, 2013 at 5:06

Community · Accepted Answer · 2017-05-23 12:23:34Z

5

You are executing the command before opening database connection.

ExecuteNonQuery() method and all other Execute method require an open database connection.

And another error is:
Number of columns (i.e. 5) and provided values (i.e. 4) are not equal.

And one more issue in your code is here as stated by Steve Wellens.

Change Your Code like below:

try
{
    MySqlCommand cmd = new MySqlCommand("INSERT INTO survey (gender, age, birthplace, occupation, winner) VALUES ('" + sex  + ", " + likes + ", " + edu + ", " + userage + "')", mycon);
    mycon.Open();
    cmd.ExecuteNonQuery();
}    
catch (Exception ex)
{
    Response.Write(ex.Message);
}    
finally
{
    mycon.Close();
}

Security Notes:

Never add data into query using + operator. It may cause SQL Injection.

What if a user enters 1); DROP TABLE <table-name> -- in Age TextBox..??
Anyone can delete any table entirely from database.

Use MySQL Parameter to avoid such problems. It may prevent from causing serious damages to your entire database.

edited May 23, 2017 at 12:23

CommunityBot

11 silver badge

answered Feb 23, 2013 at 18:48

shashwat

8,0749 gold badges61 silver badges95 bronze badges

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

3 Comments

Edaleen Cruz Over a year ago

Thank you for pointing out the security flaws. The only thing the professor had mentioned was limiting the permits of the user but somehow I don't think that's quite as effective as MySQL Parameter.

Edaleen Cruz Over a year ago

It is still not writing to the database, would you happen to know if that's related to the fact that the application is still not loaded into the server? I'm still using Visual Studio to finish it.

shashwat Over a year ago

If it is not writing to database then u must b getting n exception. Can u pls tell me the exact error message..?

Steve Wellens · Accepted Answer · 2013-02-23 17:30:56Z

1

In your connection string:

"database= -table to be accessed-;

...you don't put the table. The table is specified in the SQL statement.

answered Feb 23, 2013 at 17:30

Steve Wellens

20.6k2 gold badges31 silver badges70 bronze badges

Comments

mrqyue · Accepted Answer · 2013-02-23 18:02:17Z

1

you should open the connect first, then execute the query.

    try
                {
                    MySqlCommand cmd = new MySqlCommand("INSERT INTO survey (gender, age, birthplace, occupation, winner) VALUES ('" + sex  + ", " + likes + ", " + edu + ", " + userage + "')", mycon);
                    mycon.Open();
                    cmd.ExecuteNonQuery(); 
                }

answered Feb 23, 2013 at 18:02

mrqyue

941 gold badge2 silver badges7 bronze badges

Comments

Ann L. · Accepted Answer · 2013-02-23 18:49:08Z

1

This is likely not the only problem, but it is a problem:

"INSERT INTO survey (gender, age, birthplace, occupation, winner) " + 
"VALUES ('" + sex  + ", " + likes + ", " + edu + ", " + userage + "')",

(I've broken it into two strings to make it easier to read.)

You are inserting into five columns. You are only specifying four data values, and with the exception of gender they don't appear to be in the right order or even be the right data.

answered Feb 23, 2013 at 18:49

Ann L.

14k5 gold badges39 silver badges67 bronze badges

1 Comment

Edaleen Cruz Over a year ago

Wow, I had completely overlooked that. Thank you very much!

andri · Accepted Answer · 2013-02-23 17:35:46Z

-1

try checking these things :

try opening your connection before executing the SQL
check your SQL, and try execute them directly against the database. what i see in your SQL is you are concatenating the values into one string (quotes exist only in beginning and end, but not in between the parameters passed)

answered Feb 23, 2013 at 17:35

andri

1,0211 gold badge9 silver badges16 bronze badges

Collectives™ on Stack Overflow

Writing to a database in asp.net

5 Answers 5

Security Notes:

3 Comments

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Security Notes:

3 Comments

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related