ADO.Net INSERT not inserting data

Question

I've got a c#.Net 4.5 console application and I'm trying to insert data from a DataTable to a SQL Server 2008R2 database table. I get no errors, but no data gets inserted. Here's my code:

Int32 newID = 0;
DataTable dtMaxUserID = GeneralClassLibrary.GeneralDataAccessLayer.ExecuteSelect("SELECT MAX(UserID)+1 AS NewID FROM TIUser", false, "TrackitCN");
newID = Convert.ToInt32(dtMaxUserID.Rows[0]["NewID"].ToString());

//Get new users from AllUserData
DataColumn dcRowID = new DataColumn("RowID", typeof(Int32));
//dcRowID.AllowDBNull = false;
dcRowID.AutoIncrement = true;
dcRowID.AutoIncrementSeed = 1;
dcRowID.AutoIncrementStep = 1;
//dcRowID.Unique = true;
//dcRowID.ColumnName = "RowID";
DataTable dtNewUsers = new DataTable();
dtNewUsers.Columns.Add(dcRowID);
dtNewUsers.Columns.Add("MaxID");
dtNewUsers.Columns.Add("UserID");
dtNewUsers.Columns.Add("FullName");
dtNewUsers.Columns.Add("Title");
dtNewUsers.Columns.Add("Phone");
dtNewUsers.Columns.Add("EMailAddr");
dtNewUsers.Columns.Add("Fax");
dtNewUsers.Columns.Add("Dept");
dtNewUsers.Columns.Add("Dept_Num");
dtNewUsers.Columns.Add("Location");
dtNewUsers.Columns.Add("UserDef_1");
dtNewUsers.Columns.Add("UserDef_2");
dtNewUsers.Columns.Add("Login");
dtNewUsers.Columns.Add("Password");
dtNewUsers.Columns.Add("PasswordFlags");
dtNewUsers.Columns.Add("LanguageID");
dtNewUsers.Columns.Add("NTAuthentication");
dtNewUsers.Columns.Add("NTAccount");
dtNewUsers.Columns.Add("SID");
dtNewUsers.Columns.Add("SelfServiceAccess");
dtNewUsers.Columns.Add("ImagePath");
dtNewUsers.Columns.Add("CompFlag");
dtNewUsers.Columns.Add("Employee_ID");
dtNewUsers.Columns.Add("SessionID");
string strQuery = "SELECT " + newID + " AS MaxID, LName" + ", " + "FName AS FullName, Title, Phone, EMail AS EmailAddr, Fax, Department AS Dept, Office AS Location, [Login] AS Employee_ID FROM [User] WHERE StartDate >= CAST(DATEPART(year,getdate()) AS varchar) + '-' + CAST(DATEPART(month,getdate()) AS varchar) + '-' + CAST(DATEPART(day,getdate())-1 AS varchar)";
DataTable dtTemp = GeneralClassLibrary.GeneralDataAccessLayer.ExecuteSelect(strQuery, false, "AllUserDataCN");
foreach (DataRow row in dtTemp.Rows)
    dtNewUsers.ImportRow(row);

SqlTransaction tran = null;
    try
    {
        connection.Open();
        tran = connection.BeginTransaction();

        //Insert new users into TIUser
        SqlDataAdapter TIUser_adapter = new SqlDataAdapter();
        string queryString = "SELECT * FROM TIUser WHERE 1 = 0";
        TIUser_adapter.SelectCommand = new SqlCommand(queryString, connection, tran);
        TIUser_adapter.Fill(dtNewUsers);
        dtNewUsers.AcceptChanges();
        TIUser_adapter.Update(dtNewUsers);

        tran.Commit();
    }
    catch (System.Exception ex)
    {
        tran.Rollback();
        throw ex;
    }
    finally
    {
        connection.Close();
    }

GeneralClassLibrary is a class library we use here for a number of things; here it just executes a SELECT statement on a database. TIUser is the database table. The DataTable, dtNewUsers contains one row. I've verified that by debugging the code and inspecting the DataTable after the ImportRow is done.

After following the reply from user3787557 (THANK YOU!), I'm closer, but I'm getting a concurrency violation. I'm working on a development database, and all I'm doing is inserting a record, so I have no idea why there'd be a concurrency violation. One possibility: I alter the structure of the DataTable dtNewUsers by adding two columns. However, before I do the update, I remove those columns. The InsertCommand is fine; I've checked it in SSMS and it parses. Here's my new code:

using (SqlConnection connection = new SqlConnection(GeneralClassLibrary.GeneralConfigurationManager.ConnectionStrings["TrackitCN"].ConnectionString))
        {
            SqlTransaction tran = null;
            connection.Open();
            tran = connection.BeginTransaction();

            try
            {
                //Create empty TIUser Data Adapter
                SqlDataAdapter TIUser_adapter = new SqlDataAdapter();
                SqlCommandBuilder TIUser_builder = new SqlCommandBuilder(TIUser_adapter);
                string queryString = "SELECT * FROM TIUser WHERE 1 = 0";
                TIUser_adapter.SelectCommand = new SqlCommand(queryString, connection, tran);
                TIUser_adapter.InsertCommand = TIUser_builder.GetInsertCommand();
                TIUser_adapter.UpdateCommand = TIUser_builder.GetUpdateCommand();

                //Get new users from AllUserData
                DataTable dtNewUsers = new DataTable();
                TIUser_adapter.Fill(dtNewUsers);
                DataColumn dcRowID = new DataColumn("RowID", typeof(Int32));
                dcRowID.AutoIncrement = true;
                dcRowID.AutoIncrementSeed = 1;
                dcRowID.AutoIncrementStep = 1;
                dtNewUsers.Columns.Add(dcRowID);
                dtNewUsers.Columns.Add("MaxID");                                        string strQuery = "SELECT " + newID + " AS MaxID, LName + ', ' + FName AS FullName, Title, Phone, EMail AS EmailAddr, Fax, Department AS Dept, Office AS Location, [Login] AS Employee_ID FROM [User] WHERE StartDate >= CAST(DATEPART(year,getdate()) AS varchar) + '-' + CAST(DATEPART(month,getdate()) AS varchar) + '-' + CAST(DATEPART(day,getdate())-1 AS varchar)";
                DataTable dtTemp = GeneralClassLibrary.GeneralDataAccessLayer.ExecuteSelect(strQuery, false, "AllUserDataCN");
                foreach (DataRow row in dtTemp.Rows)
                    dtNewUsers.ImportRow(row);

                //Make sure new users aren't already in Trackit
                foreach (DataRow row in dtNewUsers.Rows)
                {
                    row["UserID"] = (Convert.ToInt32(row["RowID"]) + Convert.ToInt32(row["MaxID"])).ToString();
                    DataTable dtOverlap = GeneralClassLibrary.GeneralDataAccessLayer.ExecuteSelect("SELECT * FROM TIUser WHERE Employee_ID = '" + row["Employee_ID"].ToString() + "'", false, "TrackitCN");
                    if (dtOverlap.Rows.Count > 0)
                        dtNewUsers.Rows.Remove(row);
                }    

                //Remove MaxID and RowID Columns
                dtNewUsers.Columns.Remove("MaxID");
                dtNewUsers.Columns.Remove("RowID")

                TIUser_adapter.Update(dtNewUsers);
                tran.Commit();
            }
            catch (System.Exception ex)
            {
                GeneralClassLibrary.GeneralEmail.ExceptionNotification(System.Reflection.Assembly.GetExecutingAssembly().ToString(), ex.Message, ex.StackTrace);
                tran.Rollback();
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }

The Stack Trace I get is:

   at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)


at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)



at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)



at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)



at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)



at UpdateTrackitUsers.Program.Main(String[] args) in c:\Users\06717\Documents\Visual Studio 2012\Projects\UpdateTrackitUsersConsole\UpdateTrackitUsersConsole\Program.cs:line 91

Line 87 is the line:

TIUser_adapter.Update(dtNewUsers);

Can you please include the code that populates the data table — dgavian
– dgavian, Commented Jun 19, 2015 at 19:25
It's an update command. I'm using visualcsharptutorials.com/ado-net/… and stackoverflow.com/questions/1623446/… as references — Melanie
– Melanie, Commented Jun 19, 2015 at 19:49
side note: in your catch block, you'll want to do throw; instead of throw ex;. Don't lose your precious stacktrace :) — sstan
– sstan, Commented Jun 19, 2015 at 19:50
sstan - thanks! I actually removed a line where I email myself ex.Message and ex.StackTrace, just to simplify things. But the code never goes there, so I have to believe I'm just doing the INSERT wrong. — Melanie
– Melanie, Commented Jun 19, 2015 at 20:00

John Saunders · Accepted Answer · 2015-06-23 05:02:00Z

2

This is the code that worked for me. Basically what you're missing on your code is to use SqlCommandBuilder to create InsertCommand and UpdateCommand. Also GET RID of AcceptChanges(). That is going to cause the new row to NOT be sent to the database. One last thing: make sure you mark the MDF database file (if you're using one) properties with "Do Not Copy" otherwise the database gets overridden every time you compile and you can't see your changes.

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlDataAdapter adapter = new SqlDataAdapter();
    SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

    adapter.SelectCommand = new SqlCommand(queryString, connection);
    adapter.InsertCommand = builder.GetInsertCommand();
    adapter.UpdateCommand = builder.GetUpdateCommand();
    adapter.DeleteCommand = builder.GetDeleteCommand();

    DataTable dt = new DataTable();
    adapter.Fill(dt);

    DataRow row = dt.NewRow();
    row["RegionID"] = 5;
    row["RegionDescription"] = "Some region";
    dt.Rows.Add(row);

    //dt.AcceptChanges();

    int counter = adapter.Update(dt); 
}

edited Jun 23, 2015 at 5:02

John Saunders

162k26 gold badges252 silver badges403 bronze badges

answered Jun 23, 2015 at 4:51

jsanalytics

13.2k4 gold badges27 silver badges46 bronze badges

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

13 Comments

jsanalytics Over a year ago

FYI I used Northwind DB and Region table to make it simple to work with it.

Melanie Over a year ago

OK, I'm getting closer. THANK YOU for all your help. I'm now getting a concurrency violation on the Update statement. I've posted my new code in the question.

jsanalytics Over a year ago

Please post the stack trace for the exception you get.

Melanie Over a year ago

The stack trace is too long to put in a comment, so I've added it to the bottom of my question.

jsanalytics Over a year ago

Right before calling Update(), please check the following properties for any row you have either inserted and or modified: HasError, RowError and RowState. Also post the full stack trace including the inner exceptions. Use the exception ToString() method to get it easily and post it formatted as code, not plain text.

|

Collectives™ on Stack Overflow

ADO.Net INSERT not inserting data

1 Answer 1

13 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related