none
Code will not insert a new row into my databse RRS feed

  • Question

  • I am new to C#, sql, and the use of databases. I am trying to insert a new row into PATIENT_TBL. The code returns no errors, warnings, exceptions or unhandled exceptions. The console also does not return the rows that are affected. I was wondering if someone could help me out, and find something I may have done incorrect.
    private void addNewPatient()
            {
                string query = "INSERT INTO PATIENT_TBL (PATIENT_ID, LAST_NAME, FIRST_NAME, DATE_Of_BIRTH, ADDRESS_STREET, ADDRESS_CITY, ADDRESS_STATE, ADDRESS_ZIP, PHONE_HOME, PHONE_MOBILE, PRIMARY_ID)" +
                               "VALUES (@PATIENT_ID, @LAST_NAME, @LAST_NAME, @DATE_Of_BIRTH, @ADDRESS_STREET, @ADDRESS_CITY, @ADDRESS_STATE, @ADDRESS_ZIP, @PHONE_HOME, @PHONE_MOBILE, @PRIMARY_ID);";
    
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
     
                    connection.Open();
    
                    using (SqlCommand cmd = new SqlCommand(query, connection))
                    {
                        cmd.Parameters.Add(new SqlParameter("@PATIENT_ID", SqlDbType.NChar, 10));
                        cmd.Parameters.Add(new SqlParameter("@LAST_NAME", SqlDbType.NVarChar, 20));
                        cmd.Parameters.Add(new SqlParameter("@First_Name", SqlDbType.NVarChar, 20));
                        cmd.Parameters.Add(new SqlParameter("@DATE_Of_BIRTH", SqlDbType.Date));
                        cmd.Parameters.Add(new SqlParameter("@ADDRESS_STREET", SqlDbType.NVarChar, 50));
                        cmd.Parameters.Add(new SqlParameter("@ADDRESS_CITY", SqlDbType.NVarChar, 20));
                        cmd.Parameters.Add(new SqlParameter("@ADDRESS_STATE", SqlDbType.NVarChar, 20));
                        cmd.Parameters.Add(new SqlParameter("@ADDRESS_ZIP", SqlDbType.NChar, 10));
                        cmd.Parameters.Add(new SqlParameter("@PHONE_HOME", SqlDbType.NChar, 10));
                        cmd.Parameters.Add(new SqlParameter("@PHONE_MOBILE", SqlDbType.NChar, 10));
                        cmd.Parameters.Add(new SqlParameter("@PRIMARY_ID", SqlDbType.NChar, 10));
    
                        cmd.Parameters["@PATIENT_ID"].Value = txtIdentityNumber.Text;
                        cmd.Parameters["@LAST_NAME"].Value = txtLastname.Text;
                        cmd.Parameters["@First_Name"].Value = txtFirstname.Text;
                        cmd.Parameters["@DATE_Of_BIRTH"].Value = dateTimePicker1.Value.ToShortDateString();
                        cmd.Parameters["@ADDRESS_STREET"].Value = "Please Update";
                        cmd.Parameters["@ADDRESS_CITY"].Value = "Please Update";
                        cmd.Parameters["@ADDRESS_STATE"].Value = "Please Update";
                        cmd.Parameters["@ADDRESS_ZIP"].Value = "Update";
                        cmd.Parameters["@PHONE_HOME"].Value = "Update";
                        cmd.Parameters["@PHONE_MOBILE"].Value = "Update";
                        cmd.Parameters["@PRIMARY_ID"].Value = "Update";
    
                        try
                        {
                            cmd.ExecuteNonQuery();
                        }
                        catch (SqlException e)
                        {
                            e.ToString();
                        }
    
                    }

    Saturday, December 23, 2017 4:10 PM

Answers

  • Hello,

    If you assign cms.ExecuteReader to a variable e.g. var affected = cmd.ExecuteReader(); and examine the value, if it's 1 then the insert worked, otherwise it failed.

    If the database is in the project e.g. an attached database and the property copy to output directory is "copy always" change it to "Copy if newer" and try again. See my MSDN code sample and Microsoft docs on the following page.

    In regards to the catch e.g. have you done either MessageBox.Show(e.Message); for a forms app or for a console app Console.WriteLine(e.Message); followed by Console.ReadLine(); ?

    The following class has two methods for inserting, the first is C# 7 while the second is for any version of C#.

    Note the AddWithValue, that's normally all that is needed and note the secondary query after the first one which gets the new primary key via ExecuteScalar which is the result of the secondary query after the insert.

    using System;
    using System.Data.SqlClient;
    
    namespace BackEnd
    {
        public class DataOperations
        {
            private string Server = "KARENS-PC";
            private string Catalog = "NorthWindAzure";
    
            private string ConnectionString = "";
    
            public DataOperations()
            {
                ConnectionString = $"Data Source={Server};Initial Catalog={Catalog};Integrated Security=True";
            }
    
            #region Using C# 7 
    
            public (bool Success, int Identifier, Exception Error) InsertDepartment(string pDepartmentName)
            {
                int newIdentifier = 0;
                bool success = false;
                Exception e = null;
    
                using (SqlConnection cn = new SqlConnection { ConnectionString = ConnectionString })
                {
                    using (SqlCommand cmd = new SqlCommand { Connection = cn })
                    {
                        cmd.CommandText = "INSERT INTO Departments VALUES (@DeparmentName); SELECT CAST(scope_identity() AS int);";
                        cmd.Parameters.AddWithValue("@DeparmentName", pDepartmentName);
                        try
                        {
                            cn.Open();
                            // New primary key for the record inserted
                            newIdentifier = Convert.ToInt32(cmd.ExecuteScalar());
                            success = true;
                        }
                        catch (Exception ex)
                        {
                            success = false;
                            e = ex;
                        }
                    }
                }
    
                return (success, newIdentifier, e);
    
            }
    
            #endregion
            #region Before C# 7
    
            public bool HasErrors { get; set; }
            public string ExceptionMessage { get; set; }
    
            public bool InsertDepartmentOld(string pDepartmentName, ref int pIdentifier)
            {
    
                bool success = false;
    
                using (SqlConnection cn = new SqlConnection { ConnectionString = ConnectionString })
                {
                    using (SqlCommand cmd = new SqlCommand { Connection = cn })
                    {
                        cmd.CommandText = "INSERT INTO Departments VALUES (@DeparmentName); SELECT CAST(scope_identity() AS int);";
                        cmd.Parameters.AddWithValue("@DeparmentName", pDepartmentName);
                        try
                        {
                            cn.Open();
                            // This returns the new primary key
                            pIdentifier = Convert.ToInt32(cmd.ExecuteScalar());
                            success = true;
                        }
                        catch (Exception ex)
                        {
                            success = false;
                            HasErrors = true;
                            ExceptionMessage = ex.Message;
                        }
                    }
                }
    
                return success;
            }
    
            #endregion
        }
    }
    

    Here is a call to the C# 7 version above

    private void cmdAddDepartment_Click(object sender, EventArgs e)
    {
        var ops = new Operations();
    
        var (Success, Identifier, Error) = ops.InsertDepartment(comboBox1.Text);
        if (Success)
        {
            MessageBox.Show($"Id: {Identifier} for '{comboBox1.Text}'");
        }
        else
        {
            MessageBox.Show(Error.Message);
        }
    
    }
    The above is from the following code sample.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Saturday, December 23, 2017 4:31 PM
    Moderator

All replies

  • Hello,

    If you assign cms.ExecuteReader to a variable e.g. var affected = cmd.ExecuteReader(); and examine the value, if it's 1 then the insert worked, otherwise it failed.

    If the database is in the project e.g. an attached database and the property copy to output directory is "copy always" change it to "Copy if newer" and try again. See my MSDN code sample and Microsoft docs on the following page.

    In regards to the catch e.g. have you done either MessageBox.Show(e.Message); for a forms app or for a console app Console.WriteLine(e.Message); followed by Console.ReadLine(); ?

    The following class has two methods for inserting, the first is C# 7 while the second is for any version of C#.

    Note the AddWithValue, that's normally all that is needed and note the secondary query after the first one which gets the new primary key via ExecuteScalar which is the result of the secondary query after the insert.

    using System;
    using System.Data.SqlClient;
    
    namespace BackEnd
    {
        public class DataOperations
        {
            private string Server = "KARENS-PC";
            private string Catalog = "NorthWindAzure";
    
            private string ConnectionString = "";
    
            public DataOperations()
            {
                ConnectionString = $"Data Source={Server};Initial Catalog={Catalog};Integrated Security=True";
            }
    
            #region Using C# 7 
    
            public (bool Success, int Identifier, Exception Error) InsertDepartment(string pDepartmentName)
            {
                int newIdentifier = 0;
                bool success = false;
                Exception e = null;
    
                using (SqlConnection cn = new SqlConnection { ConnectionString = ConnectionString })
                {
                    using (SqlCommand cmd = new SqlCommand { Connection = cn })
                    {
                        cmd.CommandText = "INSERT INTO Departments VALUES (@DeparmentName); SELECT CAST(scope_identity() AS int);";
                        cmd.Parameters.AddWithValue("@DeparmentName", pDepartmentName);
                        try
                        {
                            cn.Open();
                            // New primary key for the record inserted
                            newIdentifier = Convert.ToInt32(cmd.ExecuteScalar());
                            success = true;
                        }
                        catch (Exception ex)
                        {
                            success = false;
                            e = ex;
                        }
                    }
                }
    
                return (success, newIdentifier, e);
    
            }
    
            #endregion
            #region Before C# 7
    
            public bool HasErrors { get; set; }
            public string ExceptionMessage { get; set; }
    
            public bool InsertDepartmentOld(string pDepartmentName, ref int pIdentifier)
            {
    
                bool success = false;
    
                using (SqlConnection cn = new SqlConnection { ConnectionString = ConnectionString })
                {
                    using (SqlCommand cmd = new SqlCommand { Connection = cn })
                    {
                        cmd.CommandText = "INSERT INTO Departments VALUES (@DeparmentName); SELECT CAST(scope_identity() AS int);";
                        cmd.Parameters.AddWithValue("@DeparmentName", pDepartmentName);
                        try
                        {
                            cn.Open();
                            // This returns the new primary key
                            pIdentifier = Convert.ToInt32(cmd.ExecuteScalar());
                            success = true;
                        }
                        catch (Exception ex)
                        {
                            success = false;
                            HasErrors = true;
                            ExceptionMessage = ex.Message;
                        }
                    }
                }
    
                return success;
            }
    
            #endregion
        }
    }
    

    Here is a call to the C# 7 version above

    private void cmdAddDepartment_Click(object sender, EventArgs e)
    {
        var ops = new Operations();
    
        var (Success, Identifier, Error) = ops.InsertDepartment(comboBox1.Text);
        if (Success)
        {
            MessageBox.Show($"Id: {Identifier} for '{comboBox1.Text}'");
        }
        else
        {
            MessageBox.Show(Error.Message);
        }
    
    }
    The above is from the following code sample.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Saturday, December 23, 2017 4:31 PM
    Moderator
  • As Karen said for 'ExecuteReader()', ditto for your 'cmd.ExecuteNonQuery()' method. It returns an integer value depending on the number of rows affected by your command.

    wizend

    Saturday, December 23, 2017 5:02 PM
  • Okay, I tried your code and learned that there was an issue with the table design. I would suggest using

    CREATE TABLE [dbo].[PATIENT_TBL](
    	[id] [INT] IDENTITY(1,1) NOT NULL,
    	[PATIENT_ID] [NVARCHAR](MAX) NULL,
    	[LAST_NAME] [NVARCHAR](MAX) NULL,
    	[First_Name] [NVARCHAR](MAX) NULL,
    	[DATE_Of_BIRTH] [DATE] NULL,
    	[ADDRESS_STREET] [NCHAR](50) NULL,
    	[ADDRESS_CITY] [NCHAR](20) NULL,
    	[ADDRESS_STATE] [NCHAR](20) NULL,
    	[ADDRESS_ZIP] [NVARCHAR](MAX) NULL,
    	[PHONE_HOME] [NVARCHAR](MAX) NULL,
    	[PHONE_MOBILE] [NVARCHAR](MAX) NULL,
    	[PRIMARY_ID] [NVARCHAR](MAX) NULL,
     CONSTRAINT [PK_PATIENT_TBL] PRIMARY KEY CLUSTERED 
    (
    	[id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    

    Proof it works

    private void button1_Click(object sender, EventArgs e)
    {
        var ops = new DataOperations();
        int id = 0;
        if (ops.Insert("1","Payne", "Karen", new DateTime(1960,8,23), ref id))
        {
            MessageBox.Show($"Id: {id}");
        }
        else
        {
            MessageBox.Show(ops.ExceptionMessage);
        }
    }

    Back end class

    using System;
    using System.Data.SqlClient;
    
    namespace BackEnd
    {
        public class DataOperations
        {
            private string Server = "KARENS-PC";
            private string Catalog = "ForumExamples";
    
            private string ConnectionString = "";
    
            public DataOperations()
            {
                ConnectionString = $"Data Source={Server};Initial Catalog={Catalog};Integrated Security=True";
            }
    
            public bool HasErrors { get; set; }
            public string ExceptionMessage { get; set; }
    
            public bool Insert(string pPatientId,string pLastName, string pFirstName, DateTime pBirth, ref int pIdentifier)
            {
    
                bool success = false;
                var needValues = "Please update";
                using (SqlConnection cn = new SqlConnection { ConnectionString = ConnectionString })
                {
                    string query =
                        "INSERT INTO PATIENT_TBL " +
                        "(PATIENT_ID, LAST_NAME, FIRST_NAME, DATE_Of_BIRTH, ADDRESS_STREET, ADDRESS_CITY, " +
                        "ADDRESS_STATE, ADDRESS_ZIP, PHONE_HOME, PHONE_MOBILE, PRIMARY_ID)" +
                        "VALUES (@PATIENT_ID, @LAST_NAME, @LAST_NAME, @DATE_Of_BIRTH, @ADDRESS_STREET, " +
                        "@ADDRESS_CITY, @ADDRESS_STATE, @ADDRESS_ZIP, @PHONE_HOME, @PHONE_MOBILE, @PRIMARY_ID);" +
                        " SELECT CAST(scope_identity() AS int);";
    
    
                    using (SqlCommand cmd = new SqlCommand { Connection = cn })
                    {
                        cmd.CommandText = query;
    
                        cmd.Parameters.AddWithValue("@PATIENT_ID", pPatientId);
                        cmd.Parameters.AddWithValue("@LAST_NAME", pLastName);
                        cmd.Parameters.AddWithValue("@First_Name", pFirstName);
                        cmd.Parameters.AddWithValue("@DATE_Of_BIRTH", pBirth);
                        cmd.Parameters.AddWithValue("@ADDRESS_STREET", needValues);
                        cmd.Parameters.AddWithValue("@ADDRESS_CITY", needValues);
                        cmd.Parameters.AddWithValue("@ADDRESS_STATE", needValues);
                        cmd.Parameters.AddWithValue("@ADDRESS_ZIP", needValues);
                        cmd.Parameters.AddWithValue("@PHONE_HOME", needValues);
                        cmd.Parameters.AddWithValue("@PHONE_MOBILE", needValues);
                        cmd.Parameters.AddWithValue("@PRIMARY_ID", needValues);
    
                        try
                        {
                            cn.Open();
                            // This returns the new primary key
                            pIdentifier = Convert.ToInt32(cmd.ExecuteScalar());
                            success = true;
                        }
                        catch (Exception ex)
                        {
                            success = false;
                            HasErrors = true;
                            ExceptionMessage = ex.Message;
                        }
                    }
                }
    
                return success;
            }
        }
    }
    

    Your design was invalid as your "Please update" was too long for the fields while the propose design allows for larger strings. There is no reason to constrain values in the database table but instead the constraints would be in your code.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, December 23, 2017 5:33 PM
    Moderator
  • Just came back to see how you were doing and noticed another issue, you have @LAST_NAME in twice so that means first and last name would be the same as they are called named parameters for a good reason while if done in MS-Access they are ordinal position parameters.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, December 23, 2017 6:23 PM
    Moderator
  • Sorry, not to get back soon enough. I was taking a break over the holidays. Thank you so much Karen. That worked and is now adding a patient to the db. I have implemented your advice and fixes along with your other posts. Happy holidays.
    Tuesday, December 26, 2017 10:40 PM