Insert statement into SQL Server db

Question

I'm developing an ASP.NET MVC Web Application using SQL Server.

I am trying to INSERT a new entry into my database and I don't understand what am I doing wrong.

I get an exception on the line:

command.ExecuteNonQuery();

The code is:

try
            {
                SqlConnection connection = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=UniversityManager;Integrated Security=True");

                using (connection)
                {
                    //SqlCommand command = new SqlCommand(
                    //    "INSERT INTO Students VALUES(@Id, @Name, @Surname, @Year, @PhoneNumber, @Cnp);",
                    //    connection);
                    connection.Open();
                    String sql = "INSERT INTO Students(Id,Name,Surname,Year,PhoneNumber,Cnp) " +
                        "VALUES (@Id, @Name, @Surname, @Year, @PhoneNumber, @Cnp)";
                    SqlCommand command = new SqlCommand(sql, connection);
                    command.Parameters.Add("@Id", SqlDbType.Int);
                    command.Parameters["@Id"].Value = 5;

                    command.Parameters.Add("@Name", SqlDbType.VarChar);
                    command.Parameters["@Name"].Value = collection.Name;

                    command.Parameters.Add("@Surname", SqlDbType.VarChar);
                    command.Parameters["@Surname"].Value = collection.Surname;

                    command.Parameters.Add("@Year", SqlDbType.Int);
                    command.Parameters["@Year"].Value = collection.Year;

                    command.Parameters.Add("@PhoneNumber", SqlDbType.VarChar);
                    command.Parameters["@PhoneNumber"].Value = collection.PhoneNumber;

                    command.Parameters.Add("@Cnp", SqlDbType.VarChar);
                    command.Parameters["@Cnp"].Value = collection.Cnp;

                    command.ExecuteNonQuery();
                    connection.Close();
                }

                return RedirectToAction("Index");
            }
            catch
            {
                return View();
            }
        }

Thank you!

A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll — Teodora
– Teodora, Commented May 24, 2014 at 19:27
You need to provide the inner exception in order for help on this one my friend. — Derek
– Derek, Commented May 24, 2014 at 19:38
Exception:Thrown: "String or binary data would be truncated. The statement has been terminated." (System.Data.SqlClient.SqlException) A System.Data.SqlClient.SqlException was thrown: "String or binary data would be truncated. The statement has been terminated." Time: 5/24/2014 10:46:55 PM Thread:Worker Thread[3416] — Teodora
– Teodora, Commented May 24, 2014 at 19:48

Steve · Accepted Answer · 2014-05-24 19:52:33Z

2

YEAR is a reserved keyword for Sql Server. So, if you really have a column with that name, then you need to enclose it in square brackets every time you refer to it. Better change that name

 String sql = "INSERT INTO Students(Id,Name,Surname,[Year],PhoneNumber,Cnp) " +
              "VALUES (@Id, @Name, @Surname, @Year, @PhoneNumber, @Cnp)";

Another possibility is the Id column. If this column has the IDENTITY property set to true, then you should not set a value for it. It is automatically calculated by the database engine.

Looking at your innerexception message, it seems the problem is due to one or more of your parameters contains more text than allowed by the database field size.
You could try something like this (for each varchar parameter)

 // Assuming the Name field is defined as varchar(15)
 command.Parameters.Add("@Name", SqlDbType.VarChar, 15);
 command.Parameters["@Name"].Value = collection.Name;

edited May 24, 2014 at 19:52

answered May 24, 2014 at 19:27

Steve

217k22 gold badges242 silver badges296 bronze badges

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

11 Comments

Steve Over a year ago

Try to read also the message in the InnerException property of the main exception

Teodora Over a year ago

Followed your Id suggestion yet still nothing.

Steve Over a year ago

Could you add some information on the schema of the table Students? (Column name, DataType, Size and nullability)

Steve Over a year ago

It seems that one of your parameters contains more text than allowed by the size of the database field.

Teodora Over a year ago

OMG, I'm too tired for my own good. Yup, that's what I did. It works now. Thank you so much.

|

jebar8 · Accepted Answer · 2014-05-24 19:56:52Z

1

The String or binary data would be truncated exception means you're trying to insert a value that is too large for one of the columns in your Student table. For example, your Name field has a maximum length of 10 but you're trying to insert a 15 character name.

Check the values you're inserting and see if they're too large for the columns.

answered May 24, 2014 at 19:56

jebar8

2,1533 gold badges21 silver badges32 bronze badges

1 Comment

Teodora Over a year ago

i get this one now: Exception:Caught: "Cannot insert the value NULL into column 'Id', table 'UniversityManager.dbo.Students'; column does not allow nulls. INSERT fails. The statement has been terminated." (System.Data.SqlClient.SqlException) A System.Data.SqlClient.SqlException was caught: "Cannot insert the value NULL into column 'Id', table 'UniversityManager.dbo.Students'; column does not allow nulls. INSERT fails. The statement has been terminated." Time: 5/24/2014 10:55:24 PM Thread:Worker

Collectives™ on Stack Overflow

Insert statement into SQL Server db

2 Answers 2

11 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related