Inserting values into sqlserver

Question

I have inserted values into sql several times but now i am facing problem with the following code

 protected void Button1_Click(object sender, EventArgs e)
    {

        string connstring = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;
        con = new SqlConnection(connstring);

        string name = txtName.Text;
        string user = txtUser.Text;
        string password = txtPwd.Text;
        string email = txtEmail.Text;
        long phone=Convert.ToInt64(txtPhone.Text);
        string address = txtAddr.Text;
        string city = txtCity.Text;
        string gender = RadioButtonList1.SelectedItem.ToString();
        string dob = txtDOB.Text;
        string qualification = DropDownList1.SelectedItem.ToString();
        string skills = CheckBoxList1.SelectedItem.ToString();

        string insertstring = " insert into JobRegisteration values ("+name+","+user+","+password+","+email+","+phone+","+address+","+city+","+gender+","+dob+","+qualification+","+skills+")";
        cmd = new SqlCommand(insertstring,con);
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();

    }
}

When I am inserting values into this through asp.net page, its giving following error.

Exception Details: System.Data.SqlClient.SqlException: Invalid column name 'sbip'.
Invalid column name 'tttt'.
Invalid column name 'ttt'.
The multi-part identifier "[email protected]" could not be bound.
Invalid column name 't'.
Invalid column name 'tttt'.
Invalid column name 'Male'.
Invalid column name 'MCA'.
Invalid column name 'C#'.

where tttt, male mca, etc etc are values that are passed from asp page.

thanks!

Try to use sql parameters. They are more secure and prevent some issues like bad input format, your error may related to this if values have commas or quotes or special characters like that. You can find examples here: msdn.microsoft.com/en-us/library/… — Kuzgun
– Kuzgun, Commented Aug 15, 2013 at 6:47
Try put fields in sql explicitly i.e. "insert into JobRegisteration(field1_for_name, ..., field_for_skills) values (...)" — Dmitrii Bychenko
– Dmitrii Bychenko, Commented Aug 15, 2013 at 6:47

Damith · Accepted Answer · 2013-08-15 06:57:44Z

3

use parameters like below and also using statements

string connstring = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;
// change this select statement based on your exact column names 
string insertstring = "insert into JobRegisteration ([Name] ,[User] ,[Password] ,[Email] ,[Phone],[Address] ,[City] ,[Gender] ,[Dob] ,[Qualification] ,[Skills]) values (@name ,@user ,@password ,@email ,@phone,@address ,@city ,@gender ,@dob ,@qualification ,@skills)";

using (var con = new SqlConnection(connstring))
using(var cmd = new SqlCommand(insertstring, con))
{
    cmd.Parameters.AddWithValue("@name", txtName.Text);
    cmd.Parameters.AddWithValue("@user", txtUser.Text);
    // give all the parameters..
    con.Open();
    cmd.ExecuteNonQuery();
}

answered Aug 15, 2013 at 6:57

Damith

63.2k13 gold badges106 silver badges154 bronze badges

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

Comments

haim770 · Accepted Answer · 2013-08-15 06:48:13Z

2

You need to wrap your inserted values with ' otherwise the database treat them as column names:

string insertstring = " insert into JobRegisteration values ('"+name+"','"+user+"','"+password+"','"+email+"','"+phone+"','"+address+"','"+city+"','"+gender+"','"+dob+"','"+qualification+"','"+skills+"')";

Also, as other suggested you really should rely on Prepared Statements to avoid such problems (among others).

answered Aug 15, 2013 at 6:48

haim770

49.2k7 gold badges113 silver badges139 bronze badges

1 Comment

Marc Gravell Over a year ago

prepared statements don't do a lot here; it is simply parameterization that is the essential bit; that is not quite the same thing

mickro · Accepted Answer · 2013-08-15 06:52:42Z

1

There are many solution to your problem.

1) Try to fit with this format:

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

2) as said haim770, surround your values with '

3) use sql parameters way

4) or look at Linq, that's really simplify way to work with database

answered Aug 15, 2013 at 6:52

mickro

8912 gold badges12 silver badges27 bronze badges

Comments

Jignesh.Raj · Accepted Answer · 2013-08-15 06:53:24Z

1

You need to add single quote ' in your query:

string insertstring = " insert into JobRegisteration values ('"+name+"','"+user+"','"+password+"','"+email+"','"+phone+"','"+address+"','"+city+"','"+gender+"','"+dob+"','"+qualification+"','"+skills+"')";

answered Aug 15, 2013 at 6:53

Jignesh.Raj

6,0074 gold badges30 silver badges57 bronze badges

Comments

Dmitrii Bychenko · Accepted Answer · 2013-08-15 07:02:03Z

use using (pun!), bind variables (a.k.a. parameters), format your query, when query seems dubious put what you want explicitly...

protected void Button1_Click(object sender, EventArgs e) {
  string name = txtName.Text;
  string user = txtUser.Text;
  string password = txtPwd.Text;
  string email = txtEmail.Text;
  long phone = Convert.ToInt64(txtPhone.Text); // <- what about +77(555)123-456-78?
  string address = txtAddr.Text;
  string city = txtCity.Text;
  string gender = RadioButtonList1.SelectedItem.ToString();
  string dob = txtDOB.Text;
  string qualification = DropDownList1.SelectedItem.ToString();
  string skills = CheckBoxList1.SelectedItem.ToString();

  using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString)) {
    con.Open();

    using(var cmd = con.CreateCommand()) {
      cmd.CommandText = 
        // replace all "field_for_*" for actual fields
        @"insert into JobRegisteration(
            field_for_name,
            field_for_user,
            field_for_password,
            field_for_email,
            field_for_phone, 
            field_for_address, 
            field_for_city, 
            field_for_gender, 
            field_for_dob, 
            field_for_qualification, 
            field_for_skills)
          values (
            @prm_name,
            @prm_user,
            @prm_password,
            @prm_email,
            @prm_phone, 
            @prm_address, 
            @prm_city, 
            @prm_gender, 
            @prm_dob, 
            @prm_qualification, 
            @prm_skills)";

       cmd.Parameters.AddWithValue("@prm_name", name);
       cmd.Parameters.AddWithValue("@prm_user", user);
       cmd.Parameters.AddWithValue("@prm_password", password);
       cmd.Parameters.AddWithValue("@prm_email", email); 
       cmd.Parameters.AddWithValue("@prm_phone", phone);
       cmd.Parameters.AddWithValue("@prm_address", address);
       cmd.Parameters.AddWithValue("@prm_city", city);
       cmd.Parameters.AddWithValue("@prm_gender", gender); 
       cmd.Parameters.AddWithValue("@prm_dob", dob);
       cmd.Parameters.AddWithValue("@prm_qualification", qualification);
       cmd.Parameters.AddWithValue("@prm_skills", skills);

       cmd.ExecuteNonQuery();
    }
  }
}

Collectives™ on Stack Overflow

Inserting values into sqlserver

5 Answers 5

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related