SQL Insert Data not working properly

Question

I want to insert some values into my table based on a users input. I get these values from some textboxes. When i put a breakpoint before my SQL logic, and go through every line with F10, everything works just fine. I open the SQLConnection, create a SQLCommand, execute it, and close the connection again. I refresh the table and have all the values in there. But when I delete or disable the breakpoint, and the programm goes over these lines of code by itself, it doesn't work, meaning no values are being added to the table, no matter how often i refresh.

Here is the code I'm referring to:

try
{
     SqlConnection con = new SqlConnection("Server=...;Database=...;Integrated Security=true;");
     con.Open();

     SqlCommand com = new SqlCommand("INSERT INTO TestTable (Type,Serialnumber) VALUES('" + TypeText + "','" + SerText + "')", con);
     //Debug.WriteLine(com.CommandText);

     com.BeginExecuteNonQuery();

     con.Close();
}
catch(Exception ex)
{
     MessageBox.Show(ex.Message);
}

@BugFinder so long there is no way to tell if that variables are sanitized, could have been. Anyway it is a good advice using parametrized queries ;-) — Cleptus, Oct 26 '17 at 12:52

score 1 · Accepted Answer · answered Oct 26 '17 at 12:50

When it runs on your own, it calls BeginExecuteNonQuery() and right next line connection is closed without waiting the async call to end. While using the debugger doing F10 you give it time to efectively end the execution. Consider using ExecuteNonQuery() instead, note the lack of the Begin.

score 0 · Answer 2 · answered Oct 26 '17 at 13:02

The BeginExecuteNonQuery method starts the process of asynchronously executing a Transact-SQL statement or stored procedure that does not return rows, so that other tasks can run concurrently while the statement is executing. When the statement has completed, developers must call the EndExecuteNonQuery method to finish the operation. The BeginExecuteNonQuery method returns immediately (CommandTimeout has no effect on BeginExecuteNonQuery), but until the code executes the corresponding EndExecuteNonQuery method call, it must not execute any other calls that start a synchronous or asynchronous execution against the same SqlCommand object. Calling the EndExecuteNonQuery before the command's execution is completed causes the SqlCommand object to block until the execution is finished.

You could have linked to the source were you copied that from: https://msdn.microsoft.com/en-us/library/7b6f9k7k(v=vs.110).aspx — hage, Oct 26 '17 at 13:15

score 0 · Answer 3 · answered Oct 26 '17 at 13:06

0

you need to consider many things before writing any DML code, kindly consider below link which gives you the way to handling resources.

https://stackoverflow.com/a/12142911/6783279

answered Oct 26 '17 at 13:06

Monkey D. luffy

28
5

score 0 · Answer 4 · edited Oct 26 '17 at 14:06

Best to use using with ExecuteNonQuery than BeginExecuteNonQuery

 try
            {
                using (SqlConnection conn = new SqlConnection("your Connection string"))
                {
                    conn.Open();

                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                         cmd.CommandText = @"INSERT INTO TestTable 
                                                (Type,Serialnumber )
                                                VALUES (@type,@serialnumber)";


                        cmd.Parameters.AddWithValue("@type", type);
                        cmd.Parameters.AddWithValue("@serialnumber", serialnumber);

                        int rows = cmd.ExecuteNonQuery();

                        if (rows == 0)
                            throw new Exception("Nothing Inserted into the DB");
                    }   
                }
            }
            catch (Exception ex)
            {
            }

SQL Insert Data not working properly

4 Answers4