CODEWITHSUNDEEP
c#asp.net
Edaleen Cruz
Edaleen Cruz

Reputation: 77

Writing to a database in asp.net

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.

Upvotes: 0

Views: 3775

Answers (5)

shashwat
shashwat

Reputation: 8024

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.

Upvotes: 5

Ann L.
Ann L.

Reputation: 13975

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.

Upvotes: 1

mrqyue
mrqyue

Reputation: 94

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(); 
                }

Upvotes: 1

andri
andri

Reputation: 1021

try checking these things :

  1. try opening your connection before executing the SQL

  2. 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)

Upvotes: -1

Steve Wellens
Steve Wellens

Reputation: 20640

In your connection string:

"database= -table to be accessed-;

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

Upvotes: 1

Related Questions