Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# Windows Forms
I have a problem with saving the changes to the database, below is a snipe of the code:
 
In c# class, ManageUsers.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:
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 2-Jul-13 9:39am
Afnan_702
Comments
ThePhantomUpvoter at 2-Jul-13 14:42pm
   
And so what would the problem be exactly?
Afnan_ at 2-Jul-13 14:58pm
   
The record isn't deleted from the database.

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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[^]
  Permalink  
Comments
Afnan_ at 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_ at 2-Jul-13 15:13pm
   
I really don't know what the issue is.
OriginalGriff at 2-Jul-13 15:16pm
   
Did you check the user you log into SQL with?
Afnan_ at 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 at 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.
Afnan_ at 3-Jul-13 18:24pm
   
Okay, I've checked that and the user(dbo, which is the sysadmin) has the permissions
dbo user already has the permissions of db_datareader and db_datawriter.
 
I've tested other databases on web applications and run fine. What I've noticed on windows applications is that as soon as I start running the application, the database connection is disconnected(red x appears on the database).

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



Advertise | Privacy | Mobile
Web01 | 2.8.1411023.1 | Last Updated 2 Jul 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100