How to Add edit & Del To This Project

As you seem to have taken my advice about parameterized queries, here is the first example. This also demonstrates some other techniques related to error reporting.

///

/// execute a parameterized query
///

/// sql statement
/// parameters; if query does not require parameters, set to null
/// return erro message
/// null on error, else datatable with result
public DataTable SELECT(string SQL, Hashtable htParams, out string errmsg)
{
    errmsg = "";
    try
    {
        DataTable dt = new DataTable();
        // set the query
        com.CommandText = SQL;
        // set the parameters
        if (htParams != null)
        {
            foreach (DictionaryEntry de in htParams)
            {
                com.Parameters.AddWithValue(de.Key.ToString(), de.Value);
            }
        }
        // fill the datatable
        da.Fill(dt);

        // return the result
        return dt;
    }
    catch (SqlException sex)
    // sql specific errors
    {
        // the error
        errmsg = sex.Message;
        // log to file; for you to implement

        // user friendly message; if you don't log to a file, comment the below line out
        errmsg = "An database error occured";
        return null;
    }
    catch (Exception ex)
    // other errors that may occur and throw exception
    {
        // the error
        errmsg = ex.Message;
        // log to file; for you to implement

        // user friendly message; if you don't log to a file, comment the below line out
        errmsg = "An error occured";
        return null;
    }
}

The above is an extension on your select method in the DataAccess class. You can add it to the DataAccess class. It takes two additional parameters.
The first additional parameter is a hashtable that is used to pass parameters.
A hashtable contains a set of entries with a key and a value. The name is the name of a parameter (e.g. @pID) and the value is the value that I want to pass (e.g. 1).
The code loops through the entries and adds the parameters to the SqlCommand. I'm very fond of a hashtable for this as it allows to pass a variable number of parameters so it does not really matter how many parameters the sql statement requires.
The second additional parameter is an output parameter that is used to return error messages.

I've 'embedded' the code in a try/catch to catch possible errors. Using try/catch makes your code more solid and prevents the end user from seeing errors that they don't understand. If you don't implement a logging to file, comment out the lines that create the user friendly error message.

There are two catches; the first one catches sql specific errors (just because this relates to SQL) and the second one any other exceptions that might occur.

Next I started running through your code and made a few changes in Users.cs (method Login) to demonstrate how you can pass the parameters.

public bool? Login(string Name, string Pass, out string errmsg)
{
    DA.Connect();
    //string sql = "Select Count(*) From [User] Where Nam = '{0}' And Pas = '{1}'";
    string sql = "Select Count(*) From [User] Where Nam = @pNam And Pas = @pPas";
    //sql = string.Format(sql, Name, Pass);
    Hashtable htParams = new Hashtable();
    htParams.Add("@pNam", Name);
    htParams.Add("@pPas", Pass);
    DataTable dt = new DataTable();
    dt = DA.SELECT(sql, htParams, out errmsg);
    DA.Disconnect();

    // something went wrong
    if (dt == null)
    {
        return null;
    }

    bool Enter = false;
    if (dt.Rows[0][0].ToString() == "1")
    {
        Enter = true;
    }
    return Enter;
}

Note the use of bool? instead of bool in the method's declaration; this allows us to return null. The method also has an additional parameter to pass a possible error message.
I've commented out some of your statements and replaced them with mine; this might make it easier for you to follow the changes.
The first change is the select statement that now takes parameters. The second change is the creation of a hashtable and adding the parameters to it. the third change is to use the new method that we've created in DataAccess.

As it's useless to have errors and don't check for them and log/report the error, I've added a check (if (dt == null)). If there is an error, we 'stop' (return)

The last change is in the button2_Click method to cater for the changes in earlier Login method.

private void button2_Click(object sender, EventArgs e)
{
    // this can hold a possible error message
    string errmsg;

    Users us = new Users();

    // check if there is a valid login
    bool? rc = us.Login(textBox2.Text, textBox1.Text, out errmsg);
    if (rc == null)
    {
        MessageBox.Show(errmsg);
    }
    else
    {

        //if (us.Login(textBox2.Text, textBox1.Text, out errmsg) == true)
        if (rc == true)
        {
            us.Sath(textBox2.Text);

            this.Hide();
            Form2 f2 = new Form2();
            f2.Show();
        }
        else
            MessageBox.Show("unvalid pass");
    }
}

First of all we need to define a variable to hold possible error messages. Note the use of bool? instead of bool (again). We will check if it's null and display an error message if there is an error.

I hope that this explains how to use parameterized queries. Compile and run, there should not be a difference in behaviour to your original code. In the next step, delete your original select method in DataAccess, compile and fix all errors. If a query does not need parameters, pass null for htParameters.

The next post will contain an updated ADD method to make use of parameterized queries.

For the format exception
I have assumed that the first column of your datagrid contained the ID. As that is not the case, the code will not work. Change the index to 6 (the last column) or to the name of the column (possibly "ID").

us.ID = Convert.ToInt32(dr.Cells[6].toString());
or
us.ID = Convert.ToInt32(dr.Cells["ID"].toString());

I will see if I have time to look at the SQL issue. Does that happen in both codes (EDT and DEL) or only in one specific one?

One note:
please don't use screenshots; copy and paste code and errors into your posts. I  prefer to see them with a courier new font or a different color so they are easily recognizable.

hi ...

I can not do anything
The error is :

http://s5.picofile.com/file/8141787718/read.png

http://s5.picofile.com/file/8141787900/U003ntitled.png

http://s5.picofile.com/file/8141787968/Unerrtitled.png

http://s5.picofile.com/file/8141787992/Untitlerr2ed.png

What do you mean by 'I can not set the key --> edit & del'?

hi ...
i can not set Key --> edit & del !
please send full Code ...


thank you

I'm not sure how you see that this works. I think this is how you want it to work:
Edit

• User clicks edit button
• Data from selected row in datagrid is copied to textboxes etc above it.
• Data is modified
• User clicks set to save data in database

I'll leave the implementation of button1_Click to you; you can have a look at button3_Click lower down.

        private void button1_Click(object sender, EventArgs e)
        {
            // copy from datarow to edit 'window'
        }

        private void button2_Click(object sender, EventArgs e)
        {
            Users us = new Users();
            us.ID = Convert.ToInt32(textBox1.Text);
            us.Nam = textBox2.Text;
            us.Pas = textBox3.Text;
            us.Mnu = Convert.ToBoolean(comboBox1.Text);
            us.Str = Convert.ToBoolean(comboBox2.Text);
            us.Btn1 = Convert.ToBoolean(comboBox3.Text);
            us.Btn2 = Convert.ToBoolean(comboBox4.Text);
            if (us.ID == 0)
            {
                us.ADD();
                Form3_Load(null, null);
                MessageBox.Show("add new user");
            }
            else
            {
                us.EDT();
                Form3_Load(null, null);
                MessageBox.Show("update user");
            }
        }

The above assumes 0 is an invalid ID (e.g. when the ID column is an autoincrement column.

Delete

• User clicks delete button
• Selected datarow in datagrid is deleted

        private void button3_Click(object sender, EventArgs e)
        {
            Users us = new Users();

            // get ID from datarow in datagrid
            DataGridViewRow dr = dataGridView1.CurrentRow;
            us.ID = Convert.ToInt32(dr.Cells[0].ToString());

            // alternatively, copy from datarow to edit 'window'
            //us.ID = Convert.ToInt32(textBox1.Text);

            us.DEL();
            Form3_Load(null, null);
            MessageBox.Show("delete user");

        }


Some advise

• Use decent names for your controls that describe their functions or what they store. Call a delete button e.g. btnDel instead of button3 and a textbox that stores a name txtName instead of textBox2. And if you need support from forums where the main language is English, please use English names ;)
  
• The same applies to the methods for the click events. This will happen automatically if you give the button a decent name before you create the click event.
• Your query to add a user is prone to sql injection; it implies if somebody enters e.g. a specially crafted name, all records can be deleted or tables can be dropped. Read up on parameter queries and sql injection.

I hope the above helps you on the way. You need to finetune it further (e.g. add a 'new' button to clear the edit 'window' and set ID to 0)

Note that the above code snippets are not tested.