CODEWITHSUNDEEP
c#sql.netsql-servervisual-studio
parisa
parisa

Reputation: 101

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

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.

Upvotes: 3

Views: 2393

Answers (1)

Habib
Habib

Reputation: 223187

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.

Upvotes: 7

Related Questions