Insert command works without errors in C# but data is not inserted to the MS SQL Server

Question

I am trying to insert data into Microsoft SQL Server DB using C# and the insert command works well and I get no errors or exceptions. But when I check my database in SQL Server there is no effect on the table and the records are not inserted into the table. This is the code that I try:

try
{
   SqlConnection con1 = new SqlConnection();
   con1.ConnectionString = "Server = (local); Database = My_DataBase; Integrated Security = true";
   con1.Open();
   SqlCommand cm1 = new SqlCommand();
   cm1.Connection = con1;
   cm1.CommandText = "insert into Users values('" + update.Message.Chat.Id.ToString() + "','" + update.Message.Chat.FirstName + "','" + update.Message.Chat.LastName + "','@" + update.Message.Chat.Username + "','" + req1.Status + "')";
   con1.Close();

}
catch(Exception e)
{
    Console.WriteLine(e.Message);
    continue;
}

I've seen similar questions here and here, but the answers did not fix my problem.

Also when I insert data to the DB manually and run select command like mentioned below, I get the correct answer but for the insert command I do not.

SqlConnection con2 = new SqlConnection();
con2.ConnectionString = "Server = (local); Database = My_DataBase; Integrated Security = true";
con2.Open();
SqlDataAdapter da1 = new SqlDataAdapter("select * from Users where ChatID='" + update.Message.Chat.Id.ToString() + "'", con2);
DataSet ds1 = new DataSet();
da1.Fill(ds1);
con1.Close();

Please help me fix this issue.

By the way I know that this kind of insertion is not safe and I'l like to let you know that this is just a demo and I will make it secure against sql injection.

I don't have idea on .net/c# but does it auto commit the data? — Utsav
– Utsav, Commented Mar 3, 2017 at 15:06
You even have the same problem as the second question you linked. You don't have execute anywhere... — James Z
– James Z, Commented Mar 3, 2017 at 15:07
Since you say you are going to change this query to prevent sql injection why put much effort into this? You first need to fix the query before you test it. And get rid of that basically empty catch. It will write to the console before you can even see it and then it just closes. — Sean Lange
– Sean Lange, Commented Mar 3, 2017 at 15:07
@Habib I'm trying this in a Console Application and I've put my code in try-catch to get the possible exceptions but I don't and the code in the catch block works well. — parisa
– parisa, Commented Mar 3, 2017 at 15:07
@JamesZ The main scheme will not change, And the console will not close because the program is a telegram bot therefore the console keeps on being open until the bot stops. I've just mentioned a piece of code. — parisa
– parisa, Commented Mar 3, 2017 at 15:11

Habib · Accepted Answer · 2017-03-03 15:06:19Z

7

You are not executing your command anywhere. You need:

cm1.ExecuteNonQuery();

In your code, you are creating a SqlCommand object, then you associate a SqlConnection to it, but in no where you are actually executing the command. Your code should look like:

   SqlConnection con1 = new SqlConnection();
   con1.ConnectionString = "Server = (local); Database = My_DataBase; Integrated Security = true";
   con1.Open();
   SqlCommand cm1 = new SqlCommand();
   cm1.Connection = con1;
   cm1.CommandText = "insert into Users values('" + update.Message.Chat.Id.ToString() + "','" + update.Message.Chat.FirstName + "','" + update.Message.Chat.LastName + "','@" + update.Message.Chat.Username + "','" + req1.Status + "'";
   cm1.ExecuteNonQuery();
   con1.Close();

Apart from SQL Injection vulnerability, you should consider enclosing your SqlCommand and SqlConnection object in using statement, that will ensure proper disposal of un-managed resources.

answered Mar 3, 2017 at 15:06

Habib

224k30 gold badges420 silver badges446 bronze badges

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

2 Comments

parisa Over a year ago

Thanks Habib, You were right I had forgotten to execute my command. Please explain more about enclosing my command and connection.

Habib Over a year ago

@parisa, this is a good read codeproject.com/Articles/6564/…

Collectives™ on Stack Overflow

Insert command works without errors in C# but data is not inserted to the MS SQL Server

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related