Click here to Skip to main content
15,884,986 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a problem with saving the changes to the database, below is a snipe of the code:

In c# class, ManageUsers.c:
C#
private string sConnectionString = ConfigurationSettings.AppSettings["ScreenDB"];

        public void vDeleteUser(string sTagID)
        {
            SqlConnection cnConnection = new SqlConnection(sConnectionString);
            try
            {
                if (cnConnection.State == ConnectionState.Closed)//check the connection
                {
                    //  Open the connection
                    cnConnection.Open();
                }
                //
                //Use the connection
                //

                SqlCommand cmdDelete = new SqlCommand();
                cmdDelete = new SqlCommand("SP_DeleteUser", cnConnection);
                cmdDelete.CommandType = CommandType.StoredProcedure;
                //if (!sTagID.Equals(string.Empty))
                //{
                   cmdDelete.Parameters.Add("@TagID", SqlDbType.VarChar).Value = sTagID;
                //}
                // Get query results
                   
                cmdDelete.ExecuteNonQuery();
            }//end try
            //To handle SQl exception
            catch (SqlException ex)
            {
                MessageBox.Show(ex.ToString());
                           }
            //To handle any type of exception
            catch (Exception e)
            {
                MessageBox.Show(e.ToString());
               
            }
            finally
            {
                //cnConnection.Close();
            }

        }

In windows form Form1:
C#
private void button1_Click(object sender, EventArgs e)
        {
           ManageUser oUser = new ManageUser();
           oUser.vDeleteUser("11221");
           
        }


Notes:
The stored procedure works fine, I've tested it on database server.

The database connection is fine, because it connects when retrieving data. As in, it retrieves data from the database when a select statement is called, so it is connected.
Posted
Comments
[no name] 2-Jul-13 14:42pm    
And so what would the problem be exactly?
Afnan_ 2-Jul-13 14:58pm    
The record isn't deleted from the database.

1 solution

If you can retrieve information but not insert, update or delete rows, then the first thing to do is look at the permissions your connection user has been permitted - it may be that your user does not have delete permission.

It is normal to use the minimum possible permissions for security purposes: Principle of least privilege[^]
 
Share this answer
 
Comments
Afnan_ 2-Jul-13 15:06pm    
No permission was modified or changed I'm working on the default settings, the App.config file has only the connection string.
Afnan_ 2-Jul-13 15:13pm    
I really don't know what the issue is.
OriginalGriff 2-Jul-13 15:16pm    
Did you check the user you log into SQL with?
Afnan_ 3-Jul-13 2:50am    
If you mean permissions on the folder it self, then yes it has full control. i work on the embedded SQL severer 2008 in Visual Studio 2010, so where exactly can I check on the permissions.

Searched the internet and this is what I found(http://msdn.microsoft.com/en-us/library/ms189612(v=sql.105).aspx) do you mean this?
OriginalGriff 3-Jul-13 3:51am    
No, I don;t mean permissions on the folder - when you connect to SQL Server in your code, you have to provide login information. The user that you log in will have permissions within SQL server which restrict the range of actions that user can perform to teh database. A simple user may only have permission to SELECT rows, a more complex user may be able to INSERT new ones or UPDATE existing rows, and an advanced user may have permission to DELETE - but under normal circumstances users are granted the minimum possible permission to do their job.
Since you can SELECT ok with your existing connection string, it's worth looking at it and finding out what user you are logging in as, and checking the permissions assigned to that user in SQL Server Management Studio.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900