A union of curiosity and data science

Knowledgebase and brain dump of a database engineer


SQL SERVER MSSMS Error "Saving Changes is not Permitted."

 

 
 
 
 
 
   To fix this error in SSMS: At the top right of your studio go to Tools > Options.
 
 
 
Click on Designers and uncheck "Prevent Saving Changes that require table re-creation".
FYI, you should understand the implications of dropping and recreating your tables and this isn't always the best option for making table alterations. 
 
 
 
 

C# Grab image from web and put it in a SQL Server Table.

Create a table in SQL to hold your images byte data. 

Your image column / field should look like this:

 

varbinary works well for saving image to SQL.

Code for grabbing the image from the web and store it in a byte array. 

        public byte[] get_image_bytes(String url)
        {

            Image image = null;
            Image thumb = null;

            WebClient webClient = new WebClient();
            byte[] current_data = null;

            MemoryStream ms = new MemoryStream();

            try
            {
                current_data = webClient.DownloadData(url);
                image = Image.FromStream(new MemoryStream(current_data));
                thumb = image.GetThumbnailImage(150, 150, null, IntPtr.Zero);
                image.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
                return ms.ToArray();
            }
            catch (Exception ex)  // could not find the image or write it. 
            {
                while (ex != null)
                {
                    ex = ex.InnerException;
                }
                return null;
            }
          }

Store the byte array in SQL. 

            //open my connection to stg SOLEBI
            string connection_string = "Data Source=<server>;Initial Catalog=<db>;User=<user>;password=<pass>;";
            conn = new SqlConnection(connection_string);
            conn.Open();

            cmd_list = new SqlCommand("update MyTable set image_thumb = @image_thumb where ID = 1");
            cmd_list.Parameters.AddWithValue("@image_thumb", get_image_bytes("http://www.mma.tv/wp-content/uploads/2016/05/Hottie-with-the-Body2.jpg"));
            cmd_list.Connection = conn;
            cmd_list.ExecuteNonQuery();
            conn.Close();
            cmd_list = null;