CODEWITHSUNDEEP
c#sql-server
Danish
Danish

Reputation: 353

Non-integer primary key exception

Hello guys i m working with a school assignment which has a simple form and database.

there is students table in database.

Problem:

This program works when i Enter stu_ID as integer which is primary key like 1234, problem arise when i enter stu_ID as non-integer.(assignment requirement). Like BSCS-122.it gives following exception

'System.Data.SqlClient.SqlException' {"Invalid column name 'BSCS'."}

I m working with Visual studio 2015 SQL Server 2008 R2 express.

students
    stu_Id       varchar(50) PK
    stu_Name     varchar(50)
    Stu_Age      int
    stu_Semester int
    stu_City     varchar(50)

+------------+-----------------+---------+--------------+-------------+
| stu_Id     | stu_Name        | Stu_Age | stu_Semester | stu_City    | 
+------------+-----------------+---------+--------------+-------------+
| BSCS-122   | Danish Kareem   | 22      |      5       | Decya       |
| MBA-233    | Kamran          | 23      |      5       | JPT         |
| .. ....    | ............... | ..... ..| ....... .....| ...... .....|
+------------+-----------------+---------+--------------+-------------+

The c# code is bellow.

 private void btnInsert_Click(object sender, EventArgs e)
    {
        SqlConnection objSqlConnection = new SqlConnection(@"Data Source=DESKTOP-VESS66M\SQLEXPRESS;Initial Catalog=Persons;Integrated Security=True");
        try
        {
            //
            objSqlConnection.Open();
            string insertCommand = "INSERT INTO students VALUES (" +stu_ID.Text+ ",'" + stu_Name.Text + "','" + Convert.ToInt32(stu_Age.Text) + "','" + Convert.ToInt32(stu_Semester.Text) + "','" + stu_City.Text + "')";
            SqlCommand objSqlCommand = new SqlCommand(insertCommand, objSqlConnection);
            objSqlCommand.ExecuteNonQuery();
            //
            this.studentsTableAdapter.Fill(this.personsDataSet.students);
            //
            MessageBox.Show("Student " + txtName.Text + " had been added Successfully", "Added Succefull", MessageBoxButtons.OK, MessageBoxIcon.Information);

        }
        catch (Exception)
        {

            throw;
        }
        finally
        {
            objSqlConnection.Close();
        }

    }

Upvotes: 0

Views: 243

Answers (2)

Caldazar
Caldazar

Reputation: 3812

Short and quick fix is to add single quotes arount stu_ID but that's prone to SQL injection: "'"+stu_ID.Text+"'"

Better way is to add parameters to your query, to prevent SQL injection:

string insertCommand = "INSERT INTO students VALUES (@stuID, @stuName, @stuAge, @stuSemester, @stuCity)";
insertCommand.Parameters.Add(new SqlParameter("stuId", stu_ID.Text));
command.Parameters.Add(new SqlParameter("stuName", stu_Name.Text));
command.Parameters.Add(new SqlParameter("stuAge", Convert.ToInt32(stu_Age.Text)));
command.Parameters.Add(new SqlParameter("stuSemester", Convert.ToInt32(stu_Semester.Text)));
command.Parameters.Add(new SqlParameter("stuCity", stu_City.Text));

Upvotes: 1

DrewB
DrewB

Reputation: 1536

Your text field value needs to be surrounded by single quotes like:

"INSERT INTO students VALUES ('" + stu_ID.Text + "')"

However, this is still an extremely bad idea as you're leaving yourself open to SQL Injection. If you aren't going to use a framework (such as Entity Framework) for your data layer, you at least need to use an SQLParameter to inject your values.

Upvotes: 0

Related Questions