Dropdown insert issue

Question

I am creating a web form for my final project. I am working on a very basic fantasy league.

I have a page that lets the user select 11 players from drop down lists for each position and then insert them into a database. When I click submit on the bottom of the page, it should insert the text from each drop down list into the database.

Now when I try to insert the text of a drop down into the database, it shows me this error:

Invalid column name 'Courtois'.

Courtois is however text from the Goalkeeper drop down.

protected void btnConfirm_Click(object sender, EventArgs e)
{

   string query = "INSERT into userPicks (Name) values ("+ ddlKeeper.Text + ")";
   SqlConnection conn = new SqlConnection(cs);

   SqlCommand cmd = new SqlCommand(query, conn);
   conn.Open();
   int rowsInserted = cmd.ExecuteNonQuery();

   if (rowsInserted > 0)
   {
      Response.Write("Data inserted successfully");
   }
   else
   {
      Response.Write("Data was not inserted into database");
   }
}

The if statement is just to test if the data is being inserted or not.

I have tried hard coding the value that I need inserted, and that works just fine.

I think it is probably a wrong syntax or a wrong way that I am trying to insert the data into the database.

This is my code to bring up the names of players from a separate database:

protected void Page_Load(object sender, EventArgs e)
{
   DataSet gk = new DataSet();

   SqlConnection conn = new SqlConnection(cs);
   conn.Open();

   SqlCommand cmdgk = new SqlCommand("Select name FROM Players WHERE position='gk'", conn);

   SqlDataAdapter adpgk = new SqlDataAdapter(cmdgk);

   adpgk.Fill(gk);
   ddlKeeper.DataSource=gk;
   ddlKeeper.DataValueField = "Name";
   ddlKeeper.DataBind();

}

score 0 · Accepted Answer · answered Aug 15 '15 at 23:11

0

Try this -

string query = "INSERT into userPicks (Name) values ('"+ ddlGK.Text + "')";

Better way do this will be to use parameterized query. Search for parameterized query and you should find plenty of examples.

answered Aug 15 '15 at 23:11

Yousuf

3,105
7
28
38

Great! That was the issue. Thanks a lot! – ramzz Aug 15 '15 at 23:13

score 0 · Answer 2 · edited May 23 '17 at 11:51

0

A string value should be enclosed in single quotes when used in a sql query. However the correct way to execute your insert query is through the use of a parameterized query like this one

protected void btnConfirm_Click(object sender, EventArgs e)
{

   string query = "INSERT into userPicks (Name) values (@name)";
   using (SqlConnection conn = new SqlConnection(cs))
   using (SqlCommand cmd = new SqlCommand(query, conn))
   {
       conn.Open();
       cmd.Parameters.Add("@name", SqlDbType.NVarWChar).Value = ddlGK.Text; 
       int rowsInserted = cmd.ExecuteNonQuery();
       if (rowsInserted > 0)
       {
            Response.Write("Data inserted successfully");
       }
       else
       {
            Response.Write("Data was not inserted into database");
       }
    }
}

A parameterized query is always the correct way to go. You don't need to worry if your input contains a single quote (instead with string concatenation your query will fail with a Syntax Error) but most important your code will be free from Sql Injection hacks

By the way, you TAG your questio with MySql but your code uses the SqlClient classes that works for Sql Server.

edited May 23 '17 at 11:51

Community

1
1

answered Aug 15 '15 at 23:14

Steve

213,761
22
232
286

Thanks for the explanation. Will try out this way as well. – ramzz Aug 15 '15 at 23:21
Please do not use string concatenation on a public web site. You will regret it sooner or later. – Steve Aug 15 '15 at 23:25
Okay so I did what you said and it works, but now the problem is that it keeps inserting the default drop down value into the database. Regardless of what I select from drop down, it inserts the default value in there. What am I doing wrong here? – ramzz Aug 15 '15 at 23:27
It seems that you don't have the Page_Load code protected from reentry using IsPostBack. – Steve Aug 15 '15 at 23:28
Also, what does "SqlDbType.NVarWChar" mean? – ramzz Aug 15 '15 at 23:29
Sorry about too many questions. Still new to this. How do I protect Page_Load from reentry? – ramzz Aug 15 '15 at 23:31
Every parameter added to SqlCommand.Parameters collection has a DataType that helps the engine to correctly handle your value when it presents the query to the database engine for insertion. If you don't set correctly the datatype then you hope that the database engine understand your intentions and doesn't translate wrongly your values – Steve Aug 15 '15 at 23:32
If you set the default value for the dropdown in the Page_Load then you need to use _if(!IsPostBack)_ before every block of code that need to be set only the first time the page is loaded. Remember, in the ASP.NET page life cycle every time an event should run on the server, the Page_Load event is called before that event. You could easily see this pattern if you put a breakpoint in the Page_Load event. – Steve Aug 15 '15 at 23:34

Dropdown insert issue

2 Answers2