How to Insert data into a MSSQL table using C#

Question

I'm trying to using INSERT to write some data to a MSSQL Database table. I believe my SQL string is correct but I'm getting an error message when I run command.ExecuteScaler(); I've attached the error message in a screen shot. It states I'm using incorrect syntax but I'm not getting any compiler errors. I'm assuming I'm just doing something wrong.

CODE:

using (SqlConnection connection = new SqlConnection(SQLHelper.CnnCal("CQDB")))
{
    connection.Open();
    String insert = @"INSERT INTO Skills(SkillName, SkillNumber, SkillLastUpdated, SkillServer) VALUES(" + skills.SkillName + "," + skills.SkillNumber + "," + skills.LastUpdated + "," + skills.CallServer +")";
    SqlCommand command = new SqlCommand(insert, connection);
    command.ExecuteScalar();
    connection.Close();
}

Error Message from exception pop up

Question: What is the proper way of inserting data into a MSSQL database table?

for start you should use parameters in stead of building your query like this. It will prevent sql injection and will take care of quotes and formats for your values — GuidoG
– GuidoG, Commented Jun 27, 2017 at 15:32
The shortest and easiest solution to your problem is that you forgot to wrap all the parameters in quotes. You are currently building a query like INSERT INTO People (Name) VALUES (Flater), while it should be INSERT INTO People (Name) VALUES ('Flater') Notice the quotes, they are the source of your issue. However, such an answer would be highly downvoted as the expected standard is to use parameters so as to prevent SQL injection (but in all honesty, that is something that does not specifically relate to your question, it's just a matter of good practice). — Flater
– Flater, Commented Jun 27, 2017 at 15:41
using parameters in stead of building the query like this will also take care of the quotes problem. So I think it is a valid comment on this question — GuidoG
– GuidoG, Commented Jun 27, 2017 at 15:52

Alex · Accepted Answer · 2017-06-27 15:49:59Z

5

You should be using Parameters to prevent SQL Injection.

The below code takes care of that:

var query = "INSERT INTO Skills(SkillName, SkillNumber, SkillLastUpdated, SkillServer) 
             VALUES (@SkillName, @SkillNumber, @SkillLastUpdated, @SkillServer)";

using (SqlConnection connection = new SqlConnection(SQLHelper.CnnCal("CQDB")))
{
    using(SqlCommand cmd = new SqlCommand(query, connection))
    {
        // add parameters and their values
        cmd.Parameters.Add(new SqlParameter("SkillName", skills.SkillName ));
        cmd.Parameters.Add(new SqlParameter("SkillNumber", skills.SkillNumber ));
        cmd.Parameters.Add(new SqlParameter("SkillLastUpdated", skills.LastUpdated ));
        cmd.Parameters.Add(new SqlParameter("SkillServer", skills.CallServer

        cn.Open();
        cmd.ExecuteNonQuery();
    }    
}

edited Jun 27, 2017 at 15:49

answered Jun 27, 2017 at 15:37

Alex

38.6k55 gold badges217 silver badges340 bronze badges

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

Comments

suisgrand · Accepted Answer · 2017-06-27 15:33:45Z

-2

you forgot to set the connection property for the command object: command.Connection = connection; and:

command.CommandType = CommandType.Text;
command.CommandText = your_sql_query;

See: https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.connection(v=vs.110).aspx

answered Jun 27, 2017 at 15:33

suisgrand

1141 silver badge6 bronze badges

1 Comment

Flater Over a year ago

The connection property is already passed via the constructor. From OP's code: SqlCommand command = new SqlCommand(insert, connection);

Collectives™ on Stack Overflow

How to Insert data into a MSSQL table using C#

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related