-3

I tried rewrite other ways but the code always showing error like this

System.Data.SqlClient.SqlException: 'Incorrect syntax near 'student'

The error occurs on the line

cmd.ExecuteNonQuery();

I hope someone will be able to help.

private void button1_Click(object sender, EventArgs e)
{
    if (StudUsn.Text == "" || StudName.Text == "" || 
        FatherName.Text == "" || MotherName.Text == "" ||
        AddressTb.Text == "" || CollegeTb.Text == "")
    {
        MessageBox.Show("No empty filled accepted");
    }
    else
    {
        Con.Open();

        String query = "insert into Student_tbl values(" + StudUsn.Text + ",'" + FatherName.Text + "','" + MotherName.Text + "','" + AddressTb.Text + "','" + CollegeTb.Text + "','" + StudRoomCb + "','" + StudStatusCb + "')";

        SqlCommand cmd = new SqlCommand(query, Con);
        cmd.ExecuteNonQuery();

        MessageBox.Show("Student successfully added");

        Con.Close();

        updateBookedStatus();
        FillStudentDGV();
        FillStudentCombobox();
    }
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
FrankHips
  • 1
  • 5
    Use SQLParameters. Always. And create use and dispose of connections as needed. Never reuse a global one. Ever. You are also not controlling which values go into which columns. – Ňɏssa Pøngjǣrdenlarp Jan 25 '22 at 20:47
  • 5
    You should NOT do string concatenation to build your SQL query, that leads to two things: Bugs like the one you are having, and a security vulnerability called SQL Injection, one funny example is [bobby tables](https://bobby-tables.com/). Use parametrized queries instead – Cleptus Jan 25 '22 at 20:47
  • @Cleptus thanks for your answer, can u send link for parametrized sql, i mean some arcticles – FrankHips Jan 25 '22 at 20:51
  • 3
    There are literally **GAZILLIONS** of articles and blog posts on this - just **search!** See e.g. https://visualstudiomagazine.com/articles/2017/07/01/parameterized-queries.aspx or https://blog.codinghorror.com/give-me-parameterized-sql-or-give-me-death/ – marc_s Jan 25 '22 at 21:04
  • 1
    don't do inline sql like this, but issue is with single and double quotes – Nonik Jan 25 '22 at 21:13
  • Have you tried inspectin the actual value of `query` and executing that query for instance in SQL Server Management Studio? That might give you a hint on what's going wrong with your approach of constructing queries. Use parametrized queries, that will get rid of many problems (mysterious syntax errors, security issues, ...9 – derpirscher Jan 25 '22 at 21:25
  • 'Con.Open(); var query = @"insert into Student_tbl value StudUsn=@sn, FatherName=@fn, MotherName=@mn, AdressTb=@at, CollegeTb=@ct, StudRoomCb=@sr,StudStatusCb=@ss"; SqlCommand cmd = new SqlCommand(query,Con); string sn = null; cmd.Parameters.AddWithValue(@sn, StudName.Text); string fn = null; cmd.Parameters.AddWithValue(@fn, FatherName.Text);' i have tried this but the error still the same with value – FrankHips Jan 25 '22 at 21:34
  • 1
    First of all such updated info belongs into the question (via edit) and not in the comments. Second, what sort of `insert` query is that??? Have you tried reading one of the linked resources? Where did you find a syntax like that? – derpirscher Jan 25 '22 at 21:36
  • You also need to dispose your connection and command objects with `using`, do *not* cache your connection – Charlieface Jan 26 '22 at 01:19

2 Answers2

0

The following shows how to insert a record into a database using parameters. The number of rows affected will be returned (ie: 0 means the row wasn't inserted and 1 means the row was inserted successfully).

private int TblStudentInsert(string studentUsn, string fatherName, string motherName, string streetAddress, string college, string studentRoom, string studentStatus)
{
    string sqlText = "INSERT INTO Student (StudentUsn, FatherName, MotherName, StreetAddress, College, StudentRoom, StudentStatus) VALUES (@studentUsn, @fatherName, @motherName, @streetAddress, @college, @studentRoom, @studentStatus);";
    
    using (SqlConnection con = new SqlConnection(_connectionStr))
    {
        con.Open();

        using (SqlCommand cmd = new SqlCommand(sqlText, con))
        {
            //add parameters
            cmd.Parameters.Add("@studentUsn", SqlDbType.VarChar).Value = studentUsn;
            cmd.Parameters.Add("@fatherName", SqlDbType.VarChar).Value = fatherName;
            cmd.Parameters.Add("@motherName", SqlDbType.VarChar).Value = motherName;
            cmd.Parameters.Add("@streetAddress", SqlDbType.VarChar).Value = streetAddress;
            cmd.Parameters.Add("@college", SqlDbType.VarChar).Value = college;
            cmd.Parameters.Add("@studentRoom", SqlDbType.VarChar).Value = studentRoom;
            cmd.Parameters.Add("@studentStatus", SqlDbType.VarChar).Value = studentStatus;

            //execute and return number of rows affected
            return cmd.ExecuteNonQuery();
        }
    }
}

Note: For Unicode, use NVarChar instead of VarChar. If inserting a null value, use DBNull.Value.

Resources:

Tu deschizi eu inchid
  • 4,117
  • 3
  • 13
  • 24
-1

This syntax error is caused by a trailing comma as if you run the debugger and evaluate it, this will be in the format of INSERT INTO Student_tbl VALUES (N'xyz'). You just need to remove the concatenation and use parameters for these values.

Try this one

string query = "INSERT INTO Student_tbl (StudUsn, StudName,FatherName,MotherName,AddressTb,CollegeTb) VALUES (@StudUsn, StudName,@FatherName,@MotherName,@AddressTb,@CollegeTb);";
SqlCommand cmd = new SqlCommand(query, Con);
cmd.Parameters.Add("@StudUsn", SqlDbType.VarChar).Value = StudUsn.Text;
cmd.Parameters.Add("@StudName", SqlDbType.VarChar).Value = StudName.Text;
cmd.Parameters.Add("@FatherName", SqlDbType.VarChar).Value = FatherName.Text;
//.
//.
//so on and so forth
cmd.ExecuteNonQuery();

insert into Student_tbl (this name should be matched with your sql table columns) ........

Nausif Momin
  • 37
  • 1